Syntax error in INSERT statement

[Bader100]

Hi all,

Hi i have got a problem with the system i am creating on VB using Microsft Access. I have this error that keeps coming up when I debug my system. I am trying to add data in the database. I cant seem to find the solution to this error.

**An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

**Additional information: Syntax error in INSERT INTO statement.****

If anyone can see what is wrong and please let me know. Thank you.

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
            Dim cmd As New OleDb.OleDbCommand
            If Not cnn.State = ConnectionState.Open Then
                'The line of code below opens the connection to the database if it isnt open
            cnn.Open()
            End If

        cmd.Connection = cnn
        'Check whether to add new or update
        If Me.txtItemID.Tag & "" = "" Then
            'Add new 
            'The line of coding below adds data to table
            cmd.CommandText = "INSERT INTO Product ([Item ID], [Item Name], [Item Type], [Quantity], [Min Shelf Stock], [Purchase Price], [Note]) " & _
                            " VALUES (" & Me.txtItemID.Text & ",'" & Me.txtItemName.Text & "','" & _
                            Me.cboItemType.Text & "','" & Me.txtQuantity.Text & "','" & _
                            Me.txtMinShelfStock.Text & "','" & Me.txtPurchasePrice.Text & "','" & _
                            Me.txtNote.Text & "')"
            cmd.ExecuteNonQuery()
        Else
            'Update data in the table 
            cmd.CommandText = "UPDATE Product " & _
                        " SET Item ID=" & Me.txtItemID.Text & _
                        ", Item Name='" & Me.txtItemName.Text & "'" & _
                        ", Item Type='" & Me.cboItemType.Text & "'" & _
                        ", Quantity='" & Me.txtQuantity.Text & "'" & _
                        ", Min Shelf Stock='" & Me.txtMinShelfStock.Text & "'" & _
                        ", Purchase Price='" & Me.txtPurchasePrice.Text & "'" & _
                        ", Note='" & Me.txtNote.Text & "'" & _
                        " WHERE Item ID=" & Me.txtItemID.Tag
            cmd.ExecuteNonQuery()
        End If
        'Refresh data in list
        RefreshData()
        'Clear the form
        Me.btnClear.PerformClick()

        'The code below closes the connection to the database
        cnn.Close()
    End Sub

[cgeier]

What are the data types for each of the columns in the database?

[Bader100]

Item ID = Auto Number
Item Name = Text
Item Type = Text
Quantity = Number
Min Shelf Stock = Number
Purchase Price = Currency
Note = Text

[cgeier]

Numerical values don't use quotes--only for text values. Remove single-quotes from Quantity, Min Shelf Stock, and Purchase Price.

[Reverend Jim]

You can make the code a lot cleaner and safer by using Parameterized queries. Here is an example using your INSERT

cmd.CommandText = "INSERT INTO Product ([Item ID], [Item Name], " &
                  "[Item Type], [Quantity], [Min Shelf Stock],  " &
                  "[Purchase Price], [Note])  " & 
                  "VALUES (?, ?, ?, ?, ?, ?, ?)"

cmd.Parameters.AddWithValue("@p1",txtItemID.Text)
cmd.Parameters.AddWithValue("@p2",txtItemName.Text)
cmd.Parameters.AddWithValue("@p3",cboItemType.Text)
cmd.Parameters.AddWithValue("@p4",txtQuantity.Text)
cmd.Parameters.AddWithValue("@p5",txtMinShelfStock.Text)
cmd.Parameters.AddWithValue("@p6",txtPurchasePrice.Text)
cmd.Parameters.AddWithValue("@p7",txtNote.Text)

[Sulaiman_1]

con.Open();
cmd = new OleDbCommand("INSERT INTO Product ([Item ID], [Item Name], [Item Type], [Quantity], [Min Shelf Stock], [Purchase Price], [Note]) VALUES (@Item_ID, @Item_Name, @Item_Type, @Quantity, @Min_Shelf_Stock, @Purchase_Price, @Note)",con)
cmd.Parameters.Add("@Item_ID",OleDbType.Numeric).Value= txtItemID.Text 
cmd.Parameters.Add("@Item_Name",OleDbType.VarChar).Value=  txtItemName.Text 
cmd.Parameters.Add("@Item_Type",OleDbType.VarChar).Value= cboItemType.Text 
cmd.Parameters.Add("@Quantity",OleDbType.Numeric).Value=  txtQuantity.Text
cmd.Parameters.Add("@Min_Shelf_Stock",OleDbType.Numeric).Value= txtMinShelfStock.Text 
cmd.Parameters.Add("@Purchase_Price",OleDbType.Currency).Value=  txtPurchasePrice.Text 
cmd.Parameters.Add("@Note",OleDbType.VarChar).Value= txtNote.Text 
cmd.ExecuteNonQuery()
con.Close()

