CODEWITHSUNDEEP
vb.netms-access
Jay S.
Jay S.

Reputation: 1327

VB.NET Access SQL syntax error

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.

Upvotes: 2

Views: 120

Answers (1)

LarsTech
LarsTech

Reputation: 81610

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.

Upvotes: 2

Related Questions