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
