Very weird SQL Syntax errors!

[CybrDemon]

Hi guys,

 

I've been working with databases under the old ASP and Java (I'm familiar with SQL), and I've just started to play with VB .net not long ago.

 

I'm getting syntax errors where i shouldn't get any! This is the code:

 

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FFFF_users.mdb;"

conn = New OleDbConnection(connString)
conn.Open()
command = New OleDbCommand("INSERT INTO users VALUES ('" & realName & "', '" & username & "', '" & password & "')", conn)

success = command.ExecuteNonQuery() = 1

 

The above works for an insertion, but if i change the INSERT statement to:

 

"INSERT INTO users (realName, username, password) VALUES ('" & realName & "', '" & username & "', '" & password & "')"

 

which specifies the columns explicitly, then i get a nice little exception:

 

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

at FabulousFunForFree.userFunctions.registerSuccess(String realName, String username, String password)

 

I've tried just typing that INSERT command into an Access query, and it works. So i'm wondering if this is some issue with OleDb? I get a syntax error with UPDATE as well:

 

command = New OleDbCommand("UPDATE users SET password = '" & newPassword & "' WHERE username = '" & username & "'", conn)

 

this is truly weird :confused:

 

[edit]Please use

 tags [/ vb]...It is impossible to see Silver on a Grey background [/edit]

Edited April 27, 2003 by Robby

[CybrDemon]

muahahaha....someone enlightened me...

 

somehow "password" is a reserved word...so you can't use it as a column name. Same with "date" i think.

 

:D

[Vitaly]

I think, SQL statement should look like this:

INSERT INTO users (realName, username, [password]) VALUES ('" & realName & "', '" & username & "', '" & password & "')"