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

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

  • On-demand Event Event Date: March 27, 2014 Teams need to deliver quality software faster and need integrated agile planning, task tracking, source control, auto deploy with continuous builds and a configurable process to adapt to the way you work. Rational Team Concert and DevOps Services (JazzHub) have everything you need to build great software, integrated seamlessly together right out of the box or available immediately in the cloud. And with the Rational Team Concert Client, you can connect your …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds