Problems with INSERT INTO and ADO

[Yomet]

Hi community,

I have the weirdest error happening in my VB6 program when I try to execute an "INSERT INTO" query. Here are the premises:

Public conn as ADODB.Connection
Public rs1 as ADODB.Recordset

- the database is Access 2003 this will be migrated to SQL server later.
- conn has been instantiated and opened correctly, I am able to select from several tables in the database, tblUsers among them

Here is the problem, I have a form used to create users and the following code is in the OK button on that form. BTW, I do NOT have all this in the real code, just one option at a time but I pasted them here so you can see what I have tried so far.

Private Sub cmdOK_Click()
   Dim UserID As Long
   Dim SQL As String
   Dim cmd As ADODB.Command
   
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = conn

'1st try - Execute method of the Connection object
'  Gives "Sytax error in INSERT INTO statement"
   conn.Execute "INSERT INTO tblUsers (UserName, Password) " & _
                "VALUES ('" & txtUserName & "', " & Chr(34) & txtPassword & Chr(34) & ")"

'2nd try - using a string variable instead of straight text
'  Gives "Sytax error in INSERT INTO statement"
   SQL = "INSERT INTO tblUsers (UserName, Password, Active, Comments) VALUES ('" & txtUserName & "', '" & txtPassword & "', -1, '');"
   conn.Execute SQL, , adExecuteNoRecords

'3rd try - using the command object
'  Gives "Sytax error in INSERT INTO statement"
   SQL = "INSERT INTO tblUsers (UserName, Password, Active, Comments) VALUES ('" & txtUserName & "', '" & txtPassword & "', -1, '');"
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = conn
   cmd.CommandText = SQL
   cmd.CommandType = adCmdText
   cmd.Execute
   
'4th try - using a query in the access database
'  Gives "Too few parameters, 2 expected"
   cmd.CommandText = "qryInsertUser"   'This query exists in the database
   cmd.CommandType = adCmdStoredProc
   cmd.CreateParameter "UserName", adVarChar, adParamInput, , txtUserName
   cmd.CreateParameter "Password", adVarChar, adParamInput, , txtPassword
   cmd.NamedParameters = True
   cmd.Execute
   
'5th try - a colleague inerted records using this circumvent way of running the query
'  Gives "Sytax error in INSERT INTO statement"
   SQL = "INSERT INTO tblUsers (UserName, Password) VALUES ('" & txtUserName & "', '" & txtPassword & "');"
   rs1.Open SQL, conn, adOpenForwardOnly, adLockReadOnly

'This works perfectly
   rs1.Open "SELECT * FROM tblUsers WHERE UserName = '" & txtUserName & "'", conn, adOpenDynamic
   UserID = rs1!UserID
   rs1.Close
   rs1.Open "SELECT * FROM tblGroups ORDER BY GroupName", conn, adOpenDynamic
   rs1.MoveFirst
   rs1.Find "GroupName = 'Users'"
   conn.Execute "INSERT INTO tblUserGroups (UserID, GroupID) VALUES (" & UserID & ", " & rs1!GroupID & ")"
End Sub

If anyone out there has a solution to the problem, or pointers to why this weird error comes up, and would not mind sharing I would really appreciate it. This error give me headaches... :icon_confused:

Thanks

Yomet

[Yomet]

One more thing, I tried the same INSERT statement but without the variables, i.e.

conn.execute "INSERT INTO tblUsers (UserName, Password) VALUES ('Hello', 'World');", conn

Just to make sure that the problem was not in the variables, and got the same Syntax Error.

Thx

Yomet

[Yomet]

Well,

I found one workaround using the parametrized query stored in the database, here it is

'6th try - using a query in the access database
   Dim arr(1)
   cmd.CommandText = "qryInsertUser"   'This query exists in the database
   cmd.CommandType = adCmdStoredProc
   arr(0) = "sdfasdr"
   arr(1) = "kihjui"
   cmd.Execute UserID, arr()

However, this is cumbersome and I would really try to find out where the Syntax error is coming from.

Thx

Yomet

[PVBert]

conn.execute "INSERT INTO tblUsers (UserName, Password) VALUES ('Hello', 'World');", conn

should be

conn.execute "INSERT INTO" & tblUsers & "('" & UserName & "','" & Password & "') VALUES ('Hello', 'World');", conn

remark 1: don't mix the variables with text strings!
remark 2: there are some single qoutes between the doubles!

[Yomet]

Hi PVBert,

Thanks for the reply, however, I have some clarifications and a question about it.

Clarifications: The table inserted into is called tblUsers and has the following structure:

tblUsers
===================
UserID       AutoNumber
UserName     String  (Not NULL, not empty string)
Password     String  (Not NULL, Empty string OK, Default "")
Active       Boolean (Not NULL, default True)
Comment      String  (Not NULL, Empty String OK, Default "")

Question: Are you saying that I need to delimit the actual field names in single quotes? i.e. that the SQL statement sent to ADO should be INSERT INTO tblUsers ('UserName', 'Password') Values ('Hello', 'World') I have never used single quotes for delimiting the actual names of objects in SQL but if you say that this will work I'll try it on Monday.

Thanks

Yomet

[PVBert]

Nope. Sorry, forget my previous post. I misread your example.
Not the field NAMES but the VALUES should be in single quotes, which you did. But...
After conn.execute try to read the err object...

on error resume next
conn.Execute sql,recaffected
if err<>0 then
  Debug.print err.description
else 
  Debug.print recaffected
end if
conn.close

[PVBert]

Don't know if this is something:
Line 7 (and 23):

SET cmd.ActiveConnection = conn

[Abhi1443]

Could be the problem because the field "Password" is a reserved field and you could try using another fieldname such as "uPassword". It will work.