SQLite Error 20: Datatype mismatch in database insertion-CodePudding

Im making a POS style system and this is my code for storing the data for the active order and also inputting the data into the database. This should all work however I get an error on the cmd.ExecuteNonQuery() in the "Push" IF statement.

It gives me an SQLite error 20: Datatype mismatch.

And idea why?

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

CodePudding user response：

In the table, you have a different data type and you are sending a different datatype. For Example, In the table, you use Int and you pass decimal.

CodePudding user response：

When dealing with money it is better to use Decimal rather than Double.

I don't know why this class has all these Shared items. It would probably be better to just create an instance in the calling code.

ArrayList should not be used in new code. See if List(Of T) will fill the bill.

Fields are not normally Public in Classes. You want a Property to be Public.

Classes like this have no user interface. You don't want to show message boxes from your class. Show the message boxes from you User Interface code in the Form Class.

Do not store several items in a single field in the database. You should have an Orders table with a primary key OrderID and an OrderDetails table for the items.

Your for loop just lumps all the items together. You will not be able to pull them apart again without any delimiter. Also, the concatenation operator in vb.net is &.

For you insert statement it is usually best to list the field names and then the values. I don't know why you're enclosed the parameter names in single quotes.

You can pass the CommandText and the Connection directly to the constructor of the Command.

You have referenced several parameters in your insert but you never added them to the parameters collection or set a value for them.

Apparently, you are providing a unique value for OrderID and this is not an auto number field.

Use a Sub New to put your order object in a stable state ready to have its data added to the database.

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

The CurrentOrder class is completely separate from the DataAccess class.

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

Finally, the usage of the classes in the user interface, in this case a windows form.

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