Avatar of mgmhicks
mgmhicks
 asked on

Updating a datagrid view bounded to dataset

I have a datagrid web control that I have bounded to a dataset.  I am using the controls update, and edit controls.  I can get the control into edit mode, with the rowediting event.  However not sure what to run in the rowupdating event to replace old value with new and update the dataset.  How do I set the keyfield so it knows what to update?

Try
                With myCMD
                    .Connection = myConn
                    .CommandText = "SELECT * FROM TAG_InspectionCategories "
                    .CommandType = CommandType.Text
                End With
                myDA.SelectCommand = myCMD
                myConn.Open()
                myDA.Fill(dsCategories, "Categories")
                gvCategories.DataSource = dsCategories.Tables(0)
                gvCategories.DataBind()
                myConn.Close()
            Catch ex As Exception
                If myConn.State = ConnectionState.Open Then
                    myConn.Close()
                End If
            End Try

dsCategories.Tables(0).Rows(e.RowIndex).Delete()
        dsCategories.Tables(0).Rows(e.RowIndex).AcceptChanges()
        gvCategories.DataSource = dsCategories.Tables(0)
        gvCategories.DataBind()

Open in new window

ASP.NET.NET ProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
gamarrojgq

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
gamarrojgq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mgmhicks

ASKER
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?
gamarrojgq

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

Open in new window

mgmhicks

ASKER
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

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
gamarrojgq

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
mgmhicks

ASKER
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

gamarrojgq

Ok, try this, change the Update call with this

daAdapter.Update(dsCategories, "Categories")
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mgmhicks

ASKER
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.
gamarrojgq

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

gamarrojgq

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes