insert ข้อมูลจาก Gridview มากกว่า 1 Row เจอ Error: Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate key in object 'dbo.Orders'. The duplicate key value is (OR1500002).
The statement has been terminated.
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim i As Integer
Dim AuOrderID As String = ORID()
cmd = New SqlCommand(strSQL, SqlCon)
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & AuOrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
End With
End With
Next
SqlCon.Close()
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
'*** AUTOKEY ***'
Public Function ORID() As String
Dim iMAX As Integer = 1
Dim intNum As String = ""
Dim cmd As New SqlCommand
With cmd
.Connection = SqlCon
.CommandText = "SELECT MAX(RIGHT(orderID,5)) As orderID FROM Orders"
SqlCon.ConnectionString = con
SqlCon.Open()
If IsDBNull(.ExecuteScalar()) = True Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "0000" & iMAX
Else
iMAX = .ExecuteScalar() + 1
End If
If iMAX <= 9 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "0000" & iMAX
ElseIf iMAX <= 99 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "000" & iMAX
ElseIf iMAX <= 999 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "00" & iMAX
ElseIf iMAX <= 9999 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "0" & iMAX
ElseIf iMAX <= 99999 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & iMAX
End If
End With
SqlCon.Close()
Return intNum
End Function
Tag : .NET, Ms SQL Server 2008, Web (ASP.NET), VB.NET
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim i As Integer
cmd = New SqlCommand(strSQL, SqlCon)
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & ORID() & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
End With
End With
Next
SqlCon.Close()
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
'*** AUTOKEY ***'
Public Function ORID() As String
Dim iMAX As Integer = 1
Dim intNum As String = ""
Dim cmd As New SqlCommand
With cmd
.Connection = SqlCon
.CommandText = "SELECT MAX(RIGHT(orderID,5)) As orderID FROM Orders"
SqlCon.ConnectionString = con
SqlCon.Open()
If IsDBNull(.ExecuteScalar()) = True Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "0000" & iMAX
Else
iMAX = .ExecuteScalar() + 1
End If
If iMAX <= 9 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "0000" & iMAX
ElseIf iMAX <= 99 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "000" & iMAX
ElseIf iMAX <= 999 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "00" & iMAX
ElseIf iMAX <= 9999 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & "0" & iMAX
ElseIf iMAX <= 99999 Then
intNum = "OR" & CStr(Now.Year).Substring(2) & iMAX
End If
End With
SqlCon.Close()
Return intNum
End Function
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim i As Integer
cmd = New SqlCommand(strSQL, SqlCon)
Dim AuOrderID As String
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
AuOrderID = ORID()
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & AuOrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
strSQL = " INSERT INTO ORderDetails(?????) VALUES(??????) " ' เอา AuOrderID มา INSERT แบบบ้านๆเลยครับ
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
Next
SqlCon.Close()
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
ประมาณนี้ครับ
ถ้า ORderDetails มีหลายๆ rows ก็ for เหมือน Orders ครับ
Violation of PRIMARY KEY constraint 'PK_OrderDetails'. Cannot insert duplicate key in object 'dbo.OrderDetails'. The duplicate key value is (OR1500005).
The statement has been terminated.
Public Function Sumamount(orderID As Integer ) As Double
Dim _Sum As Double = 0
Dim cmd As New SqlCommand
With cmd
.Connection = SqlCon
.CommandText = "SELECT Sum(amount) As _Sumamount FROM orderDetails Where orderID = " & orderID
SqlCon.ConnectionString = con
SqlCon.Open()
If IsDBNull(.ExecuteScalar()) <> True Then
_Sum= .ExecuteScalar() ' อาจจะต้อง convert เป็น Double นะครับ
End If
End With
SqlCon.Close()
Return _Sum
End Function
ไม่แนะนำให้นำเอา Sum ลงใน database ไม่สะดวกในการแก้ไข
ให้โชว์ตอนดึงออกมาใช้งานก็พอ
แต่หากยังต้องการแบบนั้น
ลองใช้ for loop และ if เก็บค่า amount ที่มี orderID ตามต้องการได้ครับ
Dim _amount As Double = 0
for
if orderID = OR1500001 ' บวกเพิ่ม
next
แต่หากยังต้องการแบบนั้น
ลองใช้ for loop และ if เก็บค่า amount ที่มี orderID ตามต้องการได้ครับ
Dim _amount As Double = 0
for
if orderID = OR1500001 ' บวกเพิ่ม
next
Dim lst As List(Of OrderEmp)
Function checkOrderEmp(_emp As String) As OrderEmp
Dim _OrderEmp As OrderEmp
Dim str As String = ""
For Each oe As OrderEmp In lst
If oe.Emp = _emp Then
_OrderEmp = oe
str = _emp
Exit For
End If
Next
If lst.Count < 0 Or str = "" Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = _emp
_OrderEmp = lst(lst.Count - 1)
End If
Return _OrderEmp
End Function
'ปรับใหม่แบบนี้
Private Sub btnsubmit_Click(sender As Object, e As EventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim orKeyID As String
cmd = New SqlCommand()
Dim strSQL As String
Dim _OrderEmp As OrderEmp
For i As Integer = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
_OrderEmp = checkOrderEmp(CType(dt.Rows(i)("empID"), String))
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & _OrderEmp.OrderID & "',"
strSQL &= "'" & _OrderEmp.OrderID & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
orKeyID = Orkey()
strSQL = "INSERT INTO OrderDetails(orKey,orderID,storefood_rkey,optionDetails,qty,unitPrice,amount,empID)"
strSQL &= "VALUES('" & orKeyID & "','" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("foodID"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("option"), String) & "',"
strSQL &= "'" & CType(dt.Rows(i)("qty"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("price"), Decimal) & "',"
strSQL &= "'" & CType(dt.Rows(i)("amount"), Decimal) & "',"
strSQL &= "'" & _OrderEmp.Emp & "')"
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
SqlCon.Close()
Next
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
มันติด Error อ่ะค่ะ Object reference not set to an instance of an object.
Line 151: Dim _OrderEmp As OrderEmp
Line 152: Dim str As String = "" Line 153: For Each oe As OrderEmp In lst
Line 154: If oe.Emp = _emp Then
Line 155: _OrderEmp = oe
Public Class OrderEmp
Public Emp As String
Public OrderID As String
End Class
Dim lst As List(Of OrderEmp)
Function checkOrderEmp(_emp As String) As OrderEmp
Dim _OrderEmp As OrderEmp = Nothing
Dim str As String = ""
For Each oe As OrderEmp In lst
If oe.Emp = _emp Then
_OrderEmp = oe
str = _emp
Exit For
End If
Next
If lst.Count < 0 Or str = "" Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = _emp
_OrderEmp = lst(lst.Count - 1)
End If
Return _OrderEmp
End Function
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim orKeyID As String
Dim i As Integer
cmd = New SqlCommand()
Dim strSQL As String
Dim _OrderEmp As OrderEmp
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
_OrderEmp = checkOrderEmp(CType(dt.Rows(i)("empID"), String))
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
orKeyID = orKey()
strSQL = "INSERT INTO OrderDetails(orKey,orderID,storefood_rkey,optionDetails,qty,unitPrice,amount)"
strSQL &= "VALUES('" & orKeyID & "','" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("foodID"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("option"), String) & "',"
strSQL &= "'" & CType(dt.Rows(i)("qty"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("price"), Decimal) & "',"
strSQL &= "'" & CType(dt.Rows(i)("amount"), Decimal) & "')"
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
SqlCon.Close()
Next
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
ตอนนี้ติดปัญหาคือถ้ามีข้อมูลที่เป็น empID เดียวกันสั่งอาหาร มากกว่า 1 รายการมันจะขึ้นเออเร่อ Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate key in object 'dbo.Orders'. The duplicate key value is (OR1500002).
The statement has been terminated. ค่ะ
Code (VB.NET)
Public Class OrderEmp
Public Emp As String
Public OrderID As String
End Class
Dim lst As New List(Of OrderEmp)
Function checkOrderEmp(_emp As String) As OrderEmp
Dim _OrderEmp As OrderEmp = Nothing
Dim str As String = ""
For Each oe As OrderEmp In lst
If oe.Emp = _emp Then
_OrderEmp = oe
str = _emp
Exit For
End If
Next
If lst.Count < 0 Or str = "" Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = _emp
_OrderEmp = lst(lst.Count - 1)
End If
Return _OrderEmp
End Function
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim orKeyID As String
Dim i As Integer
cmd = New SqlCommand()
Dim strSQL As String
Dim _OrderEmp As OrderEmp
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
_OrderEmp = checkOrderEmp(CType(dt.Rows(i)("empID"), String))
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
orKeyID = orKey()
strSQL = "INSERT INTO OrderDetails(orKey,orderID,storefood_rkey,optionDetails,qty,unitPrice,amount)"
strSQL &= "VALUES('" & orKeyID & "','" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("foodID"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("option"), String) & "',"
strSQL &= "'" & CType(dt.Rows(i)("qty"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("price"), Decimal) & "',"
strSQL &= "'" & CType(dt.Rows(i)("amount"), Decimal) & "')"
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
SqlCon.Close()
Next
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
Public Class OrderEmp
Public Emp As String
Public OrderID As String
End Class
Dim lst As New List(Of OrderEmp)
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim orKeyID As String
Dim i As Integer
cmd = New SqlCommand()
Dim strSQL As String
Dim _OrderEmp As OrderEmp
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
For Each oe As OrderEmp In lst
If oe.Emp = _emp Then
_OrderEmp = oe
str = _emp
Exit For
End If
Next
If lst.Count < 0 Or str = "" Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = _emp
_OrderEmp = lst(lst.Count - 1)
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
End If
orKeyID = orKey()
strSQL = "INSERT INTO OrderDetails(orKey,orderID,storefood_rkey,optionDetails,qty,unitPrice,amount)"
strSQL &= "VALUES('" & orKeyID & "','" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("foodID"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("option"), String) & "',"
strSQL &= "'" & CType(dt.Rows(i)("qty"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("price"), Decimal) & "',"
strSQL &= "'" & CType(dt.Rows(i)("amount"), Decimal) & "')"
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
SqlCon.Close()
Next
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
Public Class OrderEmp
Public Emp As String
Public OrderID As String
End Class
Dim lst As New List(Of OrderEmp)
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim orKeyID As String
Dim i As Integer
cmd = New SqlCommand()
Dim strSQL As String
Dim _OrderEmp As OrderEmp
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
For Each oe As OrderEmp In lst
If oe.Emp = CType(dt.Rows(i)("empID"), String) Then
_OrderEmp = oe
str = _emp
Exit For
End If
Next
If lst.Count < 0 Or str = "" Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = _emp
_OrderEmp = lst(lst.Count - 1)
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
End If
orKeyID = orKey()
strSQL = "INSERT INTO OrderDetails(orKey,orderID,storefood_rkey,optionDetails,qty,unitPrice,amount)"
strSQL &= "VALUES('" & orKeyID & "','" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("foodID"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("option"), String) & "',"
strSQL &= "'" & CType(dt.Rows(i)("qty"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("price"), Decimal) & "',"
strSQL &= "'" & CType(dt.Rows(i)("amount"), Decimal) & "')"
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
SqlCon.Close()
Next
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim orKeyID As String
Dim i As Integer
cmd = New SqlCommand()
Dim strSQL As String
Dim str As String = ""
Dim _emp As String = ""
Dim _OrderEmp As OrderEmp = Nothing
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
For Each oe As OrderEmp In lst
If oe.Emp = CType(dt.Rows(i)("empID"), String) Then
_OrderEmp = oe
str = _emp
Exit For
End If
Next
If lst.Count < 0 Or str = "" Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = _emp
_OrderEmp = lst(lst.Count - 1)
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.Year.ToString() & "-" & DateTime.Now.Month.ToString() & "-" & DateTime.Now.Day.ToString() & "',"
strSQL &= "'" & DateTime.Now.Year.ToString() & "-" & DateTime.Now.Month.ToString() & "-" & DateTime.Now.Day.ToString() & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
End If
orKeyID = orKey()
strSQL = "INSERT INTO OrderDetails(orKey,orderID,storefood_rkey,optionDetails,qty,unitPrice,amount)"
strSQL &= "VALUES('" & orKeyID & "','" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("foodID"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("option"), String) & "',"
strSQL &= "'" & CType(dt.Rows(i)("qty"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("price"), Decimal) & "',"
strSQL &= "'" & CType(dt.Rows(i)("amount"), Decimal) & "')"
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
strSQL = "UPDATE StoreFood SET QTY = QTY - '" & CType(dt.Rows(i)("qty"), Integer) & "' WHERE rkey ='" & CType(dt.Rows(i)("foodID"), Integer) & "'"
With cmd
.CommandText = strSQL
.ExecuteNonQuery()
End With
SqlCon.Close()
Next
dt.Clear()
GridView1.DataSource = dt
GridView1.DataSource = Nothing
End Sub
For Each oe As OrderEmp In lst
If oe.Emp = CType(dt.Rows(i)("empID"), String) Then //ลองเช็ค CType(dt.Rows(i)("empID") แล้วมันให้ empID ตามรายการที่สั่งค่ะ ไม่เข้าใจว่าตรงนี้มันทำงานยังไงช่วยอธิบายหน่อยนะคะ
_OrderEmp = oe
str = _emp
Exit For
End If
Next
If lst.Count < 0 Or str = "" Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = _emp
_OrderEmp = lst(lst.Count - 1)
ตอนนี้ empID เดียวกันลงเป็น OrderID เดียวกันแล้วค่ะ
ลองเทสแบบสั่ง empID เดียวกันสองรายการลง DB ปกติค่ะ แต่พอลองสั่ง 2 รายการโดยที่ empID ต่างกันขึ้นเออเร่อ The connection was not closed. The connection's current state is open. ข้อมูลลง DB แค่ empID เดียว คือ empID ที่สั่งเป็นคนแรก
Code (VB.NET)
Protected Sub btnsubmit_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles btnsubmit.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim SqlCon As New SqlConnection(con)
Dim da As New SqlDataAdapter
Dim orKeyID As String
Dim i As Integer
Dim _dt As New DataTable
cmd = New SqlCommand()
Dim strSQL As String
Dim _emp As String = ""
Dim _OrderEmp As OrderEmp
Try
For i = 0 To dt.Rows.Count - 1
_OrderEmp = Nothing
With dt.Rows(i)
With cmd
For Each oe As OrderEmp In lst
If oe.Emp = CType(dt.Rows(i)("empID"), String) Then ' เชคข้อมูลใน lst
_OrderEmp = oe
Exit For
'ถ้ามีข้อมูลใน lst แล้วให้เรียกใช้ _OrderEmp = oe แล้ว Exit For
End If
Next
'กรณี เชคด้านบนแล้วไม่เจอ *** ยังไม่มีรายชื่อ ใน lst ให้ Add(New OrderEmp())
If lst.Count < 0 Or IsNothing(_OrderEmp) Then
lst.Add(New OrderEmp())
lst(lst.Count - 1).OrderID = ORID()
lst(lst.Count - 1).Emp = CType(dt.Rows(i)("empID"), String)
_OrderEmp = lst(lst.Count - 1)
MsgBox(CType(dt.Rows(i)("empID"), String))
strSQL = "INSERT INTO Orders(orderID,empID,orderDate,createdDate,createdBy)"
strSQL &= "VALUES('" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("empID"), String) & "',"
strSQL &= "'" & DateTime.Now.Year.ToString() & "-" & DateTime.Now.Month.ToString() & "-" & DateTime.Now.Day.ToString() & "',"
strSQL &= "'" & DateTime.Now.Year.ToString() & "-" & DateTime.Now.Month.ToString() & "-" & DateTime.Now.Day.ToString() & "',"
strSQL &= "'" & lblempid.Text & "')"
SqlCon.Open()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = SqlCon
.ExecuteNonQuery()
End If
orKeyID = orKey()
strSQL = "INSERT INTO OrderDetails(orKey,orderID,storefood_rkey,optionDetails,qty,unitPrice,amount)"
strSQL &= "VALUES('" & orKeyID & "','" & _OrderEmp.OrderID & "',"
strSQL &= "'" & CType(dt.Rows(i)("foodID"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("option"), String) & "',"
strSQL &= "'" & CType(dt.Rows(i)("qty"), Integer) & "',"
strSQL &= "'" & CType(dt.Rows(i)("price"), Decimal) & "',"
strSQL &= "'" & CType(dt.Rows(i)("amount"), Decimal) & "')"
.CommandText = strSQL
.ExecuteNonQuery()
End With
End With
Next
Catch ex As Exception
MsgBox("เกิดข้อผิดพลาด" + ex.Message) 'แจ้ง Error
Finally
If SqlCon.State = ConnectionState.Open Then SqlCon.Close()
End Try
End Sub
For i = 0 To dt.Rows.Count - 1
With dt.Rows(i)
With cmd
_OrderEmp = Nothing
For Each oe As OrderEmp In lst
If oe.Emp = CType(dt.Rows(i)("empID"), String) Then ' เชคข้อมูลใน lst
_OrderEmp = oe
Exit For
'ถ้ามีข้อมูลใน lst แล้วให้เรียกใช้ _OrderEmp = oe แล้ว Exit For
End If
Next
รบกวนอีกนิดนะค่ะ ตอนนี้ใช้ function นี้ในการ insert total ลงตาราง orders แต่มันลงเป็น 0.00 อ่ะค่ะ
Code (VB.NET)
Public Function Sumamount(orderID As Integer ) As Double
Dim _Sum As Double = 0
Dim cmd As New SqlCommand
With cmd
.Connection = SqlCon
.CommandText = "SELECT Sum(amount) As _Sumamount FROM orderDetails Where orderID = " & orderID
SqlCon.ConnectionString = con
SqlCon.Open()
If IsDBNull(.ExecuteScalar()) <> True Then
_Sum= .ExecuteScalar() ' อาจจะต้อง convert เป็น Double นะครับ
End If
End With
SqlCon.Close()
Return _Sum
End Function
Dim checkname As String = ""
Dim checkempID As String = ""
Dim check As String = ""
Dim checkempGrade As String = ""
Dim checkremain As String
Dim price As String
Dim amount As String
Protected Sub btnadd_Click(sender As Object, e As ImageClickEventArgs) Handles btnadd.Click
Dim dt As DataTable = DirectCast(ViewState("Order"), DataTable)
Dim dr As DataRow = dt.NewRow
btnsubmit.Visible = True 'เมื่อกดปุ่มรูปรถเข็นปุ่ม Submit จะแสดงขึ้นมา
If ddlstore2.SelectedItem.Value = "0" Or ddlmenu.SelectedItem.Value = "0" Or txtquantity.Text = String.Empty Or txtunitPrice.Text = String.Empty Then
messagetxt()
ElseIf txtstock.Text <= "0" And txtstock.Text <> String.Empty Then
messageusedup()
ElseIf chkReplace.Checked And txtempID.Text = String.Empty Then
messagetxtempID()
Else
price = Decimal.Parse(txtunitPrice.Text.Trim)
amount = Decimal.Parse((txtquantity.Text) * (txtunitPrice.Text))
'เช็คการเลือก Option เพิ่มเติม
For Each li As ListItem In chkOption.Items
If li.Selected Then
If check = "" Then
check = (li.Text)
Else
check += ("," + li.Text)
End If
End If
Next
If chkNewoption.Checked = True Then
check = txtoption.Text
End If
For Each li As ListItem In chkOption.Items
If li.Selected AndAlso chkNewoption.Checked = True Then
If check = "" Then
check = (txtoption.Text)
Else
check += ("," + li.Text)
End If
End If
Next
'เช็คว่ามีการสั่งซื้อแทนหรือไม่
If chkReplace.Checked = True Then
checkempID = txtempID.Text.Trim
checkname = lblnamefor.Text.Trim + " " + lbllastnamefor.Text.Trim
checkempGrade = lblempgradefor.Text.Trim
checkremain = lblremainfor.Text.Trim 'เครดิตของคนสั่งซื้อแทนที่ดึงมาจากDB โดยยังไม่ได้คำนวณ
Else '
checkempID = lblempid.Text.Trim
checkname = lblempname.Text.Trim + " " + lblemplastname.Text.Trim
checkempGrade = lblempgrade.Text.Trim
checkremain = lblremain.Text.Trim 'เครดิตของคนสั่งซื้อแทนที่ดึงมาจากDB โดยยังไม่ได้คำนวณ
End If
dr("empID") = checkempID
dr("name") = checkname
dr("foodID") = ddlmenu.SelectedItem.Value
dr("foodName") = ddlmenu.SelectedItem.Text.Trim
dr("store") = ddlstore2.SelectedItem.Text.Trim
dr("option") = check
dr("QTY") = Integer.Parse(txtquantity.Text.Trim)
dr("price") = FormatNumber(price, 2)
dr("amount") = FormatNumber(amount, 2)
dr("empGrade") = checkempGrade
'dr("remain") =
dt.Rows.Add(dr)
ViewState("Order") = dt
Call BindGrid()
'การคำนวณผลรวมของการสั่งซื้อสินค้าทั้งหมด
GridView1.FooterRow.Cells(7).Text = String.Format("{0:0}", dt.Compute("sum(QTY)", ""))
GridView1.FooterRow.Cells(9).Text = String.Format("{0:C}", dt.Compute("sum(amount)", ""))
Call ResetAll()
End If
End Sub
For i = 0 To dt.Rows.Count - 1
'Dim _NewRemain As Integer
_remain = Nothing
For Each rm As remain In Relst
If rm.Emp = checkempID Then
_remain = rm
Exit For
End If
Next
Next
If Relst.Count < 0 Or IsNothing(_remain) Then
Relst.Add(New remain())
Relst(Relst.Count - 1).OrderID = ORID()
Relst(Relst.Count - 1).Emp = checkempID
Relst(Relst.Count - 1).Remain = checkremain - amount ' checkremain คือตัวแปรที่เก็บค่า remain ของพนักงานแต่ละคน
_remain = Relst(Relst.Count - 1)
For i = 0 To dt.Rows.Count - 1
Dim NewRemain As Integer
Dim _amount As Decimal = amount
Dim _NewRemain As Integer
NewRemain = Relst(Relst.Count - 1).Remain
_NewRemain = NewRemain - _amount
_remain.Remain = _NewRemain
MsgBox(NewRemain)
Next
MsgBox(checkremain)
End If
For i = 0 To dt.Rows.Count - 1
Dim NewRemain As Integer
Dim _amount As Decimal = amount
Dim _NewRemain As Integer
NewRemain = Relst(Relst.Count - 1).Remain
_NewRemain = NewRemain - _amount
_remain.Remain = _NewRemain
MsgBox(NewRemain)
Next
For i = 0 To dt.Rows.Count - 1
_remain = Nothing
For Each rm As remain In Relst
If rm.Emp = checkempID Then
_remain = rm
Exit For
End If
Next
Next
If Relst.Count < 0 Or IsNothing(_remain) Then
Relst.Add(New remain())
Relst(Relst.Count - 1).Emp = checkempID
Relst(Relst.Count - 1).Amount = amount
Relst(Relst.Count - 1).Remain = checkremain - amount
_remain = Relst(Relst.Count - 1)
Else
End If
For i = 0 To dt.Rows.Count - 1
_remain = Nothing
For Each rm As remain In Relst
If rm.Emp = checkempID Then
_remain = rm 'คนเก่า
Exit For
End If
Next
Next
If Relst.Count < 0 Or IsNothing(_remain) Then
Relst.Add(New remain())
Relst(Relst.Count - 1).Emp = checkempID
Relst(Relst.Count - 1).Amount = amount 'มีไว้ทำอะไรรึ
Relst(Relst.Count - 1).Remain = checkremain
_remain = Relst(Relst.Count - 1) 'คนใหม่
Else
End If
'เวลา ลบ ก็
_remain.Remain = _remain.Remain - amount
For i = 0 To dt.Rows.Count - 1
_remain = Nothing
For Each rm As remain In Relst
If rm.Emp = checkempID Then
_remain = rm
Exit For
End If
Next
Next
If Relst.Count < 0 Or IsNothing(_remain) Then
Relst.Add(New remain())
Relst(Relst.Count - 1).Emp = checkempID
'Relst(Relst.Count - 1).Amount = amount
Relst(Relst.Count - 1).Remain = checkremain
_remain = Relst(Relst.Count - 1)
Else
End If
_remain.Remain = _remain.Remain - amount
'For i = 0 To dt.Rows.Count - 1
' NewRemain = Relst(Relst.Count - 1).Remain
' _NewRemain = NewRemain - _amount
' _remain.Remain = _NewRemain
' MsgBox(NewRemain)
'Next
MsgBox(checkremain)
MsgBox(_remain.Remain)
For i = 0 To dt.Rows.Count - 1
'_remain = Nothing
For Each rm As remain In Relst ' เชคข้อมูลใน Relst
If rm.Emp = checkempID Then
_remain = rm 'คนเก่า
'ถ้ามีข้อมูลใน Relst แล้วให้เรียกใช้ _remain = rm แล้ว Exit For
Exit For
End If
Next
Next
'กรณี เชคด้านบนแล้วไม่เจอ *** ยังไม่มีรายชื่อ ใน Relst ให้ Relst.Add(New remain())
If Relst.Count < 0 Or IsNothing(_remain) Then
Relst.Add(New remain())
Relst(Relst.Count - 1).Emp = checkempID
Relst(Relst.Count - 1).Remain = checkremain
_remain = Relst(Relst.Count - 1) 'คนใหม่
End If
_remain.Remain = checkremain - amount
MsgBox(checkempID)
MsgBox(checkremain)
For Each rm As remain In Relst ' เชคข้อมูลใน Relst
If rm.Emp = checkempID Then
_remain = rm 'คนเก่า
MsgBox(checkempID)
'ถ้ามีข้อมูลใน Relst แล้วให้เรียกใช้ _remain = rm แล้ว Exit For
Exit For
End If
Next
Dim item As Integer = 0
For i As Integer = 0 To Relst.Count - 1
If Relst(i) = checkempID Then
item = i
Exit For
End If
Next
'ไม่มีก็เพิ่ม
item = Relst.Count - 1
Relst(item).Remain = ???
Load balance : Server 04
ขอถามอีกคำถามนะค่ะว่า ถ้าจะ insert ข้อมูลลงอีก 1 ตาราง คือ ORderDetails โดยใช้ OrderID เดียวกับตาราง Orders ต้องทำยังไงค่ะ
