Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click
FlagAction = "EDIT"
If TxtSaleID.Text.Trim = "" Then
MessageBox.Show("กรุณาค้นหาใบขายที่ต้องการแก้ไขก่อน!!", "การแก้ไข")
Exit Sub
End If
If MessageBox.Show("คุณต้องการแก้ไขใบขายใช่หรือไม่!!", "การแก้ไข", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
Exit Sub
End If
If MessageBox.Show("คุณต้องการแก้ไขข้อมูลใช่หรือไม่....ระบบจะทำการคืนสินค้ากลับสู่สต๊อก กรุณารอสักครู่", "ยืนยัน", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No Then
Exit Sub
End If
With cnn
If .State = ConnectionState.Open Then .Close()
.Open()
End With
Dim Del As String = "Delete from TBSale where SaleId = '" & TxtSaleID.Text & "'"
With cmm
.CommandType = CommandType.Text
.CommandText = Del
.Connection = cnn
.ExecuteNonQuery()
End With
For i As Integer = 0 To lviProduct.Items.Count - 1
With cnn
Dim Up As String
Up = "Update TBProduct set UnitInStock = UnitInStock + @Unit where ProId=@PID "
With cmm
.CommandText = Up
.Parameters.Clear()
.Parameters.Add("@Unit", SqlDbType.Int).Value = CInt(lviProduct.Items(i).SubItems(5).Text)
.Parameters.Add("@PID", SqlDbType.NVarChar).Value = (lviProduct.Items(i).SubItems(1).Text)
.ExecuteNonQuery()
End With
End With
Next
MessageBox.Show("กรุณาเลือกรายการสินค้าที่ต้องการเพิ่มใหม่ค่ะ")
lviProduct.Items.Clear()
Dim J As Integer
num = 0
For J = 0 To lviProduct.Items.Count - 1
num = num + 1
lviProduct.Items(J).SubItems(0).Text = num.ToString
Next
End Sub
Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Dim sb As StringBuilder
If TxtSaleID.Text.Trim = "" Then
MessageBox.Show("กรุณากดปุ่มเพิ่มใบขายก่อน!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
If TxtCusID.Text.Trim = "" Then
MessageBox.Show("กรุณาเลือกลูกค้าก่อน!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
If TxtProID.Text.Trim = "" Then
MessageBox.Show("กรุณาเลือสินค้าที่ต้องการ!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
If MessageBox.Show("คุณต้องการบันทึกข้อมูลใช่หรือไม่", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No Then
Exit Sub
End If
'-- บันทึก ลงตาราง sale
Dim SaveSale As String = ""
sb = New StringBuilder()
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO TBSale (SaleId,CusId,SaleDate,NetVat,NetTotal)")
sb.Append(" VALUES (@SaleID,@CusID,@SaleDate,@NetVat,@NetTotal)")
SaveSale = sb.ToString()
With cmm
.Parameters.Clear()
.CommandText = SaveSale
.Connection = cnn
cnn.Open()
.Parameters.AddWithValue("@SaleID", TxtSaleID.Text)
.Parameters.AddWithValue("@CusID", TxtCusID.Text)
.Parameters.Add("@SaleDate", SqlDbType.DateTime).Value = TxtSaleDate.Text
.Parameters.Add("@NetVat", SqlDbType.Float).Value = CSng(lblNetVAT.Text)
.Parameters.Add("@NetTotal", SqlDbType.Float).Value = CSng(Lbltotal.Text)
.ExecuteNonQuery()
End With
'บันทึกลงตาราง saledetail
Dim d As Integer = 0
For d = 0 To lviProduct.Items.Count - 1
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO TBSaleDetail(SaleId,ProId,Amount,SalePrice,Total)")
sb.Append(" Values(@Sid,@Pid,@amount,@SalePrice,@Total)")
SaveSale = sb.ToString()
With cmm
.Parameters.Clear()
.CommandText = SaveSale
.Parameters.AddWithValue("@Sid", TxtSaleID.Text)
.Parameters.AddWithValue("@Pid", lviProduct.Items(d).SubItems(1).Text)
.Parameters.AddWithValue("@amount", CInt(lviProduct.Items(d).SubItems(4).Text))
.Parameters.AddWithValue("@SalePrice", CSng(lviProduct.Items(d).SubItems(3).Text))
.Parameters.Add("@Total", SqlDbType.Float).Value = CSng(lviProduct.Items(d).SubItems(5).Text)
.ExecuteNonQuery()
End With
' ตัดสต๊อก
sb.Remove(0, sb.Length())
sb.Append("UPDATE TBProduct")
sb.Append(" SET UnitInstock = @Unit ")
sb.Append(" WHERE ProId = @Pid ")
Dim sqlun As String
sqlun = sb.ToString
With cmm
.Parameters.Clear()
.CommandText = sqlun
.Parameters.AddWithValue("@Unit", SqlDbType.Int).Value = CInt(lviProduct.Items(d).SubItems(6).Text)
.Parameters.AddWithValue("@Pid", lviProduct.Items(d).SubItems(1).Text)
.ExecuteNonQuery()
End With
Next
MessageBox.Show("บันทึกข้อมูลเรียบร้อบแล้วค่ะ!!!")
BtnClear_Click(sender, e)
cnn.Close()
End Sub
แล้วก้อเขียน sub ขึ้นมาอ่ะค่ะ Code (VB.NET)
Private Sub EDIT()
With cnn
If .State = ConnectionState.Open Then .Close()
.Open()
End With
Dim sql As String = ""
Select Case FlagAction
Case "ADD"
sql = ("INSERT INTO TBSale (SaleId,CusId,SaleDate,NetVat,NetTotal)")
sql &= (" VALUES (@SID,@CID,@SaleDate,@NetVat,@NetTotal)")
Case "EDIT"
sql = ("UPDATE TBSale ")
sql &= (" SET CusId = @CID, SaleDate = @SaleDate,NetVat = @NetVat,NetTotal = @NetTotal")
sql &= (" WHERE(SaleId = @SID) ")
End Select
With cmm
.CommandType = CommandType.Text
.CommandText = sql
.Connection = cnn
.Parameters.Clear()
.Parameters.Add("@SID", SqlDbType.NVarChar).Value = TxtSaleID.Text.Trim()
.Parameters.Add("@CID", SqlDbType.NVarChar).Value = TxtCusID.Text.Trim()
.Parameters.Add("@SaleDate", SqlDbType.DateTime).Value = TxtSaleDate.Text.Trim()
.Parameters.Add("@NetVat", SqlDbType.Money).Value = CSng(lblNetVAT.Text.Trim())
.Parameters.Add("@NetTotal", SqlDbType.Money).Value = CSng(Lbltotal.Text.Trim())
.ExecuteNonQuery()
End With
End Sub
Private Sub tsAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsAdd.Click
Dim sqlLastID As String = ""
sqlLastID = "select top 1 SaleId from TBsale Order by SaleId DESC"
With cnn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
With cmm
.CommandText = sqlLastID
.Connection = cnn
dr = .ExecuteReader()
End With
If dr.HasRows Then
Do While dr.Read
Dim n As String = dr.Item("SaleId")
Dim g As String
g = n.Substring(2, 3)
Dim newid As String
newid = "S-" & (CInt(g) + 1).ToString("000")
TxtSaleID.Text = newid
TxtSaleID.ReadOnly = True
Loop
Else
TxtSaleID.Text = "S-001"
End If
TxtSaleDate.Text = Now.Date
dr.Close()
End Sub
- ปรับฟิลด์ SaleId ใน TBsale ให้เป็น INT และตั้งเป็น auto increment
และเปลี่ยนโค้ดเป็น Code (VB.NET) ตัดตัวแปร g ออก
Private Sub tsAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsAdd.Click
Dim sqlLastID As String = ""
sqlLastID = "select top 1 SaleId from TBsale Order by SaleId DESC"
With cnn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
With cmm
.CommandText = sqlLastID
.Connection = cnn
dr = .ExecuteReader()
End With
If dr.HasRows Then
Do While dr.Read
Dim n As String = dr.Item("SaleId")
Dim newid As String
newid = "S-" & (CInt(n) + 1).ToString("000")
TxtSaleID.Text = newid
TxtSaleID.ReadOnly = True
Loop
Else
TxtSaleID.Text = "S-001"
End If
TxtSaleDate.Text = Now.Date
dr.Close()
End Sub
และ Code (VB.NET) แก้ที่ บันทึก ลงตาราง sale ตรง Insert
Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Dim sb As StringBuilder
If TxtSaleID.Text.Trim = "" Then
MessageBox.Show("กรุณากดปุ่มเพิ่มใบขายก่อน!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
If TxtCusID.Text.Trim = "" Then
MessageBox.Show("กรุณาเลือกลูกค้าก่อน!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
If TxtProID.Text.Trim = "" Then
MessageBox.Show("กรุณาเลือสินค้าที่ต้องการ!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
If MessageBox.Show("คุณต้องการบันทึกข้อมูลใช่หรือไม่", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No Then
Exit Sub
End If
'-- บันทึก ลงตาราง sale
Dim SaveSale As String = ""
sb = New StringBuilder()
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO TBSale (CusId,SaleDate,NetVat,NetTotal)")
sb.Append(" VALUES (@CusID,@SaleDate,@NetVat,@NetTotal)")
SaveSale = sb.ToString()
With cmm
.Parameters.Clear()
.CommandText = SaveSale
.Connection = cnn
cnn.Open()
.Parameters.AddWithValue("@CusID", TxtCusID.Text)
.Parameters.Add("@SaleDate", SqlDbType.DateTime).Value = TxtSaleDate.Text
.Parameters.Add("@NetVat", SqlDbType.Float).Value = CSng(lblNetVAT.Text)
.Parameters.Add("@NetTotal", SqlDbType.Float).Value = CSng(Lbltotal.Text)
.ExecuteNonQuery()
End With
'บันทึกลงตาราง saledetail
Dim d As Integer = 0
For d = 0 To lviProduct.Items.Count - 1
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO TBSaleDetail(SaleId,ProId,Amount,SalePrice,Total)")
sb.Append(" Values(@Sid,@Pid,@amount,@SalePrice,@Total)")
SaveSale = sb.ToString()
With cmm
.Parameters.Clear()
.CommandText = SaveSale
.Parameters.AddWithValue("@Sid", TxtSaleID.Text)
.Parameters.AddWithValue("@Pid", lviProduct.Items(d).SubItems(1).Text)
.Parameters.AddWithValue("@amount", CInt(lviProduct.Items(d).SubItems(4).Text))
.Parameters.AddWithValue("@SalePrice", CSng(lviProduct.Items(d).SubItems(3).Text))
.Parameters.Add("@Total", SqlDbType.Float).Value = CSng(lviProduct.Items(d).SubItems(5).Text)
.ExecuteNonQuery()
End With
' ตัดสต๊อก
sb.Remove(0, sb.Length())
sb.Append("UPDATE TBProduct")
sb.Append(" SET UnitInstock = @Unit ")
sb.Append(" WHERE ProId = @Pid ")
Dim sqlun As String
sqlun = sb.ToString
With cmm
.Parameters.Clear()
.CommandText = sqlun
.Parameters.AddWithValue("@Unit", SqlDbType.Int).Value = CInt(lviProduct.Items(d).SubItems(6).Text)
.Parameters.AddWithValue("@Pid", lviProduct.Items(d).SubItems(1).Text)
.ExecuteNonQuery()
End With
Next
MessageBox.Show("บันทึกข้อมูลเรียบร้อบแล้วค่ะ!!!")
BtnClear_Click(sender, e)
cnn.Close()
End Sub
Load balance : Server 04
