Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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

0
mgmhicks
Asked:
mgmhicks
  • 6
  • 4
1 Solution
 
gamarrojgqCommented:
HI,

You should add code that updates you Datatable like this example
'Retrieve the table from your dataset
    Dim dt As Datatable = dsCategories.Tables(0)

    'Update the values.
    Dim row = gvCategories.Rows(e.RowIndex)
    dt.Rows(row.DataItemIndex)("YourFieldName1") = row.Cells(1).Text 'Assuming you DO NOT have any controls in the column
    dt.Rows(row.DataItemIndex)("YourFieldName2") = (CType((row.Cells(2).Controls(0)), TextBox)).Text 'Assuming you have a Textbox in the column
    dt.Rows(row.DataItemIndex)("YourFieldName3") = (CType((row.Cells(3).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()

Open in new window

0
 
mgmhicksAuthor Commented:
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?
0
 
gamarrojgqCommented:
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

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mgmhicksAuthor Commented:
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

0
 
gamarrojgqCommented:
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
0
 
mgmhicksAuthor Commented:
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

0
 
gamarrojgqCommented:
Ok, try this, change the Update call with this

daAdapter.Update(dsCategories, "Categories")
0
 
mgmhicksAuthor Commented:
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.
0
 
gamarrojgqCommented:
Ok, lets go step by step here, your ID  column is hidden from the gridview but exist in the Datatable right?

0
 
gamarrojgqCommented:
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
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now