我正在制作一個 POS 風格的系統，這是我用于存盤活動訂單資料并將資料輸入資料庫的代碼。這應該都可以作業，但是我在“Push”IF 陳述句中的 cmd.ExecuteNonQuery() 上遇到錯誤。
它給了我一個 SQLite 錯誤 20：資料型別不匹配。
為什么？
TIA
Imports Microsoft.Data.Sqlite
Public Class CurrentOrder
Public Shared OrderID As Integer
Public Shared Items As New ArrayList
Public Shared ItemsString As String
Public Shared CustName As String
Public Shared Table As Integer
Public Shared Cost As Double 'How much the restaurant will have to pay to make the meal
Public Shared Price As Double 'How much the customer will pay for this order
Public Shared Sub Database(ByVal Mode As String)
Dim Connection As New SqliteConnection("Data Source = Database.db")
Dim SQLcommand As String
Dim CMD As New SqliteCommand
'ItemsString = ""
If Items.Count = 0 Then
MessageBox.Show("Please add items to order", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
If Mode = "Push" Then
For i = 0 To Items.Count - 1
ItemsString = ItemsString Items(i) 'Concatatanation to take a list to a string
Next
Order.Label3.Text = ItemsString
SQLcommand = "INSERT INTO Orders VALUES ('@OrderID', '@ItemsString', '@CustName', '@Table', '@Cost', '@Price')" 'SQL Push Statement
Try
CMD.Connection = Connection
Connection.Open()
CMD.CommandText = SQLcommand
CMD.ExecuteNonQuery() 'Error 20: Datatype mismatch
Connection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
ElseIf Mode = "Pull" Then
SQLcommand = ("SELECT * FROM Orders WHERE OrderID = " & OrderID) 'SQL Pull Statement
Try
CMD.Connection = Connection
Connection.Open()
CMD.CommandText = SQLcommand
Dim reader As SqliteDataReader = CMD.ExecuteReader()
While reader.Read()
Order.Label3.Text = reader("ItemID") & ", " & reader("Name") & ", " & reader("Price")
End While
reader.Close()
Connection.Close()
Catch e As Exception
MessageBox.Show(e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
End Sub
End Class
uj5u.com熱心網友回復：
在表中，您有不同的資料型別，并且您正在發送不同的資料型別。例如，在表中，您使用 Int 并傳遞十進制。
uj5u.com熱心網友回復：
在處理金錢時，最好使用Decimal而不是Double.
我不知道為什么這個類有所有這些Shared專案。在呼叫代碼中創建一個實體可能會更好。
ArrayList不應在新代碼中使用。看看是否List(Of T)會填滿賬單。
欄位通常不Public以Classe秒。你想Property成為Public。
像這樣的類沒有用戶界面。您不想顯示班級中的訊息框。在表單類中顯示來自用戶界面代碼的訊息框。
不要將多個專案存盤在資料庫的單個欄位中。您應該有一個Orders帶有主鍵OrderID的OrderDetails表和一個用于專案的表。
您的 for 回圈只是將所有專案組合在一起。如果沒有任何分隔符，您將無法再次將它們分開。此外，vb.net 中的連接運算子是&.
對于插入陳述句，通常最好先列出欄位名稱，然后列出值。我不知道您為什么將引數名稱括在單引號中。
您可以將CommandTextandConnection直接傳遞給 Command 的建構式。
您在插入中參考了幾個引數，但您從未將它們添加到引數集合或為它們設定值。
顯然，您提供的是唯一值OrderID，這不是自動編號欄位。
使用 aSub New將您的訂單物件置于穩定狀態，以便將其資料添加到資料庫中。
Public Class CurrentOrder
Public Property OrderID As Integer
Public Property Items As New List(Of String)
Public Property CustName As String
Public Property Table As Integer
Public Property Cost As Decimal 'How much the restaurant will have to pay to make the meal
Public Property Price As Decimal 'How much the customer will pay for this order
Public Sub New(ID As Integer, Itms As List(Of String), Name As String, TBL As Integer, Cst As Decimal, Prc As Decimal)
OrderID = ID
Items = Itms
CustName = Name
Table = TBL
Cost = Cst
Price = Prc
End Sub
End Class
的CurrentOrder類是從完全分開DataAccess類。
Public Class DataAccess
Private ConStr As String = "Data Source = Database.db"
Public Sub SaveOrderAndDetails(O As CurrentOrder)
Dim OrderInsert = "INSERT INTO Orders (OrderID, CustName, Table, Cost, Price) VALUES (@OrderID, @CustName, @Table, @Cost, @Price)"
Using Connection As New SQLiteConnection(ConStr)
Using CMD As New SQLiteCommand(OrderInsert, Connection)
CMD.Parameters.Add("@OrderID", DbType.Int32).Value = O.OrderID
CMD.Parameters.Add("@CustName", DbType.String).Value = O.CustName
CMD.Parameters.Add("@Table", DbType.Int32).Value = O.Table
CMD.Parameters.Add("@Cost", DbType.Decimal).Value = O.Cost
CMD.Parameters.Add("@Price", DbType.Decimal).Value = O.Price
Connection.Open()
CMD.ExecuteNonQuery()
End Using
Dim DetailsInsert = "Insert Into OrderDetails (OrderID, Item) Values (@OrderId, @Item)"
Using cmd As New SQLiteCommand(DetailsInsert, Connection)
cmd.Parameters.Add("@OrderId", DbType.Int32).Value = O.OrderID
cmd.Parameters.Add("@Item", DbType.String)
For Each s In O.Items
cmd.Parameters("@Item").Value = s
cmd.ExecuteNonQuery()
Next
End Using
End Using
End Sub
Public Function GetOrderByID(id As Integer) As DataTable
Dim dt As New DataTable
Dim SqlCommand = "SELECT * FROM Orders WHERE OrderID = @ID"
Using cn As New SQLiteConnection(ConStr),
cmd As New SQLiteCommand(SqlCommand, cn)
cmd.Parameters.Add("@ID", DbType.Int32).Value = id
cn.Open()
Using reader = cmd.ExecuteReader
dt.Load(reader)
End Using
End Using
Return dt
End Function
Public Function GetOrderDetailByID(id As Integer) As DataTable
Dim dt As New DataTable
Dim sql = "Select Item From OrderDetails Where OrderID = @ID"
Using cn As New SQLiteConnection(ConStr),
cmd As New SQLiteCommand(sql, cn)
cmd.Parameters.Add("@ID", DbType.Int32).Value = id
cn.Open()
Using reader = cmd.ExecuteReader
dt.Load(reader)
End Using
End Using
Return dt
End Function
End Class
最后，在用戶界面中類的使用，在本例中為 windows 表單。
Private Sub Button1_Click() Handles Button1.Click
Dim lst As New List(Of String)
lst.Add("Bacon")
lst.Add("Eggs")
Dim newOrder As New CurrentOrder(7, lst, "John", 4, 12.03D, 22D)
Dim DA As New DataAccess
DA.SaveOrderAndDetails(newOrder)
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim DA As New DataAccess
Dim dt = DA.GetOrderByID(7)
'Fill various text boxes with the DataTable fields
Dim dt2 = DA.GetOrderDetailByID(7)
DataGridView1.DataSource = dt2
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/406395.html
標籤：