Help with insert query using VB6

[qman]

I can't get the following insert statement to work using VB6. As you can see from the code, I am trying to insert dummy text data into the "Data_Cert_Results" table. Any ideas....?

My error upon calling Execute is:

Unable to execute -
(-2147217900: The name "aaaaaaa" is not permitted in this contect. Valid expressions are constants, contant, expressions, and (in some contects) variables. Column names are not permitted.


Dim mobjADOConn As adodb.Connection
Dim dbrs As adodb.Recordset

Dim sqlstrInsert As String
Dim td As String
Dim ca As String
Dim pc As String

Set mobjADOConn = New adodb.Connection
mobjADOConn.ConnectionString = "DSN=aaa;Uid=bbb;Pwd=ccc;"
mobjADOConn.Open

' Create a Recordsets by executing an SQL statement.
Set dbrs = mobjADOConn.Execute(sqlstr)

' Loop through the recordset and show the fields.
If dbrs.RecordCount >= 1 Then
dbrs.MoveFirst
End If
Do While Not dbrs.EOF
' Show recordset fields
td = "AAAA"
ca = "BBBB"
pc = "CCCC"
sqlstrInsert = "INSERT INTO " & "Date_Cert_Results (Type_of_Data, Check_Area, Product_Code) values (" & td & "," & ca + "," & pc & ")"
mobjADOConn.Execute (sqlstrInsert)

[GaryMazzone]

When inserting strings (text) into a database they need to be enclosed by single qoutes

sqlstrInsert = "INSERT INTO " & "Date_Cert_Results (Type_of_Data, Check_Area, Product_Code) values ('" & td & "','" & ca + "','" & pc & "')"

[qman]

Great, thanks for the info....!

[qman]

Still something appears to be wrong once I build on what you said. I am now getting unclosed quotation mark after the character string ".

I do not see it, do you?

sqlstrInsert = "INSERT INTO " & "Data_Cert_Results (Data_Cert_Results_Id, Rn_Descriptor, Rn_Create_Date, Rn_Create_User, Rn_Edit_Date, Rn_Edit_User) values ('" & resultid & "','" & rndesc & "','" & rncreatedate & "','" & rncreateuser & "','" & rneditdate & "','" & rnedituser & "')"

[qman]

I hate string.....!

[si_the_geek]

It's not just String values that need to be delimited in a certain way in SQL statements, similar applies to Date and other data types. For more info, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

What does sqlstrInsert contain when you get the error? (you can use Debug.Print sqlstrInsert to copy it to the Immediate window, for easy copy & paste here).

[qman]

Below is what sqlstrInsert contains..... My ID and User fields should display a hex value. From the output below, they display ' ?'. Could this be the problem?

INSERT INTO Data_Cert_Results (Data_Cert_Results_Id, Rn_Descriptor, Rn_Create_Date, Rn_Create_User, Rn_Edit_Date, Rn_Edit_User) values (' A','1 - Titles (ex. JR SR II MD).
Given & Middle Name/Initial comINSERT INTO Data_Cert_Results (Data_Cert_Results_Id, Rn_Descriptor, Rn_Create_Date, Rn_Create_User, Rn_Edit_Date, Rn_Edit_User) values (' A','1 - Titles (ex. JR SR II MD).
Given & Middle Name/Initial compounded in Given N','1/19/2009 1:15:12 PM',' ?','1/19/2009 1:15:12 PM',' ?')'
pounded in Given N','1/19/2009 1:15:12 PM',' ?','1/19/2009 1:15:12 PM',' ?')'

[si_the_geek]

You seem to have tested it twice, and left the cursor in the middle of the text - can you please try again.

The only thing that pops out at me is that it seem like you have ' after the final bracket, which is not what you should have (and is not in the code you posted before).

[qman]

Hi, here is what sqlstrInsert is set to within VB.

sqlstrInsert = "INSERT INTO " & "Data_Cert_Results (Data_Cert_Results_Id, Rn_Descriptor, Rn_Create_Date, Rn_Create_User, Rn_Edit_Date, Rn_Edit_User) values ('" & resultid & "','" & rndesc & "','" & rncreatedate & "','" & rncreateuser & "','" & rneditdate & "','" & rnedituser & "')'"

Below is what is shown with the debug.print:

INSERT INTO Data_Cert_Results (Data_Cert_Results_Id, Rn_Descriptor, Rn_Create_Date, Rn_Create_User, Rn_Edit_Date, Rn_Edit_User) values (' A','1 - Titles (ex. JR SR II MD).
Given & Middle Name/Initial compounded in Given N','1/19/2009 1:15:12 PM',' ?','1/19/2009 1:15:12 PM',' ?')'

[si_the_geek]

The problem is as I said:

... 12 PM',' ?')'

And it is due to this, which you added since you last posted the code:

... & rnedituser & "')'"

I don't know why you have ? for a couple of the fields, you should check the variables that those values are coming from.

[rothj0hn]

its hard to identify errors with those concatenation of strings. try to use other code..
let say

with dbrs
.open "Select * from TableName"
.addnew
!fieldname = Value
.close
end with

________
Mazda rotary pickup history

[rothj0hn]

i forgot to include the .update

[sansoa666]

private subcmd_click()
set rs = new adodb.recordset
rs.open"select * from tablename",cn,1,2
rs.addnew
!fieldname = textname.text
rs.update
set rs = nothing
end sub