Editing Data

In the previous lessons, you learned how to connect to a database and retrieve data. In this lesson, you will learn how to manipulate the data in the database.

Manipulating data includes:

• Browsing records
• Editing records
• Adding new records
• Deleting records
• Saving changes

To navigate through records, we use buttons such as:

• << → First record
• < → Previous record
• > → Next record
• >> → Last record

Move to first record:

MyRowPosition = 0
Me.showRecords()

Move to previous record:

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

Move to next record:

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

Move to last record:

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

After navigating to a record, you can modify the data and save it using the Update() method of the SqlDataAdapter.

If MyDataTbl.Rows.Count <> 0 Then
    MyDataTbl.Rows(MyRowPosition)("ContactName") = TxtName.Text
    MyDataTbl.Rows(MyRowPosition)("State") = TxtState.Text
    MyDatAdp.Update(MyDataTbl)
End If

To add a new record:

Dim MyNewRow As DataRow = MyDataTbl.NewRow()
MyDataTbl.Rows.Add(MyNewRow)

MyRowPosition = MyDataTbl.Rows.Count - 1
Me.showRecords()

This creates a blank record for the user to enter new data.

To delete a record:

If MyDataTbl.Rows.Count <> 0 Then
    MyDataTbl.Rows(MyRowPosition).Delete()
    MyDatAdp.Update(MyDataTbl)

    MyRowPosition = 0
    Me.showRecords()
End If

Public Class Form1

Private MyDatAdp As New SqlDataAdapter
Private MyCmdBld As New SqlCommandBuilder
Private MyDataTbl As New DataTable
Private MyCn As New SqlConnection
Private MyRowPosition As Integer = 0

Private Sub Form1_Load(...) Handles MyBase.Load

    MyCn.ConnectionString = "Data Source=YOUR_PC\SQL2012; " &
    "AttachDbFilename=C:\...\Test.mdf; " &
    "User Instance=True;Integrated Security=SSPI"

    MyCn.Open()

    MyDatAdp = New SqlDataAdapter("Select * from Contacts", MyCn)
    MyCmdBld = New SqlCommandBuilder(MyDatAdp)
    MyDatAdp.Fill(MyDataTbl)

    Me.showRecords()

End Sub

Private Sub showRecords()

    If MyDataTbl.Rows.Count = 0 Then Exit Sub

    TxtName.Text = MyDataTbl.Rows(MyRowPosition)("ContactName").ToString
    TxtState.Text = MyDataTbl.Rows(MyRowPosition)("State").ToString

End Sub

End Class