How to Modify Data Using Datasets and Data Adapters

Submitted by admin on Tuesday, May 5, 2009 - 21:30.

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

Error please help

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]

re: Error please help

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.

Is there a shorter way of doing this?

I think the code is too long

Myk

This is a great tutorial !!

updating fields after a query

Hello, your example helped me a lot. However, I'm facing a problem: I did a query according:

Dim sqlQRY As String = "SELECT * FROM JSM_LEDEN WHERE Kaartnummer = " & IntCardNr
 
Dim JSM_DataAdapter As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, JSM_Conn)
 
Dim JSM_DataSet As DataSet = New DataSet
 
JSM_DataAdapter.Fill(JSM_DataSet, "JSM_Leden")
 
Dim JSM_DataTable As DataTable = JSM_DataSet.Tables("JSM_Leden")
 
Dim row As DataRow
Dim NumLessons As Integer = 0
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:

For Each row In JSM_DataTable.Rows
      Txt_Name.Text = row("Naam")
      Txt_Forename.Text = row("Voornaam")
      Txt_Address.Text = row("Straat")
      Txt_City.Text = row("Gemeente")
      Txt_Zip.Text = row("Postcode")
 
      If IsDBNull(row("Tel")) Then
         Txt_Phone.Text = ""
     Else
         Txt_Phone.Text = row("Tel")
     End If
 
     If IsDBNull(row("GSM")) Then
       Txt_GSM.Text = ""
    Else
       Txt_GSM.Text = row("GSM")
    End If

Now I want to update a couple of fields (LastLog and NumLessons)

    If DateDiff(DateInterval.DayOfYear, CurrentDate, LastLogDate) Then
        NumLessons = Val(row("Aantal_Lessen")) + 1
        'Update the database
    End If

what would be the best approach to achieve this?

It's not clear to me on how

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:

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:

Private Sub btnMSAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMSAccess.Click
        '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"
 
        'Sql statement. This will insert record into Customers table
        Dim sqlQRY As String = "INSERT INTO Customers (CustomerID, CompanyName, City, Region, PostalCode) Values ('AAAAA', 'ABC Company', 'Seattle', 'WA', '98128')"
 
        'Create connection
        Dim conn As OleDbConnection = New OleDbConnection(cnString)
 
        Try
            ' Open connection
            conn.Open()
 
            'create command 
            Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)
 
            'execute non query command
            cmd.ExecuteNonQuery()
 
            txtStatus.Text = txtStatus.Text & "Insert Successful" & vbCrLf & vbCrLf
 
            'Sql statement. This will delete newly inserted record from Customers table
            sqlQRY = "DELETE FROM Customers WHERE CustomerID='AAAAA'"
 
            'execute non query command AGAIN! By this time using DELETE statement
            cmd.CommandText = sqlQRY
 
            'execute non query command. To see the changes in insert statement, comment the code below
            cmd.ExecuteNonQuery()
 
            txtStatus.Text = txtStatus.Text & "Delete Successful" & vbCrLf & vbCrLf
        Catch ex As OleDbException
            txtStatus.Text = "Error: " & ex.ToString & vbCrLf
        Finally
            ' Close connection
            conn.Close()
            txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
        End Try
    End Sub

Add new comment

Add new comment
128 views