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

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 = _
      ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
   txtLastName.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
   txtLocation.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
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
   FillFields()
Else
   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").

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

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.BeginEdit()  
dr("FirstName") = txtFirstName.Text
dr("LastName")  = txtLastName.Text
dr("Location")  = txtLocation.Text
dr.EndEdit()

'Ask the dataadapter to call the UpdateCommand and update the database
da.Update(ds)
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

ds.Tables(0).Rows.Add(dr)
da.Update(ds)
ds.AcceptChanges()

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

da.Update(ds)
ds.AcceptChanges()

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.



About the Author

SM Altaf

Mendhak is a web developer and a Microsoft MVP who works with ASP.NET and PHP among the usual array[] of web technologies. He is also rumored to be a three eyed frog, but the evidence is lacking. He can be contacted via his website, www.mendhak.com.


Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • Not all enterprise applications are created equal. Sophisticated applications need developer support but other more basic apps do not. With the right tools, everyone is a potential app developer with ideas and a perspective to share. Trends such as low-code development and model driven development are fundamentally changing how and who creates applications. Is your organization ready? Read this report and learn: The seven personas of enterprise app delivery How application ownership is spreading to the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds