A Basic VB.NET ADO.NET Tutorial: Adding, Deleting, and Updating

Wednesday Apr 2nd 2008 by SM Altaf

Manipulating data with ADO.NET can be easy. Discover the steps to creating a simple form to add, update, and delete database records using ADO.NET.

This tutorial continues where the first tutorial, "A Basic ADO.NET Tutorial in Visual Basic .NET," ended. In this tutorial, you will do even more with ADO.NET in Visual Basic.NET.

Start by creating another form, just like you did in Part 1. Your form should have the same labels, textboxes, and buttons. Add three more buttons: btnAdd, btnUpdate, and btnDelete. Don't forget to go into Project properties and set this new form (Form2) as the startup object.

The code for Form Load, btnFirst, btnPrevious, btnNext, and btnLast is almost the same as in the form presented in Part 1, but with a few small differences. Instead of filling the textboxes individually each time, you'll call a single method, FillFields(), which looks at the intCurrentIndex variable and fills up the textboxes.

Private Sub FillFields()
   txtFirstName.Text = _
   txtLastName.Text  = _
   txtLocation.Text  = _
End Sub

To call this method from the btnPrevious click event, for example,

'We move back only if we're not at the first row.
If intCurrentIndex > 0 Then
'Subtract one from the current index.
   intCurrentIndex = intCurrentIndex - 1
   MessageBox.Show("You're already at the first record.")
End If

Also, move the declaration of the connection object and the dataadapter object to class level variables.

Dim ds As New DataSet()
Dim intCurrentIndex As Integer = 0
Dim da As New OleDbDataAdapter()
Dim conn As New OleDbConnection()

You should then specify the dataadapter's select statement in the form's load event like so:

da.SelectCommand = New OleDbCommand("SELECT EmployeeID, FirstName, _
   LastName, Location FROM tbl_Master")
da.SelectCommand.Connection = conn

Your form should work as before.

Getting to the guts and purpose of this form now, you have to add a record, update a record, and delete a record. ADO.NET provides many objects (and therefore ways) to accomplish this. You can use stored procedures with the OledbCommand object, or you can use SQL statements directly with the OleDbCommand object, or even use the Data Adapter to perform the updates.

There are different reasons for using each method, but for the purpose of this tutorial, the Data Adapter will continue to be used.

Start with the update method. Because the dataadapter filled the dataset, you can get the dataadapter to perform the update too. All you need to do is tell it what to do when the time comes to update.

In the form's load event, specify the UpdateCommand property.

da.UpdateCommand = _
   New OleDbCommand("UPDATE tbl_Master SET FirstName = _
   @FirstName, LastName = @LastName, _
   Location =@Location WHERE EmployeeID = @EmployeeID")
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@FirstName", _
   OleDbType.VarChar, 40, "FirstName")
da.UpdateCommand.Parameters.Add("@LastName", _
   OleDbType.VarChar, 40, "LastName")
da.UpdateCommand.Parameters.Add("@Location", _
   OleDbType.VarChar, 40, "Location")
da.UpdateCommand.Parameters.Add("@EmployeeID", _
   OleDbType.Integer, 5, "EmployeeID")

If you don't understand this, don't let it daunt you. Go over it slowly and you'll see: @FirstName, @LastName, @Location, and @EmployeeID are parameters in your UPDATE statement. The Parameters that will be added in the subsequent lines take arguments that are the parameter name (@FirstName), the data type(OleDbType.VarChar), the size of the field (40), and the name of the column in the dataset that will contain the new value ("FirstName").

In the btnUpdate's click event, you now can ask the dataadapter to perform an udpate.

Dim dr As DataRow
'This gets a reference to the row currently being edited
dr = ds.Tables(0).Rows(intCurrentIndex)
dr("FirstName") = txtFirstName.Text
dr("LastName")  = txtLastName.Text
dr("Location")  = txtLocation.Text

'Ask the dataadapter to call the UpdateCommand and update the database
ds.AcceptChanges()    'Commits the change to the dataset.

Similarly, for inserting, in the form's load event, specify the InsertCommand:

da.InsertCommand = _
   New OleDbCommand("INSERT INTO tbl_Master(FirstName, LastName, _
   Location) VALUES(@FirstName,@LastName,@Location)")
da.InsertCommand.Connection = conn
da.InsertCommand.Parameters.Add("@FirstName", _
  OleDbType.VarChar, 40, "FirstName")
da.InsertCommand.Parameters.Add("@LastName", _
   OleDbType.VarChar, 40, "LastName")
da.InsertCommand.Parameters.Add("@Location", _
   OleDbType.VarChar, 40, "Location")

And in the btnAdd's click event:

Dim dr As DataRow

dr = ds.Tables(0).NewRow()    'Gets a reference to a new row.
dr("FirstName") = txtFirstName.Text
dr("LastName")  = txtLastName.Text
dr("Location")  = txtLocation.Text


Finally, to delete. In the form's load event, specify a DeleteCommand:

da.DeleteCommand = New OleDbCommand("DELETE FROM tbl_Master _
                                     WHERE EmployeeID = @EmployeeID")
da.DeleteCommand.Connection = conn
da.DeleteCommand.Parameters.Add("@EmployeeID", OleDbType.Integer, _
                                5, "EmployeeID")

And in the btnDelete's click event:

Dim dr As DataRow
dr = ds.Tables(0).Rows(intCurrentIndex)
dr.Delete() 'Delete the row


There's a lot more that can be done, of course, but this should have given you an introduction to ADO.NET to get you started.

Other areas that I would recommend exploring are using ExecuteNonQuery() and also using stored procedures. More ADO.NET tutorials can be found here: http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOverview.asp.

If you are working with SQL Server instead of Access, the principles remain the same; just the namespace changes from System.Data.Oledb to System.Data.SqlClient. You will have SqlDataAdapter instead of OleDbDataAdapter, and so on.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved