Visual Basic 2010 Lesson 31: Managing Databases

[Lesson 30] << [CONTENTS]>>[Next]

31.1 Browsing Records in Databases

In this lesson, we will create command buttons and write relevant codes to allow the user to browse the records forward and backward as well as fast forward to the last record and back to the first record.The first button we need to create is for the user to browse the first record. We can use button’s text << to indicate to the user that it is the button to move to the first record and button’s text >> to move to the last record. Besides we can use button’s text < for moving to previous record and button’s text > for moving to next record.

The code for moving to the first record is as follows:

MyRowPosition = 0
 Me.showRecords()

The code for moving to the previous record is:

If MyRowPosition > 0 Then
 MyRowPosition = MyRowPosition - 1
 Me.showRecords()
 End If

The code for moving to next record is:

If MyRowPosition < (MyDataTbl.Rows.Count - 1) Then
 MyRowPosition = MyRowPosition + 1
 Me.showRecords()
End If

The code for moving to the last record is:

If MyDataTbl.Rows.Count > 0 Then
 MyRowPosition = MyDataTbl.Rows.Count - 1
 Me.showRecords()
 End If

31.2 Editing, Saving, Adding and Deleting Records

You can edit any record by navigating to the record and change the data values. However, you need to save the data after editing them. You need to use the update method of the SqlDataAdapter to save the data. The code is:

If MyDataTbl.Rows.Count <> 0 Then
MyDataTbl.Rows(MyRowPosition)("ContactName") = txtName.Text
 MyDataTbl.Rows(MyRowPosition)("state") = txtState.Text
 MyDatAdp.Update(MyDataTbl)
 End If

You can also add a new record or new row to the table using the following code:

Dim MyNewRow As DataRow = MyDataTbl.NewRow()
 MyDataTbl.Rows.Add(MyNewRow)
 MyRowPosition = MyDataTbl.Rows.Count - 1
 Me.showRecords()

The code above will present a new record with blank fields for the user to enter the new data. After entering the data, he or she can then click the save button to save the data.

Lastly, the user might want to delete the data. The code to delete the data is:

If MyDataTbl.Rows.Count <> 0 Then
 MyDataTbl.Rows(MyRowPosition).Delete()
 MyDatAdp.Update(MyDataTbl)
 MyRowPosition = 0
 Me.showRecords()
 End If