I Hope, the above code will help u.!

[Reverend Jim]

The above code is incorrect. It will work if you are using SqlDB but for OleDb you must use "?" in the prototype query.

[Sulaiman_1]

how could you say as " OleDb you must use "?" "

the above is working fine. Did you try?

[Reverend Jim]

All I know is that when I try

cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname LIKE ?"
cmd.Parameters.AddWithValue("@parm", "D%")

my code works but when I try

cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname LIKE @parm"
cmd.Parameters.AddWithValue("@parm", "D%")

I get Must declare the scalar variable "@parm". If there is something I'm doing wrong I'd like to know what it is so I can correct it.

[cgeier]

Using Parameters with an OleDbCommand or OdbcCommand

...The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC do not support named parameters for passing parameters to an SQL statement or a stored procedure. In this case, you must use the question mark (?) placeholder, as in the following example...

[cgeier]

Although...I also have success with using named parameters with OleDb using the following:

Imports System.Data.OleDb

Public Module AccessDB

    Public dbName As String = "Inventory"
    Public connectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\" & dbName & ".accdb"


    Public Sub SelectProductTbl()

        Try

            Using cn As New OleDbConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "select [Item Id], [Item Name], [Item Type], [Quantity] from [Product] "
                sqlText += "where [Item Type] LIKE @parm"


                'open connection
                cn.Open()

                'create new OleDbCommand
                Using sqlCmd As New OleDbCommand(sqlText, cn)

                    sqlCmd.Parameters.AddWithValue("@parm", "B%")

                    'execute
                    Dim reader As OleDbDataReader = sqlCmd.ExecuteReader()

                    While (reader.Read())
                        Console.WriteLine("selectProductTbl: " & reader(0).ToString() & " " & reader(1).ToString())
                    End While
                End Using

            End Using

        Catch ex As OleDbException
            System.Windows.Forms.MessageBox.Show("Error:: SelectProductTbl: " & ex.Message, "Error - Select Table", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show("Error:: SelectProductTbl: " & ex.Message, "Error - Select Table", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

End Module

[Reverend Jim]

There must be something very different between your system and mine because when I run your code I still just get

Must declare the scalar variable "@parm".

I'm running vb.net (Visual Studio) 2010. According to MSDN, "OleDb doesn't support named parameters."

[cgeier]

Here's a copy of the project. It uses .NET 4.0.

1. Build it.
2. Run it.
3. Click button to create database (CreateDB-Access).
4. Click button to create table (CreateTbl-Access).
5. Click button to insert test data (InsertTbl-Access).
6. Click button to select from Product table (Select-Access).

[Reverend Jim]

When I try to create I get

Error:: CreateDatabase ( Inventory): Class not registered

I don't have Access installed. I thought it might be somethinig I could figure out without having to install anything. I don't need Access (although I do have Office 2003) because I have MS SQL installed. Can you show me anywhere that says OleDB supports named parameters? Even Microsoft says it does not.

[cgeier]

Below is a link to an example using named parameters with OLEDB in Access 2007.

Access 2007 OleDb with parameters example

2007 Office System Driver: Data Connectivity Components

The "Create Database" code uses "ADOX.Catalog". There needs to be a reference to "Microsoft ADO Ext 2.8 for DDl and Security". If you are using XP, ensure you have MDAC 2.8 installed.

How to check for MDAC version

Microsoft Data Access Components (MDAC) Installation

Microsoft Data Access Components (MDAC) 2.8 SP1

Note: If using a 64-bit OS, you must compile for x86 because the data connectivity components are for x86.

[Reverend Jim]

Weird. Perhaps it is something specific to Access. If I ever install it again I'll have to try it out to see for sure.