Execute query - too few parameters

[j_gold]

Hi,

Getting error 3061 - "Too few parameters. Expected 10".

The query should roll back on error, and appears too, but when I check my
database, the query has been executed. There are 11 fields in the table, but
the first one is an auto_generated number, so I wasn't including that in the
query.

Would this have anything to do with the fact that I am using linked tables
(linking to a MySQL database)? If so, could someone point me in the right
direction as to how to set up my queries and rollback transactions?

Thanks,

J Gold

Private Sub addPublication(pubTitle As String, publisher As String, urlLINK
As String, _
pubType As String, pubStatus As String, pubAuthor As
String, _
pubPages As String, pubVolume As String, publishDate
As Date, _
pubPresentedAt As String)

Dim wrk As DAO.Workspace: Set wrk = DBEngine(0)
Dim dbP As DAO.Database: Set dbP = wrk(0)
Dim dbPB As DAO.Database: Set dbPB = wrk(0)
Dim rs As DAO.Recordset

Debug.Assert Not (wrk is Nothing)
wrk.BeginTrans
On Error GoTo trans_Err

dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
& "pubType, pubStatus, pubAuthor, pubPages, pubVolume,
publishDate, pubPresentedAt) " _
& "VALUES (pubTitle, publisher, urlLINK, pubType, pubStatus,
pubAuthor, " _
& "pubPages, pubVolume, publishDate, pubPresentedAt);",
dbFailOnError

'Commit the transaction
wrk.CommitTrans dbForceOSFlush

trans_Exit:
'Clean up
wrk.Close
Set dbP = Nothing

Exit Sub

trans_Err:
'Roll back the transaction
wrk.Rollback
Resume trans_Exit

End Sub

 

[Douglas J. Steele]

The references to the variables need to be outside of quotes.

dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
& "pubType, pubStatus, pubAuthor, pubPages, pubVolume, "_
& "publishDate, pubPresentedAt) " _
& "VALUES (" & pubTitle & ", " & publisher & ", " & urlLINK & ", " _
& pubType & ", " & pubStatus & ", " & pubAuthor & ", " _
& pubPages & ", " & pubVolume & ", " & publishDate & ", " &
pubPresentedAt);", _
dbFailOnError

That assumes that everything is numeric. From the names, I assume pubTitle
is text, and publishDate is date. You need to use quote delimiters for text
values and # delimiters for dates:

dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
& "pubType, pubStatus, pubAuthor, pubPages, pubVolume, "_
& "publishDate, pubPresentedAt) " _
& "VALUES ('" & Replace(pubTitle, "'", "''") & "', " _
& publisher & ", " & urlLINK & ", " _
& pubType & ", " & pubStatus & ", " & pubAuthor & ", " _
& pubPages & ", " & pubVolume & ", " _
& Format(publishDate, "\#yyyy\-mm\-dd\#") & ", " & pubPresentedAt);",
_
dbFailOnError

Exagerated for clarity, that VALUES line is

& "VALUES ( ' " & Replace(pubTitle, " ' ", " ' ' ") & " ' , " _

The reason for the Replace function there is to handle the case where the
title has apostrophes in it.

 

[j_gold]

Many thanks Douglas. I'm going to blame my stupidity on lack of sleep.

J Gold