George,
Thank you for the offer. Very nice of you.
Please note that the plants are NOT running sequel server, they are running Advantage data base server (
Have their own drivers etc
I am to the point wher I am filling the "temp" table with delivery tonnage from each plant. It works except it goes out of scope. I need to be able to update/insert this table when I extract the data for tonnage receipts and cosequently show a grid.
From reading an artice it says:
Table Variables. If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory (
Here is the code snippet for filling the table:
Public Sub ReadAsphaltTables()
'Loop through tables for each plant and update temp table
For i = 0 To AdsConnection.GetUpperBound(0)
If i < 8 Then
OpenAstecConnection()
If ConnectionError = False Then
SelectString = "select ticket_date, job, job_phase, max(qty_shipped_today) as tons FROM salestkt where ticket_date >= " & "'" & DateTimePicker1.Text & "'" & " and ticket_date <= " & "'" & DateTimePicker2.Text & "'" & " and substring(job, 1, 3) = " & "'" & JobPrefix(i) & "'" & " group by ticket_date, job, job_phase"
Dim MyAdsSelect As String = SelectString
Dim MyAdsCommand As New AdsCommand(MyAdsSelect, AstecConnection)
Dim MyAdsDataReader = MyAdsCommand.ExecuteReader()
If MyAdsDataReader.HasRows Then
Do While MyAdsDataReader.Read()
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm1", SqlDbType.DateTime)).Value = MyAdsDataReader.GetDateTime(0) 'date
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm2", SqlDbType.Char)).Value = MyAdsDataReader.GetString(1) 'job
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm3", SqlDbType.Char)).Value = MyAdsDataReader.GetString(2) 'job phase
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm4", SqlDbType.Int)).Value = MyAdsDataReader.GetInt32(3) 'tonnage
ServerCommand.CommandText = "sp_UpdateJobTable"
ServerCommand.ExecuteNonQuery()
ServerCommand.Parameters.Clear()
Loop
End If
End If
End If
AstecConnection.Close()
Next
End Sub
Code for "temp table"
CREATE PROCEDURE sp_UpdateJobTable
(
@parm1 datetime,
@parm2 char(15),
@parm3 char(15),
@parm4 int
)
AS
DECLARE @jobtable TABLE (
job_date datetime,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)
INSERT INTO @jobtable
(
job_date,
job_number,
job_phase,
asphalt_delivered
)
VALUES
(
@parm1,
@parm2,
@parm3,
@parm4
)
GO
