Getting a SYNTAX ERROR in updating access in VB.NET

Question

1.00/5 (1 vote)

See more:

Hello,
I have been tryng to do an Insert into an Access database but I am getting the error:

SYNTAX ERROR in INSERT INTO statement.

I have tried evreything that I can think of to overcome this error, to no avail. I have run the Insert statements that I have tried in Access itself and everything works just fine. I also have an INSERT/SELECT statement that also fails with

SYNTAX ERROR in From clause.

but runs fine in Access. The table has an AutoNumber field. I ran this against an Access 2007 database, then recreated the table in a new database in Access 2013, neither worked.

What I have tried:

strSql = "INSERT INTO TRANSACTION ([Location], [CoilNo], [HeatNo], [Matsize], [Net], [Gross], [TransDate], [BOL], [PackSlip], [TransType], [Flag], [NewLocation], [CreateDate], [ShipName], [Cost]) VALUES (
@Location, @CoilNo, @HeatNo, @Matsize, @Net, @Gross,
@TransDate, @BOL, @PackSlip, @TransType, @Flag, @NewLocation, @CreateDate, @ShipName, @Cost)"

Try
Using conn As New OleDbConnection(ConnStr)
conn.Open()
Using cmd As New OleDbCommand(strSql, conn)
With cmd
.Parameters.Clear()
.Parameters.AddWithValue("@Location", txtLocation.Text)
.Parameters.AddWithValue("@CoilNo", txtCoilNumber.Text)
.Parameters.AddWithValue("@HeatNo", txtHeatNumber.Text)
........
OR
'cmd.Parameters.Add(New OleDbParameter("@Location", Trim(txtLocation.Text)))
'cmd.Parameters.Add(New OleDbParameter("@CoilNo", Trim(txtCoilNumber.Text)))
'cmd.Parameters.Add(New OleDbParameter("@HeatNo", Trim(txtHeatNumber.Text)))
........
OR
'.Parameters.Add(New OleDb.OleDbParameter("@Location", OleDb.OleDbType.VarChar))
'.Parameters("@Location").Value = txtLocation.Text
'.Parameters.Add(New OleDb.OleDbParameter("@CoilNo", OleDb.OleDbType.VarChar))
'.Parameters("@CoilNo").Value = txtCoilNumber.Text
'.Parameters.Add(New OleDb.OleDbParameter("@HeatNo", OleDb.OleDbType.VarChar))
'.Parameters("@HeatNo").Value = txtHeatNumber.Text
......
End With

Dim recordsAffected As Integer = cmd.ExecuteNonQuery()

End Using
End Using
Catch ex As Exception
MessageBox.Show(ex.Message, "SaveConsignedData", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

Posted 26-May-16 10:31am

marla713

Updated 26-May-16 10:47am

Add a Solution

1 solution

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 · 2016-05-26T10:47:00

Not enough information or code.

What I can tell you is that Access doesn't used named parameters. You can still assign names to them, but the parameters you add have to appear in the order that they appear in the SQL statement.

Next, every parameter you have is being treated as a string if you're assigning directly from a TextBox. This is bad form as you don't appear to be doing any validation of the data.

Now, if certain columns in your database are numeric, such as CoilNo, you have to convert the text in the TextBox to an integer before you add it as a parameter. You cannot insert a string into a column designed as a number.