Option Explicit On
Option Strict On
Imports System.data
Imports system.data.sqlclient
Public Class frmUser
Dim User As String = "Data Source=AUGSARA\SQL2005;Initial Catalog=myproject;Integrated Security=True;User ID=sa;Password=12345"
Dim conn As New SqlConnection(User)
Dim checkadd As DialogResult
Private Sub frmUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
show_dv1()
Me.DataGridView1.Columns.Item(0).HeaderText = "เลือก"
Me.DataGridView1.Columns.Item(1).HeaderText = "รหัสพนักงาน"
Me.DataGridView1.Columns.Item(2).HeaderText = "ชื่อ-สกุล"
Me.DataGridView1.Columns.Item(3).HeaderText = "ชื่อผู้ใช้"
Me.DataGridView1.Columns.Item(4).HeaderText = "รหัสผ่าน"
Me.DataGridView1.Columns.Item(0).Width = 40
Me.DataGridView1.Columns.Item(1).Width = 100
Me.DataGridView1.Columns.Item(2).Width = 200
Me.DataGridView1.Columns.Item(3).Width = 100
Me.DataGridView1.Columns.Item(4).Width = 100
Me.cmdSearch.Enabled = True
Me.cmdSave.Enabled = True
Me.cmdEdit.Enabled = False
Me.cmdDelete.Enabled = False
Me.cmdClear.Enabled = False
End Sub
Sub show_dv1()
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Dim SQL As String
SQL = "select*from tbUsers"
Dim objDataAdapter1 As New SqlDataAdapter(SQL, conn)
Dim objDataset1 As DataSet = New DataSet()
objDataAdapter1.Fill(objDataset1, "xxxx")
DataGridView1.DataSource = objDataset1
DataGridView1.DataMember = "xxxx"
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
Dim checkadd As DialogResult
If Me.txtEmp_No.Text = "" Then
checkadd = MessageBox.Show("กรุณากรอกข้อมูลด้วยค่ะ", "เกิดข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
If checkadd = Windows.Forms.DialogResult.OK Then
Me.txtEmp_No.Focus()
End If
Else
'CheckStateConn()
Dim cmd As New SqlCommand("insert into tbUsers(Emp_No,Emp_name,txtUsername,txtPassword) values ('" & Me.Emp_No.Text & "','" & Me.Emp_name.Text & "','" & Me.txtUser_ID.Text & "','" & Me.txtPassword.Text & "')", conn)
Try
cmd.ExecuteNonQuery()
MsgBox("การบันทึกข้อมูลเสร็จสมบูรณ์ ", MsgBoxStyle.Information, "การบันทึกข้อมูล ")
Catch ex As Exception
MsgBox("การบันทึกข้อมูลล้มเหลว!!", MsgBoxStyle.Critical, "การบันทึกข้อมูล")
End Try
Dim obdata As New SqlDataAdapter("select * from dbo.tblUsers", conn)
Dim obdata1 As DataSet = New DataSet
obdata.Fill(obdata1, "user")
Me.DataGridView1.DataSource = obdata1
Me.DataGridView1.DataMember = "user"
End If
clearAll()
conn.Close()
Me.txtEmp_No.Focus()
show_dv1()
Me.DataGridView1.Columns.Item(0).HeaderText = "เลือก"
Me.DataGridView1.Columns.Item(1).HeaderText = "รหัสพนักงาน"
Me.DataGridView1.Columns.Item(2).HeaderText = "ชื่อ-สกุล"
Me.DataGridView1.Columns.Item(3).HeaderText = "ชื่อผู้ใช้"
Me.DataGridView1.Columns.Item(4).HeaderText = "รหัสผ่าน"
Me.DataGridView1.Columns.Item(0).Width = 40
Me.DataGridView1.Columns.Item(1).Width = 100
Me.DataGridView1.Columns.Item(2).Width = 200
Me.DataGridView1.Columns.Item(3).Width = 100
Me.DataGridView1.Columns.Item(4).Width = 100
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.cmdClear.Enabled = True
End Sub
Sub clearAll()
Me.txtEmp_No.Text = ""
Me.txtEmp_name.Text = ""
Me.txtUser_ID.Text = ""
Me.txtPassword.Text = ""
End Sub
Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
If e.RowIndex = e1 Then
End If
With DataGridView1
Me.txtEmp_No.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString()
Me.txtEmp_name.Text = .Rows.Item(e.RowIndex).Cells(2).Value.ToString()
Me.txtUser_ID.Text = .Rows.Item(e.RowIndex).Cells(3).Value.ToString()
Me.txtPassword.Text = .Rows.Item(e.RowIndex).Cells(4).Value.ToString()
End With
End Sub
Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
clearAll()
Me.txtEmp_No.Focus()
End Sub
Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
Dim checkadd As DialogResult
If Me.txtEmp_No.Text = "" Then checkadd = MessageBox.Show("กรุณาเลือกข้อมูลที่ต้องการลบ", "เกิดข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
If checkadd = Windows.Forms.DialogResult.OK Then
Me.txtEmp_No.Focus()
Else
CheckStateConn()
Dim Result As DialogResult
Result = MessageBox.Show("คุณต้องการลบข้อมูลใช่หรือไม่?", "ยืนยันการลบข้อมูล", MessageBoxButtons.YesNo, MessageBoxIcon.Information)
If Result = Windows.Forms.DialogResult.Yes Then
Dim cmd As New SqlCommand("delete from tbUsers where Emp_No= '" & Me.txtEmp_No.Text & "'", conn)
cmd.ExecuteNonQuery()
End If
End If
Try
Catch ex As Exception
MsgBox("การลบข้อมูลล้มเหลว!!", MsgBoxStyle.Critical, "การลบข้อมูล")
End Try
Dim obdata As New SqlDataAdapter("select * from dbo.tbLUsers", conn)
Dim obdata1 As DataSet = New DataSet
obdata.Fill(obdata1, "user")
Me.DataGridView1.DataSource = obdata1
Me.DataGridView1.DataMember = "user"
Me.txtEmp_No.Text = ""
Me.txtEmp_name.Text = ""
Me.txtUser_ID.Text = ""
Me.txtPassword.Text = ""
conn.Close()
Me.txtEmp_No.Focus()
show_dv1()
show_dv1()
Me.DataGridView1.Columns.Item(0).HeaderText = "เลือก"
Me.DataGridView1.Columns.Item(1).HeaderText = "รหัสพนักงาน"
Me.DataGridView1.Columns.Item(2).HeaderText = "ชื่อ-สกุล"
Me.DataGridView1.Columns.Item(3).HeaderText = "ชื่อผู้ใช้"
Me.DataGridView1.Columns.Item(4).HeaderText = "รหัสผ่าน"
Me.DataGridView1.Columns.Item(0).Width = 40
Me.DataGridView1.Columns.Item(1).Width = 100
Me.DataGridView1.Columns.Item(2).Width = 200
Me.DataGridView1.Columns.Item(3).Width = 100
Me.DataGridView1.Columns.Item(4).Width = 100
End Sub
Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
Dim checkadd As DialogResult
If Me.txtEmp_No.Text = "" Then
checkadd = MessageBox.Show("กรุณาเลือกข้อมูลที่ต้องการแก้ไข", "เกิดข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
If checkadd = Windows.Forms.DialogResult.OK Then
Me.txtEmp_No.Focus()
End If
Else
'CheckStateConn()
Dim update As SqlCommand = New SqlCommand("UPDATE tbUsers SET Emp_name='" & Me.txtEmp_name.Text & "',txtUser_ID='" & Me.txtUser_ID.Text & "',txtPassword='" & Me.txtPassword.Text & "' WHERE Emp_No='" & Me.txtEmp_No.Text & "'", conn)
Try
update.ExecuteNonQuery()
MessageBox.Show("แก้ไขข้อมูลเรียบร้อย", "ผลการแก้ไข", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
Catch ex As Exception
MessageBox.Show("การแก้ไขข้อมูลผิดพลาด!!", "ผลการแก้ไข", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
Dim obdata As New SqlDataAdapter("select * from dbo.tblUsers", conn)
Dim obdata1 As DataSet = New DataSet()
obdata.Fill(obdata1, "user")
Me.DataGridView1.DataSource = obdata1
Me.DataGridView1.DataMember = "user"
Me.txtEmp_No.Text = ""
Me.txtEmp_name.Text = ""
Me.txtUser_ID.Text = ""
Me.txtPassword.Text = ""
conn.Close()
Me.txtEmp_No.Focus()
show_dv1()
show_dv1()
Me.DataGridView1.Columns.Item(0).HeaderText = "เลือก"
Me.DataGridView1.Columns.Item(1).HeaderText = "รหัสพนักงาน"
Me.DataGridView1.Columns.Item(2).HeaderText = "ชื่อ-สกุล"
Me.DataGridView1.Columns.Item(3).HeaderText = "ชื่อผู้ใช้"
Me.DataGridView1.Columns.Item(4).HeaderText = "รหัสผ่าน"
Me.DataGridView1.Columns.Item(0).Width = 40
Me.DataGridView1.Columns.Item(1).Width = 100
Me.DataGridView1.Columns.Item(2).Width = 200
Me.DataGridView1.Columns.Item(3).Width = 100
Me.DataGridView1.Columns.Item(4).Width = 100
End Sub
Sub DisplayData() 'Refresh ข้อมูลในตาราง
Dim objdataadapter As New SqlDataAdapter("select Emp_No,Emp_name,txtUsername,txtPassword from tbUsers where Emp_No ='" & Me.txtEmp_No.Text & "'", conn)
Dim objdataset As New DataSet
'CheckStateConn()
objdataadapter.Fill(objdataset, "user") 'ชื่อนี้แล้วแต่จะตั้งไม่เกี่ยวกับชื่อของดาต้าเบส
DataGridView1.DataSource = objdataset
DataGridView1.DataMember = "user" 'ชื่อนี้ตั้งเหมือนกันกับดาต้าเบส ที่กำหนดไว้่
End Sub
Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
If Me.txtEmp_No.Text = "" Then MessageBox.Show("กรุณากรอกรหัสพนักงาน", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
'CheckStateConn()
Dim objDataAdapter1 As New SqlDataAdapter("select Emp_No,Emp_name,txtUsername,txtPassword from tbUsers where Emp_No ='" & Me.txtEmp_No.Text & "'", conn)
Dim objdataset1 As DataSet = New DataSet()
'CheckStateConn()
objDataAdapter1.Fill(objdataset1, "user")
DataGridView1.DataSource = objdataset1
DataGridView1.DataMember = "user"
Me.DataGridView1.Columns.Item(0).HeaderText = "เลือก"
Me.DataGridView1.Columns.Item(1).HeaderText = "รหัสพนักงาน"
Me.DataGridView1.Columns.Item(2).HeaderText = "ชื่อ-สกุล"
Me.DataGridView1.Columns.Item(3).HeaderText = "ชื่อผู้ใช้"
Me.DataGridView1.Columns.Item(4).HeaderText = "รหัสผ่าน"
Me.DataGridView1.Columns.Item(0).Width = 40
Me.DataGridView1.Columns.Item(1).Width = 100
Me.DataGridView1.Columns.Item(2).Width = 200
Me.DataGridView1.Columns.Item(3).Width = 100
Me.DataGridView1.Columns.Item(4).Width = 100
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.cmdClear.Enabled = True
End Sub
Private Sub TextBox1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Emp_No.KeyUp
If e.KeyCode = Keys.Enter Then
Me.cmdSearch_Click(sender, e)
End If
End Sub
End Class
Tag : Ms SQL Server 2005, Crystal Report, VS 2005 (.NET 2.x)