001.Imports System.Data
002.Imports System.Data.SqlClient
003.Imports System.IO
004.Imports System.Reflection
005.Imports System.Text
006.Public Class frmRental
007. Public GenCodeExperienceId As String
008. Public timess As String
009. Public datess As String
010. Public strSQL As String
011. Public intNumRow As Integer
012. Public Conn As SqlConnection
013. Public com, com2 As SqlCommand
014. Dim dr As SqlDataReader
015. Dim dt As DataTable
016. Public sb As New StringBuilder()
017. Public bs As New BindingSource()
018. Public strConn As String
019.
020. Dim AtCustomer, AtCar As New AutoCompleteStringCollection()
021. Dim da, da2, da3, da4, da5, da6, da7, da8, da9, da10, da11, da12 As SqlDataAdapter
022. Dim ds, ds2, ds3, ds4, ds5, ds6, ds7, ds8, ds9, ds10, ds11, ds12 As New DataSet()
023.
024. Public tb_Name As String = "tbRental"
025. Public fieldId As String = "RentalID"
026. Public field1 As String = "CustomerID"
027. Public field2 As String = "FLName"
028. Public field3 As String = "CarID"
029. Public field4 As String = "DateRental"
030. Public field5 As String = "DateReturn"
031.
032. Public th As String = "การเช่า"
033. Public cm As String = "ลูกค้า"
034.
035. Sub automode()
036. With txtCustomerID
037. .AutoCompleteSource = AutoCompleteSource.CustomSource
038. .AutoCompleteCustomSource = AtCustomer
039. .AutoCompleteMode = AutoCompleteMode.SuggestAppend
040. End With
041. With txtCarID
042. .AutoCompleteSource = AutoCompleteSource.CustomSource
043. .AutoCompleteCustomSource = AtCar
044. .AutoCompleteMode = AutoCompleteMode.SuggestAppend
045. End With
046. End Sub
047. Sub readdata()
048. ReadConnectSQLServer()
049. Dim sqlCustomer As String
050. sqlCustomer = "SELECT [CustomerID],[NameCategoryRental],[SexName],[FLName],[CardID],[Address],[Telephone] FROM [tbCustomer] ORDER BY [CustomerID]"
051. com = New SqlCommand()
052. Dim sqlCar As String
053. sqlCar = "SELECT [CarID],[CategoryCarName],[BrandName],[ModelName],[ColorName],[Cost] FROM [tbCar] ORDER BY [CarID]"
054. com2 = New SqlCommand()
055. Me.Cursor = Cursors.WaitCursor
056. With com
057. .CommandText = sqlCustomer
058. .CommandType = CommandType.Text
059. .Connection = Conn
060. dr = .ExecuteReader()
061. If dr.HasRows Then
062. dt = New DataTable()
063. dt.Load(dr)
064. dr.Close()
065.
066. For Each drw As DataRow In dt.Rows
067. AtCustomer.Add(drw.Item("CustomerID").ToString())
068. Next
069. Else
070. dr.Close()
071. End If
072. End With
073. With com2
074. .CommandText = sqlCar
075. .CommandType = CommandType.Text
076. .Connection = Conn
077. dr = .ExecuteReader()
078. If dr.HasRows Then
079. dt = New DataTable()
080. dt.Load(dr)
081. dr.Close()
082.
083. For Each drw As DataRow In dt.Rows
084. AtCar.Add(drw.Item("CarID").ToString())
085. Next
086. Else
087. dr.Close()
088. End If
089. End With
090. Me.Cursor = Cursors.Default
091. End Sub
092. Sub ReadConnectSQLServer()
093.
094.
095. Dim nameserver, dbname, user, pass As String
096.
097. Dim reader As StreamReader = File.OpenText("" & Application.StartupPath & "TaxiConnection.ini")
098.
099. nameserver = reader.ReadLine
100.
101. dbname = reader.ReadLine
102.
103. user = reader.ReadLine
104.
105. pass = reader.ReadLine
106.
107. strConn = reader.ReadLine
108.
109. Conn = New SqlConnection()
110. With Conn
111. If .State = ConnectionState.Open Then .Close()
112. .ConnectionString = strConn
113. .Open()
114. End With
115. End Sub
116. Private Sub txtCustomerID_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs)
117. If e.KeyCode = Keys.Enter Then
118.
119. For Each drw As DataRow In dt.Rows
120. If drw.Item(0).ToString() = txtCustomerID.Text Then
121. txtCustomerID.Text = drw.Item(0).ToString()
122. txtCategoryRental.Text = drw.Item(1).ToString()
123. txtSex.Text = drw.Item(2).ToString()
124. txtName.Text = drw.Item(4).ToString()
125. txtCardID.Text = drw.Item(5).ToString()
126. txtAddress.Text = drw.Item(7).ToString()
127. txtTelephone.Text = drw.Item(8).ToString()
128. Exit For
129. Else
130. txtCustomerID.Text = ""
131. txtCategoryRental.Text = ""
132. txtSex.Text = ""
133. txtName.Text = ""
134. txtCardID.Text = ""
135. txtAddress.Text = ""
136. txtTelephone.Text = ""
137.
138. End If
139. Next
140. txtCarID.Focus()
141. End If
142.End Sub
143.
144. Private Sub txtCarID_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCarID.KeyDown
145. If txtCustomerID.Text.Trim() = "" Then Exit Sub
146.
147. If e.KeyCode = Keys.Enter Then
148. sb.Remove(0, sb.Length)
149. sb.Append("SELECT CarID,CategoryCarName,BrandName,ModelName,ColorName,Cost")
150. sb.Append(" FROM tbCar")
151. sb.Append(" WHERE (CarID=@CarID)")
152. Dim sqlCar As String = sb.ToString()
153.
154. Dim dtCar As DataTable
155. With com
156. .CommandType = CommandType.Text
157. .CommandText = sqlCar
158. .Parameters.Clear()
159. .Parameters.Add("@CarID", SqlDbType.NChar).Value = txtCarID.Text.Trim()
160. .Connection = Conn
161. dr = .ExecuteReader()
162. If dr.HasRows Then
163. dtCar = New DataTable()
164. dtCar.Load(dr)
165.
166. txtCarID.Text = dtCar.Rows(0).Item("CarID").ToString()
167. lblCategoryCar.Text = dtCar.Rows(0).Item("CategoryCarName").ToString()
168. lblBrand.Text = dtCar.Rows(0).Item("BrandName").ToString()
169. lblModel.Text = dtCar.Rows(0).Item("ModelName").ToString()
170. lblColor.Text = dtCar.Rows(0).Item("ColorName").ToString()
171. lblCost.Text = dtCar.Rows(0).Item("Cost").ToString()
172. DateTimePicker1.Focus()
173. Else
174. MessageBox.Show("รหัสลูกค้าที่คุณป้อน ไม่มี !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
175. ClearCarData()
176.
177. txtCustomerID.Focus()
178. End If
179. End With
180. dr.Close()
181. End If
182. End Sub
183. Private Sub DataGridView_CellMouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView.CellMouseUp
184. If e.RowIndex = -1 Then
185. Exit Sub
186. End If
187. With DataGridView
188. If .Rows.Item(e.RowIndex).Cells(1).Value.ToString() = "" Then
189. Exit Sub
190. Else
191. txtCustomerID.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString()
192. ReadConnectSQLServer()
193. Dim mysqlstr = "SELECT * FROM tbRental where RentalID ='" & txtRentalID.Text & "'"
194. da5 = New SqlDataAdapter(mysqlstr, Conn)
195. da5.Fill(ds5, "tbRental")
196. ds5.Tables("tbRental").Clear()
197. da5.Fill(ds5, "tbRental")
198.
199.
200. txtRentalID.Text = ds5.Tables("tbRental").Rows(0).Item(0)
201. txtCustomerID.Text = ds5.Tables("tbRental").Rows(0).Item(1)
202. txtName.Text = ds5.Tables("tbRental").Rows(0).Item(2)
203. txtCarID.Text = ds5.Tables("tbRental").Rows(0).Item(3)
204. DateTimePicker1.Text = ds5.Tables("tbRental").Rows(0).Item(4)
205. DateTimePicker2.Text = ds5.Tables("tbRental").Rows(0).Item(5)
206.
207. txtRentalID.Focus()
208. txtCustomerID.SelectAll()
209.
210. End If
211. End With
212. End Sub
213.End Class
