001.Option Explicit On
002.Imports System.Text
003.Imports System.Data
004.Imports System.Data.SqlClient
005.
006.Public Class frmCustomer
007.
008. Private ReadOnly Property Conn() As SqlConnection
009. Get
010. Dim ConnToFetch As New SqlConnection(strCon)
011. ConnToFetch.Open()
012. Return ConnToFetch
013. End Get
014. End Property
015.
016. Public Function GetData() As DataView
017. Dim sqlQry = "select * from Customer"
018. Dim ds As New DataSet
019. Dim dv As DataView
020. Try
021. Dim Comm As New SqlCommand()
022. Dim da = New SqlDataAdapter()
023. With Comm
024. .CommandText = sqlQry
025. .Connection = Conn
026. End With
027. da.SelectCommand = Comm
028. da.Fill(ds)
029. dv = ds.Tables(0).DefaultView
030. Catch ex As Exception
031. Throw ex
032. End Try
033. Return dv
034. End Function
035.
036. Private Sub ShowColumnsHeads()
037. If dgvCustomer.RowCount > 0 Then
038. With dgvCustomer
039. .Columns(0).HeaderText = "รหัสลูกค้า"
040. .Columns(1).HeaderText = "ชื่อ"
041. .Columns(2).HeaderText = "นามสกุล"
042. .Columns(3).HeaderText = "ที่อยู่"
043. .Columns(4).HeaderText = "หมายเลขโทรศัพท์"
044. .Columns(5).HeaderText = "E-mail"
045. .Columns(0).Width = 100
046. .Columns(1).Width = 120
047. .Columns(2).Width = 150
048. .Columns(3).Width = 250
049. .Columns(4).Width = 120
050. .Columns(5).Width = 120
051.
052. End With
053. End If
054. End Sub
055. Private Sub frmCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
056. dgvCustomer.DataSource = GetData()
057. ShowColumnsHeads()
058. End Sub
059.
060. Private Sub dgvCustomer_CellMouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvCustomer.CellMouseUp
061. If e.RowIndex = -1 Then Exit Sub
062. With dgvCustomer
063. txtCusID.Text = .Rows.Item(e.RowIndex).Cells("CusID").Value.ToString()
064. txtCusName.Text = .Rows.Item(e.RowIndex).Cells("CusName").Value.ToString()
065. txtCusLastName.Text = .Rows.Item(e.RowIndex).Cells("CusLastName").Value.ToString()
066. txtCusAddress.Text = .Rows.Item(e.RowIndex).Cells("CusAddress").Value.ToString()
067. txtCusPhone.Text = .Rows.Item(e.RowIndex).Cells("CusPhone").Value.ToString()
068. txtCusEmail.Text = .Rows.Item(e.RowIndex).Cells("CusEmail").Value.ToString()
069. txtCusName.Focus()
070. txtCusName.SelectAll()
071. End With
072.
073. End Sub
074.
075. Sub AutoID()
076.
077.
078. Dim tmp As Integer
079. Sql = "SELECT Max(CusID) From Customer"
080. Try
081. Call dbOpen()
082. Com = New SqlCommand(Sql, Conn)
083. reader = Com.ExecuteReader()
084. tmp = reader.FieldCount - 1
085.
086. If tmp < 0 Then
087. txtCusID.Text = "00001"
088. Else
089. While reader.Read
090. txtCusID.Text = ((CInt((reader(0)).ToString.Substring(1))) + 1).ToString("00000")
091. End While
092. End If
093. Catch ex As Exception
094. MsgBox(ex.Message)
095. MsgBox("Error Auto ID")
096. End Try
097. End Sub
098.
099. Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
100. Call AutoID()
101. txtCusName.Focus()
102. End Sub
103.
104. Private Sub InsertData()
105. If MessageBox.Show("คุณต้องการเพิ่มข้อมูลลูกค้าใช่หรืไม่", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
106. tr = Conn.BeginTransaction
107.
108. With sb
109. .Remove(0, sb.Length)
110. .Append("insert into Customer (CusID,CusName,CusLastName,CusAddress,CusPhone,CusEmail)")
111. .Append("values (@CusID,@CusName,@CusLastName,@CusAddress,@CusPhone,@CusEmail)")
112. Dim sqlAdd As String
113. sqlAdd = sb.ToString()
114.
115. With Com
116. .CommandText = sqlAdd
117. .CommandType = CommandType.Text
118. .Connection = Conn
119. .Transaction = tr
120. .Parameters.Clear()
121. .Parameters.Add("@CusID", SqlDbType.Char).Value = txtCusID.Text.Trim()
122. .Parameters.Add("@CusName", SqlDbType.VarChar).Value = txtCusName.Text.Trim()
123. .Parameters.Add("@CusLastName", SqlDbType.VarChar).Value = txtCusLastName.Text.Trim()
124. .Parameters.Add("@CusAddress", SqlDbType.VarChar).Value = txtCusAddress.Text.Trim()
125. .Parameters.Add("@CusPhone", SqlDbType.VarChar).Value = txtCusPhone.Text.Trim()
126. .Parameters.Add("@CusEmail", SqlDbType.VarChar).Value = txtCusEmail.Text.Trim()
127. .ExecuteNonQuery()
128. End With
129. End With
130. End If
131. End Sub
132.
133. Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
134. Call InsertData()
135. End Sub
136.End Class
เยี่ยม
