ADO Syntax error when trying to insert a new record

[Simon]

Hi

I'm basically try to reused code from "Programming MS Access version2002"

I get an EM telling there is a syntax error when sub below is called it
debigs to the .execute line, the Variables are correctly containing the data
I'm expecting - Can anyone help me understand where I've gone wrong?

Many thanks

Snippet of code:

Sub UpdateSplit(strClassType As String, dblPassClassID As Double,
strPassClassNo As String, strPassClassTitle As String, strJudge As String,
strShowSection As String, strSectionID As String, strClassTypeID As String,
strClassStatusID As String, strClassRef As String, intOpenID As Integer,
strStatus As String, strSponsoredBy As String, strNameOf As String)
Dim cmdUpdate As ADODB.Command
Set cmdUpdate = New Command
Select Case strClassType
Case "Open"
With cmdUpdate
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO tblClassInfo(dblPassClassID,
strPassClassNo, strPassClassTitle, strJudge, strShowSection, strSectionID,
strClassTypeID, strClassStatus, strClassRef, intOpenID, strStatusID,
strSponsoredBy, strNameof)) VALUES (" & dblPassClassID & "," & strPassClassNo
& "," & strPassClassTitle & "," & strJudge & "," & strShowSection & "," &
strSectionID & "," & strClassTypeID & "," & strClassStatusID & "," &
strClassRef & "," & intOpenID & "," & strStatus & "," & strSponsoredBy & ","
& strNameOf & ")"
.CommandType = adCmdText
.Execute
End With

 

[Daniel Pineault]

I don't know about ado, but your SQL seems off.

"INSERT INTO tblClassInfo(dblPassClassID,
strPassClassNo, strPassClassTitle, strJudge, strShowSection, strSectionID,
strClassTypeID, strClassStatus, strClassRef, intOpenID, strStatusID,
strSponsoredBy, strNameof)) VALUES (" & dblPassClassID & "," & strPassClassNo
& "," & strPassClassTitle & "," & strJudge & "," & strShowSection & "," &
strSectionID & "," & strClassTypeID & "," & strClassStatusID & "," &
strClassRef & "," & intOpenID & "," & strStatus & "," & strSponsoredBy & ","
& strNameOf & ")"

1. String values need to be surrounded by ''
2. your section "(dblPassClassID,
strPassClassNo, strPassClassTitle, strJudge, strShowSection, strSectionID,
strClassTypeID, strClassStatus, strClassRef, intOpenID, strStatusID,
strSponsoredBy, strNameof))" has too many closing brackets

The best thing is to add a break at the '.CommandType = adCmdText' line, run
your code and debug your SQL string. Try it in the Query Editor until you
get it right. Once you know your SQL is good, then you can continue working
on your code. But I think the issue is your SQL.

Also, when making a post. It is very helpful it your give us the exact
error number and even the error exact description that is raise.

Cleanup your SQL and try again.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Simon]

Thanks Daniel,

I've tried to simplify for testing reduce reducing the number of variables
and still get the following EM
MSVB EM: runtime -2147217900(80640e14),
Syntax error INSERT INFO statement

Incidentally the book advises that "... this syntax is not available from
the SQL view of the Access query designer."

Code now looks like this:

Sub UpdateSplit(strClassType As String, dblPassClassID As Double,
strPassClassNo As String, strPassClassTitle As String)
Dim cmdUpdate As ADODB.Command
Set cmdUpdate = New Command
Select Case strClassType
Case "Open"
With cmdUpdate
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO tblClassInfo(dblPassClassID,
strPassClassNo, strPassClassTitle)) VALUES ('" & dblPassClassID & "','" &
strPassClassNo & "','" & strPassClassTitle & "')"
.CommandType = adCmdText
.Execute
End With

 

[Douglas J. Steele]

You've got two closing parentheses after your list of fields. And if you're
using standard naming conventions, dblPassClassID is likely a numeric field,
and hence needs no quotes around the value:

.CommandText = "INSERT INTO tblClassInfo(dblPassClassID,
strPassClassNo, strPassClassTitle) VALUES (" & dblPassClassID & ",'" &
strPassClassNo & "','" & strPassClassTitle & "')"

Incidentally, what book? That quote doesn't really make sense to me: you can
definitely get the syntax for an INSERT INTO query from the Access query
designer (although it's possible they're referring to the concatenation of
variables in there)

 

[Simon]

Hi Douglas,

It is a Microsoft Press Publication (ISBN 0-7356-1405-9): Programming
MICROSOFT ACCESS version 2002 by Rick Dobson Page 154 last paragraph re: the
SQL quote from my last post.

Many thanks for your correction I will give it a try.

 

[Simon]

Doh!

I have just realised how stupid I have been instead of using the Field Names
after the INSERT INTO( xxxx, xxxx, xxxx ) I used the variable names - that's
why it it did not work - very red faced!!!

Thank you Douglas and Daniel for your very kind offers of help,

Simon