This tutorial is part of Database Programming Made Easy Series.
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.
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"
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
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
cmd.ExecuteNonQuery()
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 txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed." End Try End Sub