INSERT INTO statement question

[jerseyboy]

I can't see the forest for the trees!

I am in VB trying to execute a [DoCmd.RunSQL strSQL] statement

Here is the code and the resulting msgbox:

_____________________________________

Private Sub cmdAddOne_Click()
Dim strSQL As String
Dim intContactID As Integer
Dim intEventID As Integer

intEventID = Me.lstAvailable.Column(0)
intContactID = Me.lstAvailable.Column(1)

strSQL = """" & "INSERT INTO tblEventAttendees ([EventID],[ContactNumber]) VALUES (" & """" & intEventID & """" & " , " & """" & intContactID & """" & &quot" & """;"

MsgBox strSQL

'DoCmd.RunSQL strSQL

End Sub

________________________________________________

msgbox Result:
"INSERT INTO tblEventAttendees ([EventID],[ContactNumber]) VALUES ("1" , "1&quot";

___________________________________________________________

What simple thing(s) am I missing? I am using Access Developers Handbook 1 (page 179) as a reference, but obviously I am missing something.

Thanks for the help


Thanks! JerseyBoy
Remember: self-praise is no recommendation

 

[mwolf00]

My guess is that you have put quotes around number values...

"INSERT INTO tblEventAttendees ([EventID],[ContactNumber]) VALUES (1 , 1)";

or you need single quotes so you don't screw up your string

"INSERT INTO tblEventAttendees ([EventID],[ContactNumber]) VALUES ('1' , '1')"; Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[PaulBricker]

I think mwolf00 is correct but I think you will have to try something like this.

strSQL = "INSERT INTO tblEventAttendees ([EventID],[ContactNumber]) VALUES (" & intEventID & " , " & intContactID & &quot"

I haven't used VB in a long time so I don't know if that makes a difference but if you were running this in Access the above would be correct for numeric datatypes.

Paul

 

[jerseyboy]

Thank you both, I did get it to work..

Here is the final statement for those who may follow this thread:

strSQL = "INSERT INTO tblTEMPEventAttendees ([EventID],[ContactNumber]) VALUES (" & "'" & strEventID & "'" & " , " & "'" & intContactID & "'" & &quot" & ";"

The SINGLE quotes made a difference as well as the final ";
" was incorrect.


JerseyBoy
Remember: self-praise is no recommendation