Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Reflection ' เรียกใช้ซ้ำได้
Imports System.Text
Public Class frmRental
Public GenCodeExperienceId As String
Public timess As String
Public datess As String
Public strSQL As String
Public intNumRow As Integer
Public Conn As SqlConnection
Public com, com2 As SqlCommand
Dim dr As SqlDataReader
Dim dt As DataTable
Public sb As New StringBuilder()
Public bs As New BindingSource()
Public strConn As String
Dim AtCustomer, AtCar As New AutoCompleteStringCollection()
Dim da, da2, da3, da4, da5, da6, da7, da8, da9, da10, da11, da12 As SqlDataAdapter
Dim ds, ds2, ds3, ds4, ds5, ds6, ds7, ds8, ds9, ds10, ds11, ds12 As New DataSet()
Public tb_Name As String = "tbRental"
Public fieldId As String = "RentalID"
Public field1 As String = "CustomerID"
Public field2 As String = "FLName"
Public field3 As String = "CarID"
Public field4 As String = "DateRental"
Public field5 As String = "DateReturn"
Public th As String = "การเช่า"
Public cm As String = "ลูกค้า"
Sub automode()
With txtCustomerID
.AutoCompleteSource = AutoCompleteSource.CustomSource
.AutoCompleteCustomSource = AtCustomer
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
End With
With txtCarID
.AutoCompleteSource = AutoCompleteSource.CustomSource
.AutoCompleteCustomSource = AtCar
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
End With
End Sub
Sub readdata()
ReadConnectSQLServer()
Dim sqlCustomer As String
sqlCustomer = "SELECT [CustomerID],[NameCategoryRental],[SexName],[FLName],[CardID],[Address],[Telephone] FROM [tbCustomer] ORDER BY [CustomerID]"
com = New SqlCommand()
Dim sqlCar As String
sqlCar = "SELECT [CarID],[CategoryCarName],[BrandName],[ModelName],[ColorName],[Cost] FROM [tbCar] ORDER BY [CarID]"
com2 = New SqlCommand()
Me.Cursor = Cursors.WaitCursor
With com
.CommandText = sqlCustomer
.CommandType = CommandType.Text
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dt = New DataTable()
dt.Load(dr)
dr.Close()
For Each drw As DataRow In dt.Rows
AtCustomer.Add(drw.Item("CustomerID").ToString())
Next
Else
dr.Close()
End If
End With
With com2
.CommandText = sqlCar
.CommandType = CommandType.Text
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dt = New DataTable()
dt.Load(dr)
dr.Close()
For Each drw As DataRow In dt.Rows
AtCar.Add(drw.Item("CarID").ToString())
Next
Else
dr.Close()
End If
End With
Me.Cursor = Cursors.Default
End Sub
Sub ReadConnectSQLServer()
'ประกาศค่าตัวแปรรอเรียกใช้งานตัวแปรสำหรับต่อ DB
Dim nameserver, dbname, user, pass As String
'อ่านค่าจาก bin/debug/XConnection.ini ใน ที่อยู่โปรแกรม
Dim reader As StreamReader = File.OpenText("" & Application.StartupPath & "TaxiConnection.ini")
'Line ที่ 1
nameserver = reader.ReadLine
'Line ที่ 2
dbname = reader.ReadLine
'Line ที่ 3
user = reader.ReadLine
'Line ที่ 4
pass = reader.ReadLine
'Line ที่ 5
strConn = reader.ReadLine 'ชุดคำสั่งในการต่อ sqlserver อยู่บันทัดนี้
Conn = New SqlConnection() ' ต่อDB
With Conn 'ตรวจสอบการต่อ DB
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn 'นำชุดคำสั่งในการต่อ sqlserver มาใช้
.Open() 'เปิดใช้งาน DB
End With
End Sub
Private Sub txtCustomerID_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs)
If e.KeyCode = Keys.Enter Then
For Each drw As DataRow In dt.Rows
If drw.Item(0).ToString() = txtCustomerID.Text Then
txtCustomerID.Text = drw.Item(0).ToString()
txtCategoryRental.Text = drw.Item(1).ToString()
txtSex.Text = drw.Item(2).ToString()
txtName.Text = drw.Item(4).ToString()
txtCardID.Text = drw.Item(5).ToString()
txtAddress.Text = drw.Item(7).ToString()
txtTelephone.Text = drw.Item(8).ToString()
Exit For
Else
txtCustomerID.Text = ""
txtCategoryRental.Text = ""
txtSex.Text = ""
txtName.Text = ""
txtCardID.Text = ""
txtAddress.Text = ""
txtTelephone.Text = ""
End If
Next
txtCarID.Focus()
End If
End Sub
Private Sub txtCarID_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCarID.KeyDown
If txtCustomerID.Text.Trim() = "" Then Exit Sub
If e.KeyCode = Keys.Enter Then
sb.Remove(0, sb.Length)
sb.Append("SELECT CarID,CategoryCarName,BrandName,ModelName,ColorName,Cost")
sb.Append(" FROM tbCar")
sb.Append(" WHERE (CarID=@CarID)")
Dim sqlCar As String = sb.ToString()
Dim dtCar As DataTable
With com
.CommandType = CommandType.Text
.CommandText = sqlCar
.Parameters.Clear()
.Parameters.Add("@CarID", SqlDbType.NChar).Value = txtCarID.Text.Trim()
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dtCar = New DataTable()
dtCar.Load(dr)
txtCarID.Text = dtCar.Rows(0).Item("CarID").ToString()
lblCategoryCar.Text = dtCar.Rows(0).Item("CategoryCarName").ToString()
lblBrand.Text = dtCar.Rows(0).Item("BrandName").ToString()
lblModel.Text = dtCar.Rows(0).Item("ModelName").ToString()
lblColor.Text = dtCar.Rows(0).Item("ColorName").ToString()
lblCost.Text = dtCar.Rows(0).Item("Cost").ToString()
DateTimePicker1.Focus()
Else
MessageBox.Show("รหัสลูกค้าที่คุณป้อน ไม่มี !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearCarData()
txtCustomerID.Focus()
End If
End With
dr.Close()
End If
End Sub
Private Sub DataGridView_CellMouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView.CellMouseUp
If e.RowIndex = -1 Then
Exit Sub
End If
With DataGridView
If .Rows.Item(e.RowIndex).Cells(1).Value.ToString() = "" Then
Exit Sub
Else
txtCustomerID.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString()
ReadConnectSQLServer()
Dim mysqlstr = "SELECT * FROM tbRental where RentalID ='" & txtRentalID.Text & "'"
da5 = New SqlDataAdapter(mysqlstr, Conn)
da5.Fill(ds5, "tbRental")
ds5.Tables("tbRental").Clear()
da5.Fill(ds5, "tbRental")
txtRentalID.Text = ds5.Tables("tbRental").Rows(0).Item(0)
txtCustomerID.Text = ds5.Tables("tbRental").Rows(0).Item(1)
txtName.Text = ds5.Tables("tbRental").Rows(0).Item(2)
txtCarID.Text = ds5.Tables("tbRental").Rows(0).Item(3)
DateTimePicker1.Text = ds5.Tables("tbRental").Rows(0).Item(4)
DateTimePicker2.Text = ds5.Tables("tbRental").Rows(0).Item(5)
txtRentalID.Focus()
txtCustomerID.SelectAll()
End If
End With
End Sub
End Class
Tag : Ms SQL Server 2008, Win (Windows App), VB.NET, VS 2010 (.NET 4.x)
Private Sub LoadAutoCompleteCustomerID()
Try
Dim ObjSKU As New ms_Customer(ms_Customer.enuOperation_Type.SEARCH)
ObjSKU.SelectAllData()
Dim ListCustomerID As New AutoCompleteStringCollection
For irow As Integer = 0 To ObjSKU.DataTable.Rows.Count - 1
ListCustomerID.Add(ObjSKU.DataTable.Rows(irow)("Customer_Id").ToString)
Next
txtCustomer_Id.AutoCompleteMode = AutoCompleteMode.SuggestAppend
txtCustomer_Id.AutoCompleteSource = AutoCompleteSource.CustomSource
txtCustomer_Id.AutoCompleteCustomSource = ListCustomerID
Catch ex As Exception
Throw ex
End Try
End Sub