Very weird SQL Syntax errors!

C

CybrDemon

New member
Joined
Apr 26, 2003
Messages
2
Location
Brisbane, Australia
Hi guys,

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

Im getting syntax errors where i shouldnt get any! This is the code:

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:

Code: 
"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)
Click to expand...

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

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

this is truly weird :confused:

[edit]Please use
Code: 
 tags [/ vb]...It is impossible to see Silver on a Grey background [/edit]
 
Last edited by a moderator:
I think, SQL statement should look like this:
INSERT INTO users (realName, username, [password]) VALUES (" & realName & ", " & username & ", " & password & ")"
 
Back
Top