How to Modify Data Using Datasets and Data Adapters

This tutorial is part of Database Programming Made Easy Series.

Modify Data using Dataset and Data Adapter

As I have mentioned in the previous tutorial on “How to Retrieve Data Using Data Readers”, I emphasized that if you want to just simply read data from the database you need to open it using data readers.

Using Datasets and Data Adapters, when you are accessing a large database and not going to save back the data is a waste of your systems resources. So be sure to select the best way in accessing your database.

There are some cases when you need to use Datasets along with Data Adapter to simplify the process of modifying a data with less coding. While you can still use ExecuteNonQuery to save the data back to the database, but this is not the superb way sometimes if you have a lot of data to save.

Saving the data programmatically using Dataset and Data Adapter.

While it is true that you can save the data easily using the dataset control and data adapter, sometimes you need flexibility in your application in accessing your database.

In my Enrollment System, I'm always using the dataset and data adapter control. But in Hotel Reservation (VB.NET) I try the opposite. If you are accessing a master file in one of your table I suggest you use the dataset and data adapter control.

Now let us move to the source code. Let us use the code from "How to Retrieve Data Using Dataset and Data Adapter"

'Set up connection string Dim cnString As String txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb" Dim sqlQRY As String = "SELECT * FROM Customers WHERE City = 'London'" 'Create connection Dim conn As OleDbConnection = New OleDbConnection(cnString) Try ' Open connection conn.Open() txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf & vbCrLf 'create data adapter Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn) 'create command builder Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da) 'create dataset Dim ds As DataSet = New DataSet 'fill dataset da.Fill(ds, "Customers") 'get data table Dim dt As DataTable = ds.Tables("Customers") ' modify city in first row dt.Rows(0)("City") = "Seattle" ' add a row Dim newRow As DataRow = dt.NewRow() newRow("CustomerID") = "BBBBB" newRow("CompanyName") = "CDE Company" newRow("City") = "Vancouver" newRow("Region") = "BC" newRow("PostalCode") = "98128" dt.Rows.Add(newRow) 'update customers table da.Update(ds, "Customers") txtStatus.Text = txtStatus.Text & "Update Successful" & vbCrLf & vbCrLf To modify the data without using the UpdateCommand property we use the code: Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da) This will automatically create an Insert, Update, Delete Command property for you. To modify and add new record we added the following code: ' modify city in first row dt.Rows(0)("City") = "Seattle" ' add a row Dim newRow As DataRow = dt.NewRow() newRow("CustomerID") = "BBBBB" newRow("CompanyName") = "CDE Company" newRow("City") = "Vancouver" newRow("Region") = "BC" newRow("PostalCode") = "98128" dt.Rows.Add(newRow) 'update customers table da.Update(ds, "Customers") Now you see how easy to update using dataset and data adapter? Try it yourself. Previous: How to Modify Data Using ExecuteNonQuery Next: Windows Forms

Comments

Submitted byAnonymous (not verified)on Tue, 06/01/2010 - 04:37

Hi, Whenever I try to write data back to my Access DB I get this message: Dim newRow As DataRow = dt.NewRow() NullReferenceException was unhandled Object reference not set to an instance of an object. Any ideas would be very helpful as I have been pulling my hair out for over two weeks now. Feel free to message me at: [email protected]
Submitted byadminon Tue, 06/01/2010 - 07:44

Is your primary key set to autonumber? If not then may be this is the cause of the problem. Register to this website and subscribe to your comments so you will receive the answer to your inbox right away. We don't have much time to email an answer to your question.
Submitted byAnonymous (not verified)on Fri, 12/03/2010 - 18:11

This is a great tutorial !!
Submitted byAnonymous (not verified)on Wed, 06/22/2011 - 04:19

Hello, your example helped me a lot. However, I'm facing a problem: I did a query according:
  1. Dim sqlQRY As String = "SELECT * FROM JSM_LEDEN WHERE Kaartnummer = " & IntCardNr
  2.  
  3. Dim JSM_DataAdapter As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, JSM_Conn)
  4.  
  5. Dim JSM_DataSet As DataSet = New DataSet
  6.  
  7. JSM_DataAdapter.Fill(JSM_DataSet, "JSM_Leden")
  8.  
  9. Dim JSM_DataTable As DataTable = JSM_DataSet.Tables("JSM_Leden")
  10.  
  11. Dim row As DataRow
  12. Dim NumLessons As Integer = 0
  13. Dim LastLogDate As Date
.. as the IntCardNr variable is a unique number, the query results in a single row from my database following code displays it:
  1. For Each row In JSM_DataTable.Rows
  2. Txt_Name.Text = row("Naam")
  3. Txt_Forename.Text = row("Voornaam")
  4. Txt_Address.Text = row("Straat")
  5. Txt_City.Text = row("Gemeente")
  6. Txt_Zip.Text = row("Postcode")
  7.  
  8. If IsDBNull(row("Tel")) Then
  9. Txt_Phone.Text = ""
  10. Else
  11. Txt_Phone.Text = row("Tel")
  12. End If
  13.  
  14. If IsDBNull(row("GSM")) Then
  15. Txt_GSM.Text = ""
  16. Else
  17. Txt_GSM.Text = row("GSM")
  18. End If
Now I want to update a couple of fields (LastLog and NumLessons)
  1. If DateDiff(DateInterval.DayOfYear, CurrentDate, LastLogDate) Then
  2. NumLessons = Val(row("Aantal_Lessen")) + 1
  3. 'Update the database
  4. End If
what would be the best approach to achieve this?

It's not clear to me on how do you like to update the LastLog and NumLessons fields. If you want to update the two fields using the "Kaartnummer" field then use:
  1. cmd.ExecuteNonQuery()
I have an example at this link: http://www.sourcecodester.com/tutorials/net/how-modify-data-using-executenonquery.html Here's the code on that link:
  1. Private Sub btnMSAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMSAccess.Click
  2. 'Set up connection string
  3. Dim cnString As String
  4.  
  5. txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf
  6.  
  7. cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"
  8.  
  9. 'Sql statement. This will insert record into Customers table
  10. Dim sqlQRY As String = "INSERT INTO Customers (CustomerID, CompanyName, City, Region, PostalCode) Values ('AAAAA', 'ABC Company', 'Seattle', 'WA', '98128')"
  11.  
  12. 'Create connection
  13. Dim conn As OleDbConnection = New OleDbConnection(cnString)
  14.  
  15. Try
  16. ' Open connection
  17. conn.Open()
  18.  
  19. 'create command
  20. Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)
  21.  
  22. 'execute non query command
  23. cmd.ExecuteNonQuery()
  24.  
  25. txtStatus.Text = txtStatus.Text & "Insert Successful" & vbCrLf & vbCrLf
  26.  
  27. 'Sql statement. This will delete newly inserted record from Customers table
  28. sqlQRY = "DELETE FROM Customers WHERE CustomerID='AAAAA'"
  29.  
  30. 'execute non query command AGAIN! By this time using DELETE statement
  31. cmd.CommandText = sqlQRY
  32.  
  33. 'execute non query command. To see the changes in insert statement, comment the code below
  34. cmd.ExecuteNonQuery()
  35.  
  36. txtStatus.Text = txtStatus.Text & "Delete Successful" & vbCrLf & vbCrLf
  37. Catch ex As OleDbException
  38. txtStatus.Text = "Error: " & ex.ToString & vbCrLf
  39. Finally
  40. ' Close connection
  41. conn.Close()
  42. txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
  43. End Try
  44. End Sub

Add new comment