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


"SiD`" wrote:

> 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.
>
>

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)
-----------------------------------------------------------------------------------------------------------------------

"SiD`" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

On Aug 11, 3:35 am, "William Vaughn" <billvaNoS...@betav.com> wrote:
> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "SiD`" <micheledico...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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.

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


"SiD`" wrote:

> 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.
>
>

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 & ")"
--
Tom Garth


"SiD`" wrote:

> 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.
>
>

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)
-----------------------------------------------------------------------------------------------------------------------

"Tom Garth" <(E-Mail Removed)> wrote in message
news:11AAF2CC-0CF2-413C-AD69-(E-Mail Removed)...
> 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 & ")"
> --
> Tom Garth
>
>
> "SiD`" wrote:
>
>> 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.
>>
>>

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.
--
Tom Garth


"William Vaughn" wrote:

> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Tom Garth" <(E-Mail Removed)> wrote in message
> news:11AAF2CC-0CF2-413C-AD69-(E-Mail Removed)...
> > 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 & ")"
> > --
> > Tom Garth
> >
> >
> > "SiD`" wrote:
> >
> >> 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.
> >>
> >>
>
>

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)
-----------------------------------------------------------------------------------------------------------------------

"Tom Garth" <(E-Mail Removed)> wrote in message
news:6DDD6B04-4BDA-4535-B976-(E-Mail Removed)...
> 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.
> --
> Tom Garth
>
>
> "William Vaughn" wrote:
>
>> 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)
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> "Tom Garth" <(E-Mail Removed)> wrote in message
>> news:11AAF2CC-0CF2-413C-AD69-(E-Mail Removed)...
>> > 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 & ")"
>> > --
>> > Tom Garth
>> >
>> >
>> > "SiD`" wrote:
>> >
>> >> 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.
>> >>
>> >>
>>
>>

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.