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 [email protected] 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”).