我正在制作一个POS风格的系统，这是我存储活动订单数据并将数据输入数据库的代码。这一切都应该工作，但是我在cmd上得到了一个错误。"中的ExecuteNonQuery((；"推"；IF声明。
它给了我一个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
处理金钱时，最好使用Decimal而不是Double。
我不知道为什么这个班有这么多Shared项目。最好只是在调用代码中创建一个实例。
新代码中不应使用ArrayList。看看List(Of T)是否会买单。
字段通常不是Classes中的Public。您希望Property是Public。
像这样的类没有用户界面。你不想显示你班上的留言框。在窗体类中显示用户界面代码的消息框。
不要将多个项目存储在数据库的单个字段中。您应该有一个带有主键OrderID的Orders表和一个用于项目的OrderDetails表。
你的for循环只是把所有的项目拼凑在一起。如果没有任何分隔符，您将无法再次将它们分开。另外，vb.net中的串联运算符是&。
对于插入语句，通常最好先列出字段名，然后再列出值。我不知道你为什么把参数名称用单引号括起来。
您可以将CommandText和Connection直接传递给命令的构造函数。
您在插入中引用了多个参数，但从未将它们添加到参数集合或为它们设置值。
显然，您为OrderID提供了一个唯一的值，而这不是一个自动编号字段。
使用Sub 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
在表中，您有不同的数据类型，并且您发送的数据类型不同。例如，在表中，使用Int并传递decimal。