Exam Code

Load

Public Sub loadData()

        Dim objConn As New SqlConnection

        Dim objCmd As New SqlCommand

        Dim strSQL As String

        Dim dt As New DataTable

        Dim Adapter As SqlDataAdapter

        Dim strConnString = "Data Source=COMSCI2-PC\SQLEXPRESS;Initial Catalog=Project_RFID;Persist Security Info=True;User ID=sa;Password=root"

        strSQL = "SELECT * FROM DRIVER_DATA WHERE DRIVER_ID LIKE '30' "

        objConn.ConnectionString = strConnString

        objConn.Open()

        With objCmd

            .Connection = objConn

            .CommandText = strSQL

            .CommandType = CommandType.Text

        End With

        Adapter = New SqlDataAdapter(objCmd)

        Adapter.Fill(dt)

        objConn.Close()

        If dt.Rows.Count > 0 Then

            Me.TextBox1.Text = dt.Rows(0)("DRIVER_NAME")

            Me.TextBox2.Text = dt.Rows(0)("DRIVER_LASTNAME")

            Me.TextBox3.Text = dt.Rows(0)("DRIVER_GENDER")

            Me.TextBox4.Text = dt.Rows(0)("DRIVER_TEL")

        Else

            MessageBox.Show("ไม่สามารถโหลดข้อมูลได้")

        End If

    End Sub

Edit

    Public Sub editData()

        Dim objConn As New SqlConnection

        Dim objCmd As New SqlCommand

        Dim strSQL As String

        Dim dt As New DataTable

        Dim Adapter As SqlDataAdapter

        strSQL = "UPDATE DRIVER_DATA SET [DRIVER_NAME]='" & Me.TextBox1.Text & "',[DRIVER_LASTNAME]='" & Me.TextBox2.Text & "',[DRIVER_GENDER]='" & Me.TextBox3.Text & "',[DRIVER_TEL]='" & Me.TextBox4.Text & "'WHERE DRIVER_ID='30'"

        objConn.ConnectionString = strConnString

        objConn.Open()

        With objCmd

            .Connection = objConn

            .CommandText = strSQL

            .CommandType = CommandType.Text

        End With

        Adapter = New SqlDataAdapter(objCmd)

        Adapter.Fill(dt)

        objConn.Close()

        MessageBox.Show("แก้ไขข้อมูลสำเร็จ")

    End Sub

   

Delete

 Public Sub deleteData()

        Dim objConn As New SqlConnection

        Dim objCmd As New SqlCommand

        Dim strSQL As String

        Dim dt As New DataTable

        Dim Adapter As SqlDataAdapter

        strSQL = "DELETE FROM [DRIVER_DATA] WHERE DRIVER_ID='" & Me.TextBox5.Text & "'"

        objConn.ConnectionString = strConnString

        objConn.Open()

        With objCmd

            .Connection = objConn

            .CommandText = strSQL

            .CommandType = CommandType.Text

        End With

        Adapter = New SqlDataAdapter(objCmd)

        Adapter.Fill(dt)

        objConn.Close()

        MessageBox.Show("ลบข้อมูลสำเร็จ")

    End Sub

Add

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim objConn As New SqlConnection

        Dim objCmd As New SqlCommand

        Dim strSQL As String

        Dim strConnString = "Data Source=COMSCI2-PC\SQLEXPRESS;Initial Catalog=Project_RFID;Persist Security Info=True;User ID=sa;Password=root"

        strSQL = "INSERT INTO DRIVER_DATA (DRIVER_NAME,DRIVER_LASTNAME,DRIVER_GENDER,DRIVER_TEL) " & _

        " VALUES " & " ('" & Me.TextBox1.Text & "','" & Me.TextBox2.Text & "','" & Me.TextBox3.Text & _

"', '" & Me.TextBox4.Text & "')"

        objConn.ConnectionString = strConnString

        objConn.Open()

        With objCmd

            .Connection = objConn

            .CommandText = strSQL

            .CommandType = CommandType.Text

        End With

        'Me.Save_Button.Visible = True

        Try

            objCmd.ExecuteNonQuery()

            MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "Complete", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception

            MessageBox.Show("ไม่สามารถบันทึก", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Information)

            'Me.lblStatus.Text = "Record can not insert Error (" & ex.Message & ")"

        End Try

        objConn.Close()

        objConn = Nothing

    End Sub

load to datagrid

  Private Sub BindDataGrid()

        Dim objConn As New SqlConnection

        Dim objCmd As New SqlCommand

        Dim strSQL As String

        Dim dt As New DataTable

        Dim Adapter As SqlDataAdapter

        strSQL = "SELECT * FROM BUS_DATA b INNER JOIN DRIVER_DATA d on b.driver_id = d.driver_id"

        objConn.ConnectionString = strConnString

        objConn.Open()

        With objCmd

            .Connection = objConn

            .CommandText = strSQL

            .CommandType = CommandType.Text

        End With

        Adapter = New SqlDataAdapter(objCmd)

        Adapter.Fill(dt)

        objConn.Close()

        Me.DataGridView1.DataSource = dt

        Me.DataGridView1.Columns.Clear()

        Dim column As DataGridViewTextBoxColumn

        column = New DataGridViewTextBoxColumn()

        column.DataPropertyName = "BUS_ID"

        column.HeaderText = "ID."

        column.Width = 85

        Me.DataGridView1.Columns.Add(column)

        column = New DataGridViewTextBoxColumn()

        column.DataPropertyName = "BUS_REGISTER"

        column.HeaderText = "ทะเบียน"

        column.Width = 70

        Me.DataGridView1.Columns.Add(column)

 SumLabel.Text = DataGridView1.RowCount.ToString()

        dt = Nothing

    End Sub