001.Imports System.Data
002.Imports System.Data.SqlClient
003.
004.
005.
006.
007.
008.Public Class FormCustomers
009. Dim strConn As String = "Data Source=prikii;" & "Initial Catalog=Books;Integrated Security=True"
010. Dim Conn As SqlConnection = New System.Data.SqlClient.SqlConnection()
011. Dim ds As DataSet = New DataSet()
012. Dim cmd As SqlCommand = New SqlCommand()
013. Dim rowCount As Integer = 0
014. Dim positionn As Integer = 0
015.
016. Private Sub FormCustomers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
017. Handles MyBase.Load
018. With Conn
019. If .State = ConnectionState.Open Then
020. .Close()
021. .ConnectionString = strConn
022. .Open()
023. End If
024. End With
025. ReadData()
026. FillListBox()
027. Bindings()
028. End Sub
029.
030. Private Sub FormCustomers_FormClosed(ByVal sender As System.Object, ByVal e As System.EventArgs) _
031. Handles MyBase.FormClosed
032. Conn.Close()
033. End Sub
034.
035. Private Sub frmCustomer_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) _
036. Handles Me.Disposed
037. If Conn.State = ConnectionState.Open Then Conn.Close()
038. End Sub
039.
040. Private Sub ReadData()
041. Dim sql As String = "SELECT * FROM Customer"
042.
043. Dim adapter As New SqlDataAdapter(sql, Conn)
044.
045. ds = New DataSet()
046. adapter.Fill(ds, "cust")
047. rowCount = ds.Tables("cust").Rows.Count
048. End Sub
049.
050. Private Sub FillListBox()
051. Dim custname As String = ""
052. ListBox1.Items.Clear()
053. For i = 0 To ds.Tables("cust").Rows.Count - 1
054. custname = ds.Tables("cust").Rows(i)("CustomerName")
055. ListBox1.Items.Add(custname)
056. Next
057. If (ListBox1.Items.Count > 0) Then
058. ListBox1.SelectedIndex = 0
059. End If
060. End Sub
061.
062. Private Sub Bindings()
063. TbxID.DataBindings.Add("Text", ds, "cust.CustomerID")
064. TbxName.DataBindings.Add("Text", ds, "cust.CustomerName")
065. TbxAdrs.DataBindings.Add("Text", ds, "cust.Address")
066. TbxTel.DataBindings.Add("Text", ds, "cust.Telephone")
067. TbxBirth.DataBindings.Add("Text", ds, "cust.Birthday")
068. End Sub
069.
070. Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
071. Handles ListBox1.SelectedIndexChanged
072. Dim index As Integer = ListBox1.SelectedIndex
073. Me.BindingContext(ds, "cust").Position = index
074. positionn = index
075. UpdateStatus()
076. End Sub
077.
078. Private Sub UpdateStatus()
079. ToolStripStatusLabel1.Text = (positionn + 1) & " of " & rowCount
080. End Sub
081.
082. Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
083. Handles TabControl1.SelectedIndexChanged
084. If (TabControl1.SelectedIndex = 1) Then
085. ClearBindings()
086. BtnDelete.Enabled = False
087. BtnSave.Enabled = False
088. ElseIf (TabControl1.SelectedIndex = 0) Then
089. ReadData()
090. If (rowCount = 0) Then
091. Return
092. End If
093. Bindings()
094. BtnDelete.Enabled = True
095. ListBox1.Enabled = True
096. ListBox1.SetSelected(0, True)
097. End If
098. End Sub
099.
100. Private Sub ClearBindings()
101. For Each c As Object In GroupBox1.Controls
102. If (TypeOf c Is TextBox) Then
103. c.Text = ""
104. c.DataBindings.Clear()
105. End If
106. Next
107. End Sub
108.
109. Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
110. Handles BtnSave.Click
111. If (TbxName.Text = "" Or TbxAdrs.Text = "" Or TbxTel.Text = "" Or TbxBirth.Text = "") Then
112. MsgBox("กรุณาใส่ข้อมูลให้ครบ !")
113. Return
114. End If
115. Dim sql As String = ""
116. If (TbxID.Text = "") Then
117. sql = "INSERT INTO Customer("
118. sql &= "CustomerName, Address ,Telephone, Birthday) "
119. sql &= "VALUES(@nm, @adrs, @tel, @birth)"
120. Else
121. sql = "UPDATE Customer SET "
122. sql &= "CustomerName = @nm, Address = @adrs, Telephone = @tel, Birthday = @birth "
123. sql &= "WHERE CustomerID = " & TbxID.Text
124. End If
125. cmd = New SqlCommand(sql, Conn)
126. cmd.Parameters.AddWithValue("nm", TbxName.Text)
127. cmd.Parameters.AddWithValue("adrs", TbxAdrs.Text)
128. cmd.Parameters.AddWithValue("tel", TbxTel.Text)
129. cmd.Parameters.AddWithValue("birth", TbxBirth.Text)
130. Dim affectedRow As Integer = cmd.ExecuteNonQuery()
131. If (affectedRow < 1) Then
132. ToolStripStatusLabel1.Text = "เกิดข้อผิดพลาดในการบันทึกข้อมูล"
133. Else
134. ToolStripStatusLabel1.Text = "ข้อมูลถูกจัดเก็บแล้ว"
135. Dim name As String = TbxName.Text
136. ReadData()
137. FillListBox()
138. ClearBindings()
139. If (TabControl1.SelectedIndex = 0) Then
140. Bindings()
141. End If
142. Dim idx As Integer = ListBox1.FindStringExact(name)
143. If (idx > -1) Then
144. ListBox1.SetSelected(idx, True)
145. End If
146. UpdateStatus()
147. End If
148. End Sub
149.
150. Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
151. If (TbxID.Text = "") Then
152. Return
153. End If
154. If (MsgBox("ต้องการลบข้อมูล ?", MsgBoxStyle.OkCancel) = MsgBoxResult.Cancel) Then
155. Return
156. End If
157. Dim sql As String
158. sql = "DELETE * FROM Order"
159. sql &= "WHERE CustomerName = @cust"
160. cmd = New SqlCommand(sql, Conn)
161. Dim r As Integer = cmd.ExecuteNonQuery()
162. If (r > 0) Then
163. ToolStripStatusLabel1.Text = "ข้อมูลถูกลบแล้ว"
164. End If
165. Dim idx0 As Integer = ListBox1.SelectedIndex
166. ClearBindings()
167. ReadData()
168. FillListBox()
169. Bindings()
170. If (idx0 > 0) Then
171. ListBox1.SetSelected(idx0 - 1, True)
172. End If
173. sql = "DELETE * FROM Order WHERE CustomerName = @cust"
174. cmd = New SqlCommand(sql, Conn)
175. cmd.Parameters.AddWithValue("cust", TbxName.Text)
176. cmd.ExecuteNonQuery()
177. End Sub
178.End Class
