Solved

Updating a datagrid view bounded to dataset

Posted on 2011-03-07
10
415 Views
Last Modified: 2012-05-11
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
Comment
Question by:mgmhicks
  • 6
  • 4
10 Comments
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35059168
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
 

Author Comment

by:mgmhicks
ID: 35059281
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
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35059374
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:mgmhicks
ID: 35059426
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
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35059492
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
 

Author Comment

by:mgmhicks
ID: 35059514
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
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35059645
Ok, try this, change the Update call with this

daAdapter.Update(dsCategories, "Categories")
0
 

Author Comment

by:mgmhicks
ID: 35059800
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
 
LVL 13

Expert Comment

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

0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35060929
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question