Inserting records using sqldataadapter
I am creating a temporary table in SQL Server where new
records are going to be inserted from the a customer table.
I can not get it to work when inserting into a "CREATE
TABLE"..... however, it works if I select records from an
existing table.
Is it a way around to accomplish this insert? Thanks!
My example is:
Private Sub Load ...........
Dim custno, tempCust as string
tempQuery = "CREATE TABLE #myTempTable
(Customername varchar(20) NULL)"
strQueryCust = "SELECT * from Customers WHERE
customernumber = '" & custno & "'"
oTempDA = New SqlDataAdapter(tempQuery,
SqlConnection1)
Dim oDS As DataSet = New DataSet()
Dim oDA As SqlDataAdapter = New SqlDataAdapter
()
oDA = New SqlDataAdapter(strQueryCust,
SqlConnection1)
oDA.Fill(oDS, "Customers")
For i = 0 To oDS.Tables
("Customers").Rows.Count - 1
tempCust = oDS.Tables("Customers").Rows
(i).Item("dermnumber").TRIM
InsertTemp(tempCust)
Next
oDA.dispose
End SUb
Sub InsertTemp(Byval tempcust as string)
StrQuery = "INSERT INTO #myTempTable
Dim InsertCmd As New SqlCommand(StrQuery,
SqlConnection1)
oTempDA.InsertCommand = InsertCmd
oTempDA.InsertCommand.Connection =
SqlConnection1
oTempDA.InsertCommand.Parameters.Add(New
20, "dermnumber"))
oTempDA.InsertCommand.Parameters(0).Value
= tempCust
SqlConnection1.Open()
oTempDA.InsertCommand.ExecuteNonQuery()
End Sub
