none
invalid sql statement expected delete insert procedure select or update RRS feed

  • Question

  • Can't seem to find what's wrong, according to my knowledge this should work. Please help ASAP.. Much appreciated.

    Here the code:

    'save the receipt

        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

            Dim MyTransaction As OleDb.OleDbTransaction

            Try

                'create the connection and transaction object

                Dim MyConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(My.Settings.dbConnectionString)

                MyConnection.Open()

                MyTransaction = MyConnection.BeginTransaction

               

    'insert the new receipt

                Dim SQL As String = "insert into receipts (receiptdate, receipttotal) values (:0,:1)"

                Dim CMD1 As New OleDb.OleDbCommand

                CMD1.Connection = MyConnection

                CMD1.Transaction = MyTransaction

                CMD1.CommandText = SQL

                CMD1.Parameters.AddWithValue(":0", Now.Date)

                CMD1.Parameters.AddWithValue("1", TextBox4.Text)

                CMD1.ExecuteNonQuery()

                CMD1.Dispose()

               

    'get the id for the receipt

                SQL = "SELECT max(receiptid) as MAXID from receipts"


                    DimCMD2 As NewOleDb.OleDbCommand

                CMD2.Connection = MyConnection

                CMD2.Transaction = MyTransaction

                CMD2.CommandText = SQL

                Dim ReceiptID As Long = CMD2.ExecuteScalar()

                CMD2.Dispose()

               

    'insert the details of the receipt

                Dim I As Integer

                For I = 0 To DGV2.Rows.Count - 1

                    'get the values

                    Dim Barcode As String = DGV2.Rows(I).Cells(0).Value

                    Dim BuyPrice As Decimal = DGV2.Rows(I).Cells(2).Value

                    Dim SellPrice As Decimal = DGV2.Rows(I).Cells(3).Value

                    Dim ItemCount As Integer = DGV2.Rows(I).Cells(4).Value

                   

    'next create a command

                    Dim CMD3 As New OleDb.OleDbCommand

                    SQL = "inset into ReceiptDetails" & _

                          "(receiptid,barcode,itemcount,itembuyprice,itemsellprice)" & _

                          "values" & _

                          "(:0       ,:1     ,:2      ,:3           ,:4       )"

                    CMD3.Connection = MyConnection

                    CMD3.Transaction = MyTransaction

                    CMD3.CommandText = SQL

                    CMD3.Parameters.AddWithValue(":0", ReceiptID)

                    CMD3.Parameters.AddWithValue(":1", Barcode)

                    CMD3.Parameters.AddWithValue(":2", ItemCount)

                    CMD3.Parameters.AddWithValue(":3", BuyPrice)

                    CMD3.Parameters.AddWithValue(":4", SellPrice)

                    CMD3.ExecuteNonQuery()

                    CMD3.Dispose()

                Next

               

    'all well save the changes

                MyTransaction.Commit()

               

    'close connection

                MyTransaction.Dispose()

                MyConnection.Close()

                MyConnection.Dispose()

            Catch ex As Exception 'THIS IS WHERE I GET THE ERROR

                If MyTransaction IsNot Nothing Then

                    MyTransaction.Rollback()

                End If

                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error")

            End Try

        End Sub


    • Edited by Bemz94 Tuesday, April 24, 2012 5:48 PM
    Tuesday, April 24, 2012 5:46 PM

Answers

  • Hi,

    I was not aware that in oledb,  parameters could be assigned the way you do.

    I thought the way with question marks was the only way:

    "insert into receipts (receiptdate, receipttotal) values (?,?)"

    I've not tested your way, so it may be that it causes the errors.

    Another thing i saw:

    "inset into ReceiptDetails" & _

    should of course be 'insert'. That would definitly cause an error.


    Regards, Nico

    Friday, April 27, 2012 8:56 AM

All replies

  • Hi Bemz94,

    Could you please post the error messsage here?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, April 25, 2012 8:21 AM
    Moderator
  • Hi Bemz94,

    Have you solved the issue? I look forward to hearing from you.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, April 27, 2012 2:15 AM
    Moderator
  • Hi,

    I was not aware that in oledb,  parameters could be assigned the way you do.

    I thought the way with question marks was the only way:

    "insert into receipts (receiptdate, receipttotal) values (?,?)"

    I've not tested your way, so it may be that it causes the errors.

    Another thing i saw:

    "inset into ReceiptDetails" & _

    should of course be 'insert'. That would definitly cause an error.


    Regards, Nico

    Friday, April 27, 2012 8:56 AM