Lesson 36: Editing Data in Database

Private currentPosition As Integer = 0
Private dt As New DataTable

' Move to first record
Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
    currentPosition = 0
    ShowCurrentRecord()
End Sub

' Move to previous record
Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
    If currentPosition > 0 Then
        currentPosition -= 1
        ShowCurrentRecord()
    End If
End Sub

' Move to next record
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
    If currentPosition < dt.Rows.Count - 1 Then
        currentPosition += 1
        ShowCurrentRecord()
    End If
End Sub

' Move to last record
Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
    If dt.Rows.Count > 0 Then
        currentPosition = dt.Rows.Count - 1
        ShowCurrentRecord()
    End If
End Sub

' Display current record
Private Sub ShowCurrentRecord()
    If dt.Rows.Count = 0 Then Return
    
    txtName.Text = dt.Rows(currentPosition)("ContactName").ToString()
    txtState.Text = dt.Rows(currentPosition)("State").ToString()
    
    lblPosition.Text = $"Record {currentPosition + 1} of {dt.Rows.Count}"
End Sub

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    ' Validate input
    If String.IsNullOrWhiteSpace(txtName.Text) Then
        MessageBox.Show("Contact name is required", "Validation Error", 
                      MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Return
    End If
    
    Try
        ' Update DataTable
        dt.Rows(currentPosition)("ContactName") = txtName.Text
        dt.Rows(currentPosition)("State") = txtState.Text
        
        ' Update database
        Dim adapter As New SqlDataAdapter()
        Dim cmdBuilder As New SqlCommandBuilder(adapter)
        adapter.Update(dt)
        
        MessageBox.Show("Record updated successfully!", "Success", 
                      MessageBoxButtons.OK, MessageBoxIcon.Information)
    Catch ex As Exception
        MessageBox.Show("Error updating record: " & ex.Message, "Database Error", 
                      MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
    ' Create a new row
    Dim newRow As DataRow = dt.NewRow()
    
    ' Set default values
    newRow("ContactName") = "[New Contact]"
    newRow("State") = ""
    
    ' Add to DataTable
    dt.Rows.Add(newRow)
    
    ' Move to new record
    currentPosition = dt.Rows.Count - 1
    ShowCurrentRecord()
    
    ' Focus on name field for editing
    txtName.Focus()
    txtName.SelectAll()
End Sub

Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    If dt.Rows.Count = 0 Then Return
    
    ' Get contact name for confirmation
    Dim contactName As String = dt.Rows(currentPosition)("ContactName").ToString()
    
    ' Confirm deletion
    Dim result As DialogResult = MessageBox.Show(
        $"Are you sure you want to delete '{contactName}'?", 
        "Confirm Deletion", 
        MessageBoxButtons.YesNo, 
        MessageBoxIcon.Question)
    
    If result = DialogResult.Yes Then
        Try
            ' Delete from DataTable
            dt.Rows(currentPosition).Delete()
            
            ' Update database
            Dim adapter As New SqlDataAdapter()
            Dim cmdBuilder As New SqlCommandBuilder(adapter)
            adapter.Update(dt)
            
            ' Refresh data and position
            dt.AcceptChanges()
            
            If currentPosition > dt.Rows.Count - 1 Then
                currentPosition = dt.Rows.Count - 1
            End If
            
            ShowCurrentRecord()
            
            MessageBox.Show("Record deleted successfully!", "Success", 
                          MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show("Error deleting record: " & ex.Message, "Database Error", 
                          MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End If
End Sub

Imports System.Data.SqlClient

Public Class DatabaseEditor
    Private connectionString As String = "Server=localhost\SQLEXPRESS;Database=ContactsDB;Integrated Security=True;"
    Private dt As New DataTable()
    Private currentPosition As Integer = 0
    
    Private Sub Form_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
        ShowCurrentRecord()
    End Sub
    
    Private Sub LoadData()
        Try
            Using conn As New SqlConnection(connectionString)
                conn.Open()
                
                Dim query As String = "SELECT * FROM Contacts"
                Dim adapter As New SqlDataAdapter(query, conn)
                
                dt.Clear()
                adapter.Fill(dt)
            End Using
        Catch ex As Exception
            MessageBox.Show("Error loading data: " & ex.Message, "Database Error", 
                          MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    
    ' ... Navigation methods from earlier ...
    ' ... Update, Add, Delete methods from earlier ...
End Class