Solved

Updating a datagrid view bounded to dataset

Posted on 2011-03-07
10
408 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now