sqlUpdate does not seem to work

[kenycole]

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:

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

[wild_bill]

When you call

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.

[jmcilhinney]

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.

[kenycole]

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!

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.

When you call

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.

[jmcilhinney]

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.

[kenycole]

Thanks guys,
Finally, I got it to work today.

Modification in one line of code seem to solve everything. I changed from this:

sAdapter = New SqlClient.SqlDataAdapter(sSQL, conn)

to this:

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.

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.

[jmcilhinney]

I don't know exactly what code you're using now but this is from post #1 and it's obviously a problem:

        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.

[kenycole]

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.

I don't know exactly what code you're using now but this is from post #1 and it's obviously a problem:

        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.