none
Update the databas thru datagridview changed event using ID RRS feed

  • Question

  • Hope I will get the answer soon from the ms mvps.

    I am using vs 2005 and access database.

    I have a datagridview, which produce results from a stored proc thru a search textbox for the data entry operator to edit the particular data.

    I am showing the data from a single table, but for the users sake i provide the related tables column name instead of column ID (i.e: My main table contains only ID for vendors and Suppliers but i am using a select command for retrieving the vendor name instead of vendor ID for the sake of editing purpose.

    How can i update the Changed data using the ID value  (i.e: Instead of VendorName to Vendor ID)

    Here is my code getting the data to the datagridview it all works fine.

    Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged

            Dim objConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = D:\\data\msd_db.mdb")
            'Opening connection to database
            objConn.Open()

            Dim rsadapt As New OleDb.OleDbDataAdapter
            Dim rsdata As New DataSet

            If txtSearch.Text = "" Then 'Test if the textbox is null, then reset the grid.
                rsadapt = New OleDbDataAdapter("SELECT * from tblBill", objConn)
                rsdata = New DataSet
                rsadapt.Fill(rsdata, "tblBill")
                Me.dgvEdit.DataSource = rsdata.Tables("tblBill")
            Else

                rsadapt = New OleDbDataAdapter("SELECT * FROM usp_dgView_View WHERE [DCNo] like '%" & txtSearch.Text & "%'", objConn)
                rsdata = New DataSet
                rsadapt.Fill(rsdata, "tblBill")
                Me.dgvEdit.DataSource = rsdata.Tables("tblBill")
            End If

        End Sub

    • Moved by Bob Wu-MTModerator Friday, April 6, 2012 2:03 AM (From:Windows Forms Data Controls and Databinding)
    Wednesday, April 4, 2012 5:21 AM

Answers

  • Ah ... had some typos in my orignal C# code (it was just coded off the top of my head,  not in Visual Studio). Sorry about that! Once I corrected the typos, the online converter could convert to VB just fine. This should be more to your liking:

    Private Sub UpdateBills()
        Using objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = D:\data\msd_db.mdb")
            Dim oc As New OleDbCommand("usp_BillsUpdate", objConn)
            objConn.Open()
            For Each row As DataRow In Me.rsdata.Tables("tblBill").GetChanges().Rows
                oc.Parameters.AddWithValue(row("VendorID"))
                ' add all the other parameters that your Stored Proc requires
                ' in a similar manner. Keep in mind that Access has positional parameters
                ' so they must be added in the same order as specified in the Stored Proc
                ' now execute the Stored Proc for each changed row
                
                oc.ExecuteNonQuery()
            Next
        End Using
    End Sub

    A good set of converters can be found here:

    http://www.developerfusion.com/tools/convert/csharp-to-vb/

    http://www.developerfusion.com/tools/convert/vb-to-csharp/


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, April 7, 2012 10:55 PM

All replies

  • Hi Jones,
    I will move the thread to the ADO.NET DataSet Forum for better support.
    Best Regards,

    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, April 6, 2012 2:02 AM
    Moderator
  • Hi Little Jones,

    Welcome to MSDN Forum.

    You have used dataapdater fill the dataset, after completing modify the records in the dataset, just call dataadapter.Update method, the modified records wil persist into database. More information about updating dataset by dataadapter, please refer here.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Friday, April 6, 2012 6:17 AM
    Moderator
  • You need to do a couple of things differently.

    First, make your DataSet a member of your Form, instead of declaring it in your Sub. That way you can use it from another Sub.

    If your Stored Proc does not include the Vendor ID, change it to include it. You can exclude that column from displaying in the grid.

    Now you can update the data that has changed in Me.rsdata.Tables("tblBill") and you have a couple of options. The preferable way to do this would be to create a new Stored Procedure for updating the database table, if you don't already have one. Create an Update sub that looks something like this (sorry about writing it in C# ... my favorite C#-to-VB online converter doesn't seem to be working at the moment. I'll try to find a different one after I post this):

    private void UpdateBills()
    {
        using (OleDbConnection objConn = new OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0; Data Source = D:\\data\msd_db.mdb"))
        {
            OleDbCommand oc = new OleDbCommand("usp_BillsUpdate", objConn);
            objConn.Open();
            foreach (DataRow row in this.rsdata.Tables["tblBill"].GetChanges().Rows)
            {
                oc.Parameters.AddWithValue(row("VendorID"));
                // add all the other parameters that your Stored Proc requires
                // in a similar manner. Keep in mind that Access has positional parameters
                // so they must be added in the same order as specified in the Stored Proc
                // now execute the Stored Proc for each changed row
                oc.ExecuteNonQuery();
            }
        }
    }

    You might also want to have a look at a 3-part blog series I wrote on DataAccess classes. The posts are a few years old, but they're still relevant for the most part.

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post gets into using anonymous delegates and may be too much for a beginner.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, April 7, 2012 9:10 PM
  • Ah ... had some typos in my orignal C# code (it was just coded off the top of my head,  not in Visual Studio). Sorry about that! Once I corrected the typos, the online converter could convert to VB just fine. This should be more to your liking:

    Private Sub UpdateBills()
        Using objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = D:\data\msd_db.mdb")
            Dim oc As New OleDbCommand("usp_BillsUpdate", objConn)
            objConn.Open()
            For Each row As DataRow In Me.rsdata.Tables("tblBill").GetChanges().Rows
                oc.Parameters.AddWithValue(row("VendorID"))
                ' add all the other parameters that your Stored Proc requires
                ' in a similar manner. Keep in mind that Access has positional parameters
                ' so they must be added in the same order as specified in the Stored Proc
                ' now execute the Stored Proc for each changed row
                
                oc.ExecuteNonQuery()
            Next
        End Using
    End Sub

    A good set of converters can be found here:

    http://www.developerfusion.com/tools/convert/csharp-to-vb/

    http://www.developerfusion.com/tools/convert/vb-to-csharp/


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, April 7, 2012 10:55 PM