Access "INSERT INTO" query syntax error

  • Thread starter Thread starter SiD`
  • Start date Start date
S

SiD`

Hi, I am trying to execute an INSERT INTO query, but the debugger says
me that there is a syntax error, which I cannot figure out:

Public Shared Function project_add(ByVal title As String, ByVal
desc As String, ByVal isActive As Boolean, ByVal isSystem As Boolean)
As Boolean
Dim conn As OleDbConnection = getConnection()
Dim cmd As New OleDbCommand
cmd = conn.CreateCommand

cmd.CommandType = CommandType.Text
cmd.CommandText = "INSERT INTO projects (title, desc,
isActive, isSystem) VALUES ('@title', '@desc', @isActive, @isSystem)"
cmd.Parameters.Add(New OleDbParameter("@title", title))
cmd.Parameters.Add(New OleDbParameter("@desc", desc))
cmd.Parameters.Add(New OleDbParameter("@isActive", isActive))
cmd.Parameters.Add(New OleDbParameter("@isSystem", isSystem))

conn.Open()
Dim ret As Integer = cmd.ExecuteNonQuery()
conn.Close()
Return (ret = 1)

End Function

Any idea of what can be the problem about this query?
Please note that the query is executed against an MDB.

Thanks,
Sid.
 
Sid,

Parameters in the Values clause should not be surrounded with single-quotes.

Kerry Moorman
 
OLE DB parameter collections mark the parameters with placeholders, not
named parameters. The placeholder depends on the data provider. SQL Server
and JET use "?".

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
OLE DB parameter collections mark the parameters with placeholders, not
named parameters. The placeholder depends on the data provider. SQL Server
and JET use "?".

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speakerwww.betav.comwww.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
Click to expand...

Thanks for the answers.
However,
(a) I tryied without quotes, with single-quotes and with double-quotes
and the problem persists.
(b) I understand William's reply, however, I always made queries with
named parameters (the names where ignored, only the order was used).
By the way, is the following piece of code correct? (it does not work:
same error as before).

cmd.CommandType = CommandType.Text
cmd.CommandText = "INSERT INTO projects (title, desc,
isActive, isSystem) VALUES ('?', '?', '?', '?')"
cmd.Parameters.Add(New OleDbParameter("?", title))
cmd.Parameters.Add(New OleDbParameter("?", desc))
cmd.Parameters.Add(New OleDbParameter("?", isActive))
cmd.Parameters.Add(New OleDbParameter("?", isSystem))

Thanks again.
 
Sid,

Also, Desc is an Access reserved word and needs to be enclosed in brackets:

[desc]

Kerry Moorman
 
I'm not familiar with adding parameters SQL statements. I only use them with
stored procedures. However you can make your statement work fairly easily
without parameters like this:

"INSERT INTO projects (title, desc,
isActive, isSystem) VALUES ('" & title & "', '" & desc & "'," & isActive &
"," & isSystem & ")"
 
Ah, no. This approach works until one of the strings contains a single quote
or you need to pass a date value. It also opens the application to SQL
injection attacks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
My Dear Bill,

This approach no more opens it to a SQL Injection Attacks than it already is.

As far as single quotes, are you at all familiar with the Replace function?

Replace(desc, "'", "''")

I'm not against best practices, but everything must be taken in context.
Right now Sid is just trying to get past a small stumbling block.
 
My dear Garth, I've been writing books (12 of them) on ADO and ADO.NET best
practices, RDO, ODBC, ODBCDirect, DAO and DBLib for over 15 years. My
readers, publishers, tech reviewers and critics tell me that I kinda know
what I'm talking about. Yes, any technique that passes strings into SQL puts
the application at risk for SQL injection--while it is not as prevalent with
an Access/JET database, it is still a bad practice to use as code often gets
updated to run against other platforms. And yes, I do not recommend the
Replace function which I documented when I wrote the Visual Basic version 2,
3, 4 and 5 data access documentation for Microsoft. It is a patch to a more
serious problem.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Thanks for the answers.
Obviously the problem was about the DESC keywork.

About the parameters, I would like to say that, indipendently about
the SQL Injection problem, I think that it is a good thing to let make
the language the most things: if it manage parameters, probably there
is a good reason (and also it leads to a more easy-to-read and elegant
code).
The developer should abstract the problems not only in the
architecture of the programs but in every aspect of coding.

Sorry for not explaining me very well... :)

Sid.
 
Bill,

I tried to simply explain what would work for this person. If he were just
tasked with a 'Proof of concept' situation with a very limited amount of time
to spend on it, then he wouldn't care about the format, just getting it done
quickly.

I post here when I have a problem, especially when I find myself banging my
head against the wall. It's great to have another pair of eyes to help out,
and occasionally I pick up a piece of information I wasn't previously aware
of. It seems that with every new version of a language, the list of things
that I'm not aware of gets longer.

I wasn't aware of yur aversion to the Replace function, and I don't remember
it being around in VB 2 & 3. I wouldn't mind hearing a brief version of why
we shouldn't use it if you have the time. I use it regularly, and even had my
own version of it at one time. It is very handy for string manipulation.

I want to mention that I am well aware of your background as an author. I
still have 2 or 3 fo your reference works on my shelf.

If I sounded sarcastic earlier, I sincerely apologize.

Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com

(e-mail address removed)
Office: (540) 437-0553
Cell: (540) 246-5566
 
Back
Top