Lesson 35: Connecting to Databases in VB2022

Imports System.Data.SqlClient

Public Class DatabaseForm
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Define connection string
        Dim connString As String = "Server=localhost\SQLEXPRESS;" & _
                          "Database=ContactsDB;" & _
                          "Integrated Security=True;"
        
        ' Create SqlConnection object
        Using conn As New SqlConnection(connString)
            Try
                ' Open the connection
                conn.Open()
                
                ' Check connection state
                If conn.State = ConnectionState.Open Then
                    MessageBox.Show("Database connection successful!", 
                                  "Connection Status", MessageBoxButtons.OK, 
                                  MessageBoxIcon.Information)
                End If
                
            Catch ex As SqlException
                MessageBox.Show("Error connecting to database: " & ex.Message, 
                              "Connection Error", MessageBoxButtons.OK, 
                              MessageBoxIcon.Error)
            End Try
        End Using
    End Sub
End Class

Imports System.Data
Imports System.Data.SqlClient

Public Class DataForm
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Connection string
        Dim connString As String = "Server=localhost\SQLEXPRESS;Database=ContactsDB;Integrated Security=True;"
        
        ' SQL query
        Dim query As String = "SELECT ContactID, FirstName, LastName, Email FROM Contacts"
        
        ' Create objects
        Using conn As New SqlConnection(connString)
            Try
                conn.Open()
                
                ' Create DataAdapter
                Dim adapter As New SqlDataAdapter(query, conn)
                
                ' Create DataTable
                Dim dt As New DataTable()
                
                ' Fill DataTable with data
                adapter.Fill(dt)
                
                ' Bind to DataGridView
                DataGridView1.DataSource = dt
                
            Catch ex As SqlException
                MessageBox.Show("Database error: " & ex.Message, "Error", 
                              MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub
End Class

Public Class NavigationForm
    Private currentPosition As Integer = 0
    Private dt As New DataTable
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Load data (implementation similar to previous example)
        LoadData()
        ShowCurrentRecord()
    End Sub
    
    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 btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        currentPosition = 0
        ShowCurrentRecord()
    End Sub
    
    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If currentPosition > 0 Then
            currentPosition -= 1
            ShowCurrentRecord()
        End If
    End Sub
    
    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
    
    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        currentPosition = dt.Rows.Count - 1
        ShowCurrentRecord()
    End Sub

Private Sub LoadData()
' Define schema
 dt.Columns.Add("ContactName")
 dt.Columns.Add("State")
    ' Add sample data
dt.Rows.Add("Alice", "California")
dt.Rows.Add("Bob", "Texas")
dt.Rows.Add("Charlie", "Florida")
    End Sub
End Class

Imports System.Data.SqlClient

Public Class InsertForm
    Private Sub BtnAdd_Click(sender As Object, e As EventArgs) Handles BtnAdd.Click
        Dim connString As String = "Server=localhost\SQLEXPRESS;Database=ContactsDB;Integrated Security=True;"
        Dim query As String = "INSERT INTO Contacts (FirstName, LastName, Email) " & _
                      "VALUES (@FirstName, @LastName, @Email)"
        
        Using conn As New SqlConnection(connString)
            Using cmd As New SqlCommand(query, conn)
                ' Add parameters to prevent SQL injection
                cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
                cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
                
                Try
                    conn.Open()
                    Dim rowsAffected = cmd.ExecuteNonQuery()
                    
                    If rowsAffected > 0 Then
                        MessageBox.Show("Record inserted successfully!", "Success", 
                                      MessageBoxButtons.OK, MessageBoxIcon.Information)
                        ' Clear form for next entry
                        ClearForm()
                    Else
                        MessageBox.Show("No records were inserted.", "Information", 
                                      MessageBoxButtons.OK, MessageBoxIcon.Information)
                    End If
                    
                Catch ex As SqlException
                    MessageBox.Show("Error inserting record: " & ex.Message, "Error", 
                                  MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
        End Using
    End Sub
    
    Private Sub ClearForm()
        txtFirstName.Text = ""
        txtLastName.Text = ""
        txtEmail.Text = ""
    End Sub
End Class