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