Updating a datagrid view bounded to dataset

Ok, I give this a shot, however to be clear this will only update the dataset not the underlying database.  So then the dataset.haschanges = true, so I will have to update the underlying with sqlstatement, but not sure if I do a dsUpdate it know which rows need to be changed and or added.  Is that done with the dataadapter update, insert, and delete statements?

ok, you can do it with a DataAdapter, just add  the following code to the previous one, right after the line

gvCategories.DataBind()

Dim strQuery as String

'Assign the query that orginally fills the datatable
strQuery = "SELECT * FROM TAG_InspectionCategories"

'Create the DataAdapter object
Dim daAdapter As New SqlDataAdapter(strQuery, conConnection)

'this will create all the UPDATE statements for you
Dim dcbCommand As New SqlCommandBuilder(daAdapter)

'this will update your Database
daAdapter.Update(dt)
daAdapter.Dispose()

Select all Open in new window

Here is the code I have now.  Doesnt seem to update database though. The gridview is working correctly.

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()

        Dim strQuery As String
        myConn.ConnectionString = ConStr
        'Assign the query that orginally fills the datatable
        strQuery = "SELECT * FROM TAG_InspectionCategories"

        'Create the DataAdapter object
        Dim daAdapter As New SqlDataAdapter(strQuery, myconn)

        'this will create all the UPDATE statements for you
        Dim dcbCommand As New SqlCommandBuilder(daAdapter)
        Try
            'this will update your Database
            myConn.Open()
            daAdapter.Update(dt)
            daAdapter.Dispose()
            myConn.Close()
        Catch ex As Exception
        End Try



    End Sub

Select all Open in new window

Ok the code looks good, did gives you an error message? maybe the Try catch does not let you see it, please remove (just for test) the try catch and run it.

Another test, change the Dataadapter declaration to use only the StringConecction instead of the Connection object like this

Dim daAdapter As New SqlDataAdapter(strQuery, ConStr)

and remove the lines

 myConn.ConnectionString = ConStr
myConn.Open()
myConn.Close()

If you pass only the string conecction the dataadapter will ope a connection and close it all by itself

Yea, thats what I thought, we need a update command.  Here is the error.

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

The update would be on the based on hidden column ID.   That is a IDENTITY Field

Ok, try this, change the Update call with this

daAdapter.Update(dsCategories, "Categories")

Still wants update command.  Here is what I think I have to do and maybe you can help.

After the grid updates, I want to run a sub that updates the underlying database.  So either I go through all dataset records to find changed rows, or I send the the row we just edited to the update function and update the underlying dataset.  However I know if we assign a update sql statment to the updatecommand of dataadapter it will work, just not sure of syntax to get what is in the changedrow value, to the updateCommand, via  parameters, and update underlying sql. Hope this makes sense.

thanks
Bye the way you already answered the first question, but we can continue here or I can create new for this issue.

Ok, lets go step by step here, your ID  column is hidden from the gridview but exist in the Datatable right?

ok, also try this, delete all after this line

        gvCategories.DataBind()

and put this code instead

        Dim strQuery As String
        myConn.ConnectionString = ConStr
        'Assign the query that orginally fills the datatable
        strQuery = "SELECT * FROM TAG_InspectionCategories"

        'Create the DataAdapter object
        Dim daAdapter As New SqlDataAdapter
      With myCMD
            .Connection = myConn
                .CommandText = "SELECT * FROM TAG_InspectionCategories "
                .CommandType = CommandType.Text
        End With
        myDA.SelectCommand = myCMD
        myConn.Open()

      daAdapter.SelectCommand = myCMD
        'this will create all the UPDATE statements for you
        Dim dcbCommand As New SqlCommandBuilder(daAdapter)
        'Try
            'this will update your Database
            daAdapter.Update(dt)
            daAdapter.Dispose()
            myConn.Close()
        'Catch ex As Exception
        'End Try