Syntax error in insert statement

Question

1.00/5 (1 vote)

See more:

Hi guys,

I am getting syntax error for below statement. But I can't find it, Please check and let me know if you can find it. Statement is;

Using sqlquery As New OleDb.OleDbCommand("INSERT INTO SFA Inventory(IMEINo,ProductName,Supplier,InvoiceNo,InvoiceDate,MobileNo,AppVersion)VALUES(?,?,?,?,?,?,?)", sqlconn)

What I have tried:

My Entire Code is;

VB

private Sub cmdStockAdd_Click(sender As Object, e As EventArgs) Handles cmdStockAdd.Click
        Dim res As String

        res = MsgBox("Do you want to Add this record", vbYesNo, "SFA-Inventory Add")
        If res = vbYes Then
            Try
                Using sqlconn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Data\SFA\My Project\SFA Inventory\SFA Inventory\SFA Inventory.accdb")
                    Using sqlquery As New OleDb.OleDbCommand("INSERT INTO SFA Inventory(IMEINo,ProductName,Supplier,InvoiceNo,InvoiceDate,MobileNo,AppVersion)VALUES(?,?,?,?,?,?,?)", sqlconn)
                        sqlquery.Parameters.AddWithValue("@IMEI No", TextBox2.Text)
                        sqlquery.Parameters.AddWithValue("@Product Name", TextBox1.Text)
                        sqlquery.Parameters.AddWithValue("@Supplier", ComboBox1.Text)
                        sqlquery.Parameters.AddWithValue("@Invoice No", TextBox3.Text)
                        sqlquery.Parameters.AddWithValue("@Invoice Date", DateTimePicker1.Value.ToShortDateString())
                        sqlquery.Parameters.AddWithValue("@Mobile No", TextBox4.Text)
                        sqlquery.Parameters.AddWithValue("@App Version", TextBox5.Text)
                        sqlconn.Open()
                        sqlquery.ExecuteNonQuery()
                        MsgBox("Records inserted successfully", vbOKOnly, "Records inserted")
                        TextBox2.Text = ""
                        TextBox1.Text = ""
                        ComboBox1.Text = ""
                        TextBox3.Text = ""
                        DateTimePicker1.Value = DateTime.Now
                        TextBox4.Text = ""
                        TextBox5.Text = ""
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        Else
            Show()

        End If
    End Sub

Posted 10-Dec-18 17:44pm

Member 14084723

Updated 11-Dec-18 2:26am

Bryian Tan

v2

Add a Solution

Comments

Afzaal Ahmad Zeeshan 11-Dec-18 6:31am

Try executing this query inside the Access database itself, and see if it works fine there.

Bryian Tan 11-Dec-18 7:56am

The syntax should be "INSERT INTO [SFA Inventory] ..."

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2018-12-11T02:27:00

It is a bad idea to put spaces in table and column names. If you do that, you have to enclose the names in square brackets to make sure the name is considered with the spaces:

INSERT INTO [SFA Inventory] (IMEINo, ProductName, Supplier, InvoiceNo, InvoiceDate, MobileNo, AppVersion) VALUES (@IMEINo, @ProductName, @Supplier, @InvoiceNo, @InvoiceDate, @MobileNo, @AppVersion)

Also, remove all of the spaces in your named parameters:

sqlquery.Parameters.AddWithValue("@IMEINo", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@ProductName", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Supplier", ComboBox1.Text)
sqlquery.Parameters.AddWithValue("@InvoiceNo", TextBox3.Text)
sqlquery.Parameters.AddWithValue("@InvoiceDate", DateTimePicker1.Value.ToShortDateString())
sqlquery.Parameters.AddWithValue("@MobileNo", TextBox4.Text)
sqlquery.Parameters.AddWithValue("@AppVersion", TextBox5.Text)

You're also make another grievous mistake. NEVER directly use the values of textboxes in your queries. ALWAYS validate the data that's entered and use the validated data in the parameters.

Karthik_Mahalingam · Answer 2 · 2018-12-10T19:06:00

try

VB

Using sqlquery As New OleDb.OleDbCommand("INSERT INTO SFA Inventory(IMEINo,ProductName,Supplier,InvoiceNo,InvoiceDate,MobileNo,AppVersion)VALUES(@IMEINo,@ProductName,@Supplier,@InvoiceNo,@InvoiceDate,@MobileNo,@AppVersion)", sqlconn)
sqlquery.Parameters.AddWithValue("@IMEINo", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@ProductName", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Supplier", ComboBox1.Text)
sqlquery.Parameters.AddWithValue("@InvoiceNo", TextBox3.Text)
sqlquery.Parameters.AddWithValue("@InvoiceDate", DateTimePicker1.Value.ToShortDateString())
sqlquery.Parameters.AddWithValue("@MobileNo", TextBox4.Text)
sqlquery.Parameters.AddWithValue("@AppVersion", TextBox5.Text)

BASSIES · Answer 3 · 2018-12-10T20:17:00

Hello ,

Inventory(IMEINo,ProductName,Supplier,InvoiceNo,InvoiceDate,MobileNo,AppVersion)VALUES(?,?,?,?,?,?,?)",

with out space

and here with

sqlquery.Parameters.AddWithValue("@IMEI No", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@Product Name", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Supplier", ComboBox1.Text)
sqlquery.Parameters.AddWithValue("@Invoice No", TextBox3.Text)
sqlquery.Parameters.AddWithValue("@Invoice Date", DateTimePicker1.Value.ToShortDateString())
sqlquery.Parameters.AddWithValue("@Mobile No", TextBox4.Text)
sqlquery.Parameters.AddWithValue("@App Version", TextBox5.Text)

Change it to

sqlquery.Parameters.AddWithValue("@IMEINo", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@ProductName", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Supplier", ComboBox1.Text)
sqlquery.Parameters.AddWithValue("@InvoiceNo", TextBox3.Text)
sqlquery.Parameters.AddWithValue("@InvoiceDate", DateTimePicker1.Value.ToShortDateString())
sqlquery.Parameters.AddWithValue("@MobileNo", TextBox4.Text)
sqlquery.Parameters.AddWithValue("@App Version", TextBox5.Text)

Syntax error in insert statement

3 solutions

Solution 4

Solution 1

Solution 2

Add your solution here

Preview 0