Criteria Expression Error - HELP!

[ShotoCon]

Hi There,

Have this bit of code here:

dbQuery1 = "SELECT key FROM tempWin"
   Set recordSet = dbConnection.execute(dbQuery1)

   Dim winKey

   winKey=Cint(recordSet("key"))

   dbQuery1 = "SELECT * FROM contestants WHERE key = '"&winKey&"'"
   Set recordSet = dbConnection.execute(dbQuery1)

which seems to be producing the following error in conjuection with the last recordSet execute statement (have included the previous statement so you know where im getting the data from):

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The 'key' field in the 'contestants' table is an AutoNumber and the 'key' field in the 'tempWin' table is a number. I know that when variables are created in ASP they are not assigned a variable type (Dim winKey), so when this error first started being thrown, i cast it as an integer as seen in code above.

Have even tried setting the last dbQuery1 to the following:

SELECT * FROM contestants WHERE key = '"&recordSet("key")&"'

This had the same error.

Help anyone, please?

Cheers

ShotoCon

 

[gmmastros]

I think key is a reserved word in Access. As such, I would suggest you change the name of that column (if you can). If you cannot change the name, then you should use [square brackets] around the column name, like this...

[tt][blue]dbQuery1 = "SELECT * FROM contestants WHERE [!][[/!]key[!]][/!] = '"&winKey&"'"[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ShotoCon]

Umm, can ignore this - figured it out.

Need to remove the enclosing ' ' from the winKey statement so the new code looks summit like this:

"SELECT * FROM contestants WHERE key = "&winKey&""

 

[jfdabiri]

actually you don't need the ending "" or "'" since you don't wrap those around numbers.
"SELECT * FROM contestants WHERE key = " & winKey
the above should be sufficient