?
Solved

Update Database only with CHANGED DataGrid/DataSet values

Posted on 2006-04-17
4
Medium Priority
?
251 Views
Last Modified: 2010-04-23
I have this code....

Private Sub DataSaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataSaveButton.Click
        Dim RecordCount, RecordIndex As Integer
        Dim sSQL As String
        Dim rSQL As String
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim da As OleDbDataAdapter
        Dim ds As New DataSet
        Dim oldQty As Integer

        Dim connectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename
        con = New OleDbConnection(connectionString)
        con.Open()

        RecordCount = CrimeData.Tables(0).Rows.Count

        RecordIndex = 0

        While (RecordCount > RecordIndex)

            rSQL = "SELECT Quantity FROM Virginia WHERE Month = #" + CrimeData.Tables(0).Rows(RecordIndex).Item(0) + "# AND Type = '" + dbClass.GetTypeList(dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString()).Tables(0).Rows(CrimeTypeComboBox.SelectedIndex).Item(0).ToString() + "' AND Jurisdiction = '" + dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString() + "'"

            sSQL = "UPDATE Virginia SET Quantity = " + CrimeData.Tables(0).Rows(RecordIndex).Item(1).ToString() + " WHERE Month = #" + CrimeData.Tables(0).Rows(RecordIndex).Item(0) + "# AND Type = '" + dbClass.GetTypeList(dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString()).Tables(0).Rows(CrimeTypeComboBox.SelectedIndex).Item(0).ToString() + "' AND Jurisdiction = '" + dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString() + "'"

            'MessageBox.Show(rSQL)

            da = New OleDbDataAdapter(sSQL, con)
            da.Fill(ds)

            ds.Tables(0).TableName = "Quantity"

            oldQty = ds.Tables(0).Rows(0).Item(0).ToInteger()

            If (CrimeData.Tables(0).Rows(RecordIndex).Item("Quantity") <> oldQty) Then
                cmd = New OleDbCommand(sSQL, con)
                cmd.ExecuteNonQuery()
            End If


            RecordIndex += 1
        End While

        con.Close()
        DataDisplay.Refresh()

What I would like to do is run through the displayed rows of the DataSet in the DataGrid, check to see which ones have changed, and then only update the ones that have been changed.  This would seem to work, but when I run this I get an error that says, in reference to DataSet ds, that "Table 0 cannot be found".  The query rSQL displays correct syntax when output in the message box.  Does anybody know why I cannot access this "table" (really it is only one value, a single number)??  Or, does anybody know a quick way to update only changed values in a DataGrid that is different and more efficient than what I have here??  For information, I am using SQL to update specifically an Access 2000 database.

Thanks a lot for the help.
0
Comment
Question by:SysCapstone
2 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1000 total points
ID: 16475492
You have to call the dataadapter.Update method to write changes back to the database.  You need to define yoru dataadapter outside the scope of your event.  If done correctly your procedure would look like...

Private Sub DataSaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataSaveButton.Click

try
   daVirginia.Update(ds)
catch ex as exception
   messagebox.show(ex.message)
end try
end sub

you may have to throw in a call to endcurrentedit but that's about it.
0
 
LVL 6

Assisted Solution

by:cubixSoftware
cubixSoftware earned 1000 total points
ID: 16476375
Hi

I answered this question to help someone use a dataset to update an access database - hopefully this will help you as well

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21780852.html


0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses

850 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