Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Saving Changes
One big disadvantage of programs that store data inline is that they cannot update their data. A program that loads data from a database, on the other hand, can save changes back into the database. Program FromDB saves changes to its data when it unloads.
The Closing event handler shown in Listing 3 attempts to save the data when the form unloads. This code demonstrates a couple of very important points. When the program creates its data adapters, it tells the constructors what SQL SELECT statement to use when fetching data from the database, but it does not specify any INSERT, UPDATE, or DELETE statements. The data adapter's Update method needs those statements to do its job.
The code shown in Listing 3 creates those statements with SqlCommandBuilder objects. It makes a new SqlCommandBuilder passing its constructor the DataAdapter that needs the SQL statements. The SqlCommandBuilder automatically generates the INSERT, UPDATE, and DELETE statements so the program can invoke the data adapters' Update methods safely.
A second very important point is that UPDATE and DELETE statements do not work unless the database table contains a primary key. This is true because the data adapter cannot easily figure out what record to update or delete without a primary key. If the table has a primary key, the data adapter can use the original key values to find the record it should modify and then make the necessary changes.
In the TestScores database, the primary key for the Students table is the StudentId field. The primary key for the TestScores table is the combined StudentId and TestNumber fields.
A third important point involves the call to the m_daTestScores object's Update method. When the program used the data adapter's Fill method to load data from the database, it passed the method the table's name as a parameter. Because the program did not create a TableMapping for the table, the adapter doesn't remember where it found the data in the database. To tell the program where it should apply its changes, the call to the Update method must include the name of the table as a parameter.
Listing 3—Program FromDB uses this code to save changes to its data
' Save changes to the data. Private Sub Form1_Closing(ByVal sender As Object, _ ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing Dim cb_students As New SqlCommandBuilder(m_daStudents) m_daStudents.Update(m_DataSet) Dim cb_testscores As New SqlCommandBuilder(m_daTestScores) m_daTestScores.Update(m_DataSet, "TestScores") End Sub