VB.NET Access SQL syntax error

Question

I am trying to perform an INSERT query into an access database with VB.NET, and it keeps complaining that I have a syntax error. I can't seem to figure it out though. If I run it in Access itself, it works.

To give you a glimpse of what is happening, here is some debugging code :

Dim params(6) As OleDb.OleDbParameter
    params(0) = New OleDb.OleDbParameter("@matchNumber", Integer.Parse(recMatchNumber))
    params(1) = New OleDb.OleDbParameter("@teamNumber", Integer.Parse(recTeamNumber))
    params(2) = New OleDb.OleDbParameter("@action", recAction)
    params(3) = New OleDb.OleDbParameter("@object", recObject)
    params(4) = New OleDb.OleDbParameter("@level", recLevel)
    params(5) = New OleDb.OleDbParameter("@location", recLocation)
    params(6) = New OleDb.OleDbParameter("@time", recTime)

For Each param As OleDb.OleDbParameter In params
        Console.WriteLine(command.CommandText)
        Console.WriteLine(param.ParameterName)
        Console.WriteLine(param.Value)
        command.Parameters.Add(param)
Next

And here is the result :

INSERT INTO matchData( matchNumber, teamNumber, action, object, [level], location, [time]) VALUES(@matchNumber,@teamNumber,@action,@object,@level,@location,@time)
@matchNumber
1
INSERT INTO matchData( matchNumber, teamNumber, action, object, [level], location, [time]) VALUES(@matchNumber,@teamNumber,@action,@object,@level,@location,@time)
@teamNumber
50
INSERT INTO matchData( matchNumber, teamNumber, action, object, [level], location, [time]) VALUES(@matchNumber,@teamNumber,@action,@object,@level,@location,@time)
@action
Move
INSERT INTO matchData( matchNumber, teamNumber, action, object, [level], location, [time]) VALUES(@matchNumber,@teamNumber,@action,@object,@level,@location,@time)
@object
Robot
INSERT INTO matchData( matchNumber, teamNumber, action, object, [level], location, [time]) VALUES(@matchNumber,@teamNumber,@action,@object,@level,@location,@time)
@level
0
INSERT INTO matchData( matchNumber, teamNumber, action, object, [level], location, [time]) VALUES(@matchNumber,@teamNumber,@action,@object,@level,@location,@time)
@location
Zone
INSERT INTO matchData( matchNumber, teamNumber, action, object, [level], location, [time]) VALUES(@matchNumber,@teamNumber,@action,@object,@level,@location,@time)
@time
Auto

The error is Syntax error in INSERT INTO statement.

Thanks.

I bet object is a keyword. Put it in brackets. If it's not too late, I would change that field name to something more descriptive. — LarsTech
– LarsTech, Commented Mar 3, 2015 at 22:49
Great, that worked. Thanks! If you would like to add an answer, I'd be glad to accept it. @HansUp — Jay S.
– Jay S., Commented Mar 3, 2015 at 23:09

LarsTech · Accepted Answer · 2015-03-03 23:25:59Z

2

As was commented, object and action are reserved words, so you would have to place brackets around those names: [object], [action], etc.

See Access 2007 reserved words and symbols for the entire list of reserved words.

Some common reserved words I often see causing problems:

action
date
object
password
user

It's usually better to avoid using those words as the names of your database fields.

answered Mar 3, 2015 at 23:25

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.NET Access SQL syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related