How to create dataAdapter update command

I have a dataset bound to a grid control.  I change information on the grid, and that seems to work fine. After I change the item on the grid I want to update the underlying sql database with the dataset changes.  I thought that if I provided the dataapdater the update sqlcommand object, that anytime I did a ds.update it would know what to do with the updated records, ie update it, add a record or delete a record.  The following is the code I am using, I come up with error message after running the DA.update line.  Can anyone show me error of my ways.  This a webform.



thanks

Private Sub gvCategories_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles gvCategories.RowUpdating
        Dim dt As DataTable = dsCategories.Tables(0)

        'Update the values.
        Dim row = gvCategories.Rows(e.RowIndex)
        dt.Rows(row.DataItemIndex)("Short_Name") = (CType((row.Cells(2).Controls(0)), TextBox)).Text 'Assuming you have a Textbox in the column
        dt.Rows(row.DataItemIndex)("Long_Name") = (CType((row.Cells(3).Controls(0)), TextBox)).Text 'Assuming you have a Textbox in the column
        dt.Rows(row.DataItemIndex)("Active") = (CType((row.Cells(4).Controls(0)), CheckBox)).Checked  'Assuming you have a checkbox in the column
        'Reset the edit index.
        gvCategories.EditIndex = -1

        'Bind data to the GridView control.
        gvCategories.DataSource = dt
        gvCategories.DataBind()
        UpdateTable()

Open in new window

Private Sub UpdateTable()
        Dim myUpdateCMD As New SqlCommand
        '' Dim strUpdate As String
        Dim i As Integer = 0

        With myUpdateCMD
            .Connection = myConn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "TAG_UpdateCategories"
            .Parameters.Add(New SqlParameter("@ID", dsCategories.Tables(0).Columns.Item(0)))
            .Parameters.Add(New SqlParameter("@Long_Name", dsCategories.Tables(0).Columns.Item(1)))
            .Parameters.Add(New SqlParameter("@Short_Name", dsCategories.Tables(0).Columns.Item(2)))
            .Parameters.Add(New SqlParameter("@Color", dsCategories.Tables(0).Columns.Item(3)))
            .Parameters.Add(New SqlParameter("@Active", dsCategories.Tables(0).Columns.Item(4)))
            .Parameters.Add(New SqlParameter("@LocationID", dsCategories.Tables(0).Columns.Item(5)))
            '    .Parameters(0).Value = dsCategories.Tables(0).Columns.Item(0)
            '    .Parameters(1).Value = dsCategories.Tables(0).Columns.Item(1)
            '    .Parameters(2).Value = dsCategories.Tables(0).Columns.Item(2)
            '    .Parameters(3).Value = dsCategories.Tables(0).Columns.Item(3)
            '    .Parameters(4).Value = dsCategories.Tables(0).Columns.Item(4)

        End With

        Dim daAdapter As New SqlDataAdapter

        Try
            myConn.Open()
            daAdapter.UpdateCommand = myUpdateCMD
            daAdapter.Update(dsCategories.Tables(0))
            myConn.Close()
        Catch ex As Exception
            Dim myError As String
            myError = ex.Message
        End Try

Open in new window

mgmhicksAsked:
Who is Participating?
 
mgmhicksAuthor Commented:
Here is how I had to get through it.  Not really what I wanted I thought that if the adapter had the parameter fields, the dataset, and all the add, update, delete commands, it would just run whenever you do a ds.update.   However this works, and should be adequate for what I am trying to do.
If dsCategories.HasChanges Then
            For i = 0 To dsCategories.Tables(0).Rows.Count - 1
                If dsCategories.Tables(0).Rows(i).RowState = DataRowState.Added Then

                ElseIf dsCategories.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ElseIf dsCategories.Tables(0).Rows(i).RowState = DataRowState.Modified Then
                    Dim myRow As DataRow

                    myRow = dsCategories.Tables(0).Rows(i)
                    With myCMD
                        .Connection = myConn
                        .CommandText = "TAG_UpdateCategories"
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add(New SqlParameter("@ID", myRow.Item(0)))
                        .Parameters.Add(New SqlParameter("@Long_Name", myRow.Item(1)))
                        .Parameters.Add(New SqlParameter("@Short_Name", myRow.Item(2)))
                        .Parameters.Add(New SqlParameter("@Color", myRow.Item(3)))
                        .Parameters.Add(New SqlParameter("@Active", myRow.Item(4)))
                        .Parameters.Add(New SqlParameter("@LocationID", myRow.Item(5)))
                       
                    End With
                End If
            Next

        Else
            ' nothing to change

        End If
        Dim daAdapter As New SqlDataAdapter
        Try
            myConn.Open()
            daAdapter.UpdateCommand = myCMD

            daAdapter.Update(dsCategories.Tables(0))
            myConn.Close()
        Catch ex As Exception
            Dim myError As String
            myError = ex.Message
        End Try

Open in new window

0
 
gdupadhyayCommented:
Yes, it is same as explained in first URL.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.