Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
If txtEmployeeID.Text = "" Then
MessageBox.Show("กรุณาระบุรหัส !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtEmployeeID.Focus()
Exit Sub
End If
If lsvDeviceReturn.Items.Count = 0 Then
MessageBox.Show("กรุณาป้อนรายการอุปกรณ์ที่ต้องการคืน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
If MessageBox.Show("คุณต้องการบันทึกรายการคืนอุปกรณ์ ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
Dim i As Integer = 0
Dim sqlReturn As String = ""
Dim OldBorrowNum As Integer = 0
Dim OldReturnNum As Integer = 0
Dim OldDeviceInShop As Integer = 0
Dim OldDeviceInBorrow As Integer = 0
For i = 0 To lsvDeviceReturn.Items.Count - 1
sqlReturn = "SELECT TransID,SN,DeviceBorrowNum,DeviceReturnNum,"
sqlReturn &= " IsReturn"
sqlReturn &= " FROM BorrowDetail"
sqlReturn &= " WHERE (TransID='" & CStr(lsvDeviceReturn.Items(i).SubItems(3).Text) & "')"
sqlReturn &= " AND (SN='" & CStr(lsvDeviceReturn.Items(i).SubItems(0).Text) & "')"
da.SelectCommand.CommandText = sqlReturn
da.Fill(ds, "Return")
OldBorrowNum = CInt(ds.Tables("Return").Rows(0).Item("DeviceBorrowNum"))
OldReturnNum = CInt(ds.Tables("Return").Rows(0).Item("DeviceReturnNum"))
ds.Tables("Return").Clear()
'คืนไม่ครบ
If CInt(lsvDeviceReturn.Items(i).SubItems(2).Text) < OldBorrowNum Then
sqlReturn &= " DeviceReturnNum=" & OldReturnNum + CInt(lsvDeviceReturn.Items(i).SubItems(2).Text) & ","
sqlReturn &= " DeviceBorrowNum=" & OldBorrowNum - CInt(lsvDeviceReturn.Items(i).SubItems(2).Text)
'คืนครบ
ElseIf CInt(lsvDeviceReturn.Items(i).SubItems(2).Text) = OldBorrowNum Then
sqlReturn &= " DeviceReturnNum=0,"
sqlReturn &= " DeviceBorrowNum=0,"
sqlReturn &= " IsReturn='1'"
End If
Dim comReturn As OleDbCommand = New OleDbCommand
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
With comReturn
.CommandType = CommandType.Text
.CommandText = sqlReturn
.Connection = Conn
.ExecuteNonQuery()
End With
'คืนครบ
If CInt(lsvDeviceReturn.Items(i).SubItems(2).Text) = OldBorrowNum Then
sqlReturn = "SELECT TransID,SN "
sqlReturn &= " FROM BorrowDetail"
sqlReturn &= " WHERE (TransID='" & CStr(lsvDeviceReturn.Items(i).SubItems(3).Text) & "')"
sqlReturn &= " AND (SN='" & CStr(lsvDeviceReturn.Items(i).SubItems(0).Text) & "')"
da.SelectCommand.CommandText = sqlReturn
da.Fill(ds, "ReturnComplete")
ds.Tables("ReturnComplete").Clear()
With comReturn
.CommandText = sqlReturn
.ExecuteNonQuery()
End With
End If
sqlReturn = "SELECT SN,DeviceInShop,DeviceInBorrow "
sqlReturn &= " FROM Device"
sqlReturn &= " WHERE (SN='" & CStr(lsvDeviceReturn.Items(i).SubItems(0).Text) & "')"
da.SelectCommand.CommandText = sqlReturn
da.Fill(ds, "Device")
OldDeviceInShop = CInt(ds.Tables("Device").Rows(0).Item("DeviceInShop"))
OldDeviceInBorrow = CInt(ds.Tables("Device").Rows(0).Item("DeviceInBorrow"))
ds.Tables("Device").Clear()
sqlReturn = "UPDATE Device"
sqlReturn &= " SET DeviceInShop=" & OldDeviceInShop + CInt(lsvDeviceReturn.Items(i).SubItems(2).Text) & ","
sqlReturn &= " DeviceInBorrow=" & OldDeviceInBorrow - CInt(lsvDeviceReturn.Items(i).SubItems(2).Text)
sqlReturn &= " WHERE (SN='" & CStr(lsvDeviceReturn.Items(i).SubItems(0).Text) & "')"
With comReturn
.CommandText = sqlReturn
.ExecuteNonQuery()
End With
Next
MessageBox.Show("บันทึกเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearAllCustomerData()
ClearAllDeviceData()
lsvDeviceReturn.Items.Clear()
txtEmployeeID.Text = ""
txtEmployeeID.Focus()
End If
End Sub
มันขึ้น Error ที่บรรทัด 060 ขึ้นดังนี้ครับ
Additional information: Syntax error (missing operator) in query expression '(TransID='0000001') AND (SN='test') DeviceReturnNum=0, DeviceBorrowNum=0, IsReturn='1''.