Imports System.Data.SqlClient
Public Class EmpForm
Private Sub EmpForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
binddgv()
addCombo()
'ClrText()
'GenID()
End Sub
Public Sub ClrText()
IDTextBox.Text = String.Empty
ComboIns.Text = String.Empty
ComboTitle.Text = String.Empty
EmpNameTextBox.Text = String.Empty
EmpLnameTextBox.Text = String.Empty
AddressTextBox.Text = String.Empty
ProvinceText.Text = String.Empty
ZipcodeText.Text = String.Empty
MobileText.Text = String.Empty
PhoneText.Text = String.Empty
End Sub
Public Sub binddgv() <<<<[u] เงื่อนไขเวลา binding กับ ดาต้ากิด ต้องเพิ่มตรงไหนค่ะ ถ้าเอาแค่ สถานะที่ต้องการ โชว์[/u]
Dim con As New SqlConnection(strCon)
con.Open()
Dim strSql As String = "SELECT Employees.ID,MainInstitute.InsName,Employees.TitleName,Employees.EmpName,Employees.EmpLname,"
strSql = strSql & "Employees.Address,Employees.Province,Employees.Zipcode,Employees.Mobile,Employees.Phone,Employees.Annotation "
strSql = strSql & "FROM Employees,MainInstitute "
strSql = strSql & "WHERE Employees.ID_Ins = MainInstitute.ID_Ins "
Dim command As New SqlCommand(strSql, con)
Dim adapter As New SqlDataAdapter(command)
Dim ds As New DataSet()
adapter.Fill(ds, "EmpIns")
EmpDG.DataSource = ds.Tables("EmpIns")
Dim headers() As String = {"รหัส", "หน่วยงาน", "คำหน้าชื่อ", "ชื่อ", "นามสกุล", "ที่อยู่", "จังหวัด", "รหัสไปรษณีย์", "เบอร์มือถือ", "เบอร์โทรศัพท์", "หมายเหตุ"}
For i = 0 To headers.Length - 1
EmpDG.Columns(i).HeaderText = headers(i)
Next
con.Close()
End Sub
Public Sub addCombo() 'เอาค่าจาก ตาราง MainInstitute ใส่ลง combobox
Dim con As New SqlConnection(strCon)
con.Open()
Dim sql As String = "SELECT * FROM MainInstitute"
Dim command As New SqlCommand(sql, con)
Dim adapter As New SqlDataAdapter(command)
Dim ds As New DataSet()
End Sub
Public Sub AutoID()
Dim con As New SqlConnection(strCon)
con.Open()
Dim strsql As String = String.Empty
strsql = "select max(ID) + 1 as ID from Employees"
Dim da As New SqlDataAdapter()
da.SelectCommand = New SqlCommand(strsql, con)
Dim ds As New DataSet()
Dim rid As Integer = 0
da.Fill(ds)
If (ds.Tables(0).Rows.Count > 0) Then
If ds.Tables(0).Rows(0)("ID").ToString() <> String.Empty Then
rid = CInt(ds.Tables(0).Rows(0)("ID").ToString())
Else
rid = 1
End If
Else
rid = 1
End If
IDTextBox.Text = rid.ToString("00")
End Sub
Private Sub btnAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAll.Click
binddgv()
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Dim con As New SqlConnection(strCon)
con.Open()
If TextBox1.Text = String.Empty Then
MessageBox.Show("กรุณากรอกชื่อที่ต้องการค้นหา", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
TextBox1.Focus()
End If
Dim strsql As String
strsql = "SELECT Employees.ID,MainInstitute.InsName,Employees.TitleName,Employees.EmpName,Employees.EmpLname,Employees.Address,Employees.Province,Employees.Zipcode,Employees.Mobile,Employees.Phone"
strsql = strsql & " FROM Employees,MainInstitute Where Employees.ID_Ins=MainInstitute.ID_Ins and Employees.EmpName LIKE '%" & TextBox1.Text & "%'"
Dim command As New SqlCommand(strsql, con)
Dim adapter As New SqlDataAdapter(command)
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
ClrText()
AutoID()
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
If ComboIns.Text.Trim() = String.Empty Then
MessageBox.Show("กรุณาระบุหน่วยงาน", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ComboIns.Focus()
Exit Sub
End If
If ComboTitle.Text.Trim() = String.Empty Then
MessageBox.Show("กรุณาระบุคำหน้าชื่อ", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ComboTitle.Focus()
Exit Sub
End If
If EmpNameTextBox.Text.Trim() = String.Empty Then
MessageBox.Show("กรุณากรอกชื่อ", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
EmpNameTextBox.Focus()
Exit Sub
End If
If EmpLnameTextBox.Text.Trim() = String.Empty Then
MessageBox.Show("กรุณากรอกนามสกุล", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
EmpLnameTextBox.Focus()
Exit Sub
End If
If AddressTextBox.Text.Trim() = String.Empty Then
MessageBox.Show("กรุณากรอกที่อยู่", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
AddressTextBox.Focus()
Exit Sub
End If
If ProvinceText.Text.Trim() = String.Empty Then
MessageBox.Show("กรุณากรอกจังหวัด", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ProvinceText.Focus()
Exit Sub
End If
If ZipcodeText.Text.Trim() = String.Empty Then
MessageBox.Show("กรุณากรอกรหัสไปรษณีย์", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ZipcodeText.Focus()
Exit Sub
End If
If MessageBox.Show("คุณต้องการเพิ่มพนักงานชื่อ " & EmpNameTextBox.Text & " ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
Dim con As New SqlConnection(strCon)
con.Open()
Dim sqlAdd As String = "INSERT INTO Employees(ID,ID_Ins,TitleName,EmpName,EmpLname,Address,Province,Zipcode,Mobile,Phone)VALUES"
sqlAdd = sqlAdd & "('" & IDTextBox.Text & "','" & ComboIns.SelectedValue & "','" & ComboTitle.Text & "','" & EmpNameTextBox.Text & "','" & EmpLnameTextBox.Text & "','" & AddressTextBox.Text & "','" & ProvinceText.Text & "','" & ZipcodeText.Text & "','" & MobileText.Text & "','" & PhoneText.Text & "')"
Dim command As New SqlCommand(sqlAdd, con)
Try
command.ExecuteNonQuery()
con.Close()
MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
binddgv()
ClrText()
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim con As New SqlConnection(strCon)
con.Open()
If MessageBox.Show("คุณต้องการแก้ไขข้อมูลพนักงานชื่อ " & EmpNameTextBox.Text & " ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
IDTextBox.Enabled = True
Dim sqlEdit As String = " UPDATE Employees SET "
sqlEdit = sqlEdit & "ID_Ins ='" & ComboIns.SelectedValue & "',"
sqlEdit = sqlEdit & "TitleName='" & ComboTitle.Text & "', "
sqlEdit = sqlEdit & "EmpName='" & EmpNameTextBox.Text & "', "
sqlEdit = sqlEdit & "EmpLname='" & EmpLnameTextBox.Text & "', "
sqlEdit = sqlEdit & "Address='" & AddressTextBox.Text & "', "
sqlEdit = sqlEdit & "Province='" & ProvinceText.Text & "', "
sqlEdit = sqlEdit & "Zipcode='" & ZipcodeText.Text & "', "
sqlEdit = sqlEdit & "Mobile='" & MobileText.Text & "', "
sqlEdit = sqlEdit & "Phone= '" & PhoneText.Text & "' "
sqlEdit = sqlEdit & "WHERE ID = '" & IDTextBox.Text & "' "
Dim command As New SqlCommand(sqlEdit, con)
Try
command.ExecuteNonQuery()
con.Close()
MessageBox.Show("แก้ไขข้อมูลเรียบร้อยเเล้ว")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
binddgv()
ClrText()
End Sub
ส่วนของปุ่ม Delete ทำไว้ คร่าวๆ คะ T_T
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If MessageBox.Show("คุณต้องการลบข้อมูลพนักงานชื่อ " & EmpNameTextBox.Text & " ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
Dim con As New SqlConnection(strCon)
con.Open()
Dim sql As String = "UPDATE Employees SET Status ='Inactive' WHERE ID = '" & IDTextBox.Text & "' "
Dim command As New SqlCommand(sql, con)
Try
command.ExecuteNonQuery()
con.Close()
MessageBox.Show("ลบข้อมูลเรียบร้อยเเล้ว")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
binddgv()
ClrText()
End Sub
Private Sub EmpDG_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles EmpDG.CellClick
If e.RowIndex > -1 Then
IDTextBox.Text = EmpDG.Rows(e.RowIndex).Cells(0).Value.ToString()
ComboIns.Text = EmpDG.Rows(e.RowIndex).Cells(1).Value.ToString()
ComboTitle.Text = EmpDG.Rows(e.RowIndex).Cells(2).Value.ToString()
EmpNameTextBox.Text = EmpDG.Rows(e.RowIndex).Cells(3).Value.ToString()
EmpLnameTextBox.Text = EmpDG.Rows(e.RowIndex).Cells(4).Value.ToString()
AddressTextBox.Text = EmpDG.Rows(e.RowIndex).Cells(5).Value.ToString()
ProvinceText.Text = EmpDG.Rows(e.RowIndex).Cells(6).Value.ToString()
ZipcodeText.Text = EmpDG.Rows(e.RowIndex).Cells(7).Value.ToString()
MobileText.Text = EmpDG.Rows(e.RowIndex).Cells(8).Value.ToString()
PhoneText.Text = EmpDG.Rows(e.RowIndex).Cells(9).Value.ToString()
End If
End Sub
End Class
Tag : .NET, Ms SQL Server 2005, Win (Windows App), VB.NET
Public Sub binddgv() <<<< เงื่อนไขเวลา binding กับ ดาต้ากิด ต้องเพิ่มตรงไหนค่ะ ถ้าเอาแค่ สถานะที่ต้องการ โชว์
Dim con As New SqlConnection(strCon)
con.Open()
Dim strSql As String = "SELECT Employees.ID,MainInstitute.InsName,Employees.TitleName,Employees.EmpName,Employees.EmpLname,"
strSql = strSql & "Employees.Address,Employees.Province,Employees.Zipcode,Employees.Mobile,Employees.Phone,Employees.Annotation "
strSql = strSql & "FROM Employees,MainInstitute "
strSql = strSql & "WHERE Employees.ID_Ins = MainInstitute.ID_Ins "
Dim command As New SqlCommand(strSql, con)
Dim adapter As New SqlDataAdapter(command)
Dim ds As New DataSet()
adapter.Fill(ds, "EmpIns")
EmpDG.DataSource = ds.Tables("EmpIns")
Dim headers() As String = {"รหัส", "หน่วยงาน", "คำหน้าชื่อ", "ชื่อ", "นามสกุล", "ที่อยู่", "จังหวัด", "รหัสไปรษณีย์", "เบอร์มือถือ", "เบอร์โทรศัพท์", "หมายเหตุ"}
For i = 0 To headers.Length - 1
EmpDG.Columns(i).HeaderText = headers(i)
Next
con.Close()
End Sub
Public Sub addCombo() 'เอาค่าจาก ตาราง MainInstitute ใส่ลง combobox
Dim con As New SqlConnection(strCon)
con.Open()
Load balance : Server 05
Code (VB.NET)
Quote:
