sqlUpdate does not seem to work
This is the first time I am working with sql server. when I try to update the table I get this error.
Update requires a valid InsertCommand when passed DataRow collection with new rows.
Can somebody help me ?
Thanks.
Here is my code:
Code:
Private Function CopyHeader(ByVal iAgentNo As Integer, ByVal lWkOrdNo As Long, ByVal lNewWkOrdNo As Long) As Boolean
Dim sAdOrig As New SqlClient.SqlDataAdapter
Dim sAdPreset As New SqlClient.SqlDataAdapter
Dim sAdapter As New SqlClient.SqlDataAdapter
Dim dSetOrig As New DataSet
Dim dSetPreset As New DataSet
Dim dSet As New DataSet
Dim sSQLPreset As String
'Dim iCounter As Integer
Dim bResult As Boolean
Dim maxRows As Integer
bResult = False
sSQLPreset = "SELECT * FROM PresetHeader WHERE t$agentno = " & iAgentNo
SqlConnect(connString)
sAdPreset = New SqlClient.SqlDataAdapter(sSQLPreset, conn)
sAdPreset.Fill(dSetPreset, "PresetHeader")
maxRows = dSetPreset.Tables("PresetHeader").Rows.Count
If maxRows < 0 Then
MessageBox.Show("Rep number '" & iAgentNo & "' is not set up in table 'PresetHeader'")
Exit Function
End If
Dim sSQLOrig As String = "SELECT * FROM Baan_TTDSLS840100 WHERE T$WKORDNO = " & lWkOrdNo 'current order
sAdOrig = New SqlClient.SqlDataAdapter(sSQLOrig, conn)
sAdOrig.Fill(dSetOrig, "Baan_TTDSLS840100")
'maxRows = dSetOrig.Tables("Baan_TTDSLS840100").Rows.Count
'this will add a record that is a copy
Dim sSQL As String = "SELECT * FROM Baan_TTDSLS840100"
Dim cb As New SqlCommandBuilder(sAdapter)
sAdapter = New SqlClient.SqlDataAdapter(sSQL, conn)
sAdapter.Fill(dSet, "Baan_TTDSLS840100_New")
Dim dsNewRow As DataRow
Dim fld As Data.DataColumn
dsNewRow = dSet.Tables("Baan_TTDSLS840100_New").NewRow()
'iCounter = 0
For Each fld In dSet.Tables("Baan_TTDSLS840100_New").Columns
Select Case fld.ColumnName
Case "T$WKORDNO"
dsNewRow.Item(fld.ColumnName) = lNewWkOrdNo
Case "T$SOLDCUST"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDCUST")
Case "T$SOLDTSFX"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDTSFX")
Case "T$SOLDNAM1"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDNAM1")
Case "T$SOLDNAM2"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDNAM2")
Case "T$SOLDADD1"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDADD1")
Case "T$SOLDADD2"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDADD2")
Case "T$SOLDCITY"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDCITY")
Case "T$SOLDTOST"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDTOST")
Case "T$SOLDZIP1"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDZIP1")
Case "T$SOLDZIP2"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDZIP2")
Case "T$SOLDCNTR"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$SOLDCNTR")
Case "T$ORDDT" 'use today's date
dsNewRow.Item(fld.ColumnName) = Date.Now() 'Format(Now(), "mm/dd/yy")
Case "T$STSPROGR" '-2 will print order
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$STSPROGR")
Case "T$AGENTNO"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$AGENTNO")
Case "T$COMMRALL" 'need to determine how commissions will be handled
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$COMMRALL")
Case "T$MSGTXT" 'this field needs to be cleared
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$MSGTXT")
Case "T$TERMSOFP"
dsNewRow.Item(fld.ColumnName) = dSetPreset.Tables("PresetHeader").Rows(0).Item("T$TERMSOFP")
Case "T$ORDTYPE"
If dSetOrig.Tables("Baan_TTDSLS840100").Rows(0).Item(fld.ColumnName) = "SST" Then
dsNewRow.Item(fld.ColumnName) = "STD"
ElseIf dSetOrig.Tables("Baan_TTDSLS840100").Rows(0).Item(fld.ColumnName) = "STD" Then
dsNewRow.Item(fld.ColumnName) = "SST"
End If
Case Else 'just copy the other fields
dsNewRow.Item(fld.ColumnName) = dSetOrig.Tables("Baan_TTDSLS840100").Rows(0).Item(fld.ColumnName)
End Select
'iCounter = iCounter + 1
Next fld
dSet.Tables("Baan_TTDSLS840100_New").Rows.Add(dsNewRow)
sAdapter.Update(dSet, "Baan_TTDSLS840100_New")
conn.Close()
sAdOrig = Nothing
sAdPreset = Nothing
sAdapter = Nothing
dSet = Nothing
dSetOrig = Nothing
dSetPreset = Nothing
bResult = True
CopyHeader = bResult
End Function
Re: sqlUpdate does not seem to work
When you call
Code:
sAdapter.Update(dSet, "Baan_TTDSLS840100_New")
The adapter checks the datatable for any rows in an Added status then executes the insert command. The same happens for updated and deleted rows. It's up to you to create the SQL and sqlcommand objects, then set the adapter's insertcommand, updatecommand, and deletecommand properties.
Re: sqlUpdate does not seem to work
Follow the CodeBank link in my signature and find my Retrieving & Saving Data thread. It provides a couple of code examples that show you how to create the action commands manually or automatically.
Re: sqlUpdate does not seem to work
Thank you for your reply.
I thought after you use commandbuilder, you don't have to set the adapter's insertcommand, updatecommand, and deletecommand. I thought commandbuilder does that automatically for you!
Code:
Dim cb As New SqlCommandBuilder(sAdapter)
Please clarify. I have worked with Access database before. I did not add any rows but changed the values in the datatable. And the same code worked just fine.
Thank you.
Quote:
Originally Posted by
wild_bill
When you call
Code:
sAdapter.Update(dSet, "Baan_TTDSLS840100_New")
The adapter checks the datatable for any rows in an Added status then executes the insert command. The same happens for updated and deleted rows. It's up to you to create the SQL and sqlcommand objects, then set the adapter's insertcommand, updatecommand, and deletecommand properties.
Re: sqlUpdate does not seem to work
Quote:
Originally Posted by
kenycole
I thought after you use commandbuilder, you don't have to set the adapter's insertcommand, updatecommand, and deletecommand. I thought commandbuilder does that automatically for you!
That is correct. The CommandBuilder generates the action commands based on the SelectCommand of the adapter. You'll see that from the two examples that use a DataAdapter in that thread of mine.
Re: sqlUpdate does not seem to work
Thanks guys,
Finally, I got it to work today.
Modification in one line of code seem to solve everything. I changed from this:
Code:
sAdapter = New SqlClient.SqlDataAdapter(sSQL, conn)
to this:
Code:
sAdapter.SelectCommand = New SqlClient.SqlCommand(sSQL, conn)
jmcilhinney,
I am still wondering why you dont need that change while working with Access Databases. I am still a student and working as an intern right now. So, if this question is too basic, I have an excuse. :)
Thank you.
Quote:
Originally Posted by
jmcilhinney
That is correct. The CommandBuilder generates the action commands based on the SelectCommand of the adapter. You'll see that from the two examples that use a DataAdapter in that thread of mine.
Re: sqlUpdate does not seem to work
I don't know exactly what code you're using now but this is from post #1 and it's obviously a problem:
Code:
Dim cb As New SqlCommandBuilder(sAdapter)
sAdapter = New SqlClient.SqlDataAdapter(sSQL, conn)
You can't create a command builder on a data adapter before you've created the data adapter. I can't see how the code change in post #6 would make any difference if you've done everything else properly. I've never had a problem with code like your first snippet in post #6, although I haven't used a command builder for a long time.
Re: sqlUpdate does not seem to work
jmcilhinney,
Thank you very very much. That was the problem. It works fine now after the changing the order of the code you mentioned.
The fix I mentioned in post # 6 also worked. I made no other changes in the code except for what I mentioned.
Thank you again.
Quote:
Originally Posted by
jmcilhinney
I don't know exactly what code you're using now but this is from post #1 and it's obviously a problem:
Code:
Dim cb As New SqlCommandBuilder(sAdapter)
sAdapter = New SqlClient.SqlDataAdapter(sSQL, conn)
You can't create a command builder on a data adapter before you've created the data adapter. I can't see how the code change in post #6 would make any difference if you've done everything else properly. I've never had a problem with code like your first snippet in post #6, although I haven't used a command builder for a long time.