...

View Full Version : Using AutoNumber in SQL query...



parallon
01-08-2008, 06:00 PM
Hello all. I have the following Update statement:


[... all my variables getting populated]
vcounter = Request.Form("MM_recordId") (this is the one I am having trouble with)

sQry2 = "UPDATE tblEmpTime SET dbCaseNo = '" & vCaseNo & "', dbOrg = '" & vOrg & "', dbContract = '" & vContract & "', dbSupervisor = '" & vSupervisor & "', dbAdmin = '" & vAdmin & "', dbTestSU = '" & vTestSU & "', dbTestOPS = '" & vTestOPS & "', dbTestCU = '" & vTestCU & "', dbSiteMaint = '" & vSiteMaint & "', dbFabrication = '" & vFabrication & "', dbEquipment = '" & vEquipment & "', dbTraining = '" & vTraining & "', dbSupervision = '" & vSupervision & "', dbHoliday = '" & vHoliday & "', dbSickPTO = '" & vSickPTO & "', dbVacation = '" & vVacation & "', dbOvertime = '" & vOvertime & "', JobDesc = '" & vJobDesc & "', Hours = '" & vHours & "', JobTask = '" & vJobTask & "', Location = '" & vLocation & "' WHERE Counter = '" & vCounter & "'"

The problem seems to be that the last variable 'vCounter' is an AutoNumber in Access. I keep getting the following error when running the script:


Data type mismatch in criteria expression.

Dreamweaver had originally created the code in the confusing way that it does, but I am slowly going through and recoding all of this stuff by hand since it is A LOT easier to understand. DW originally assigned the value with the by Request.Form("MM_recordId") but when trying to assign the value of Request.Form("MM_recordId") to vCounter, I get an error.

I've even changed the code to read "... WHERE Counter = " & vCounter which works sometimes for numbers, but to no prevail. Does anyone out there have any suggestions?

Thanks in advance,

Parallon

Spudhead
01-09-2008, 12:12 PM
response.write your SQL and run it directly in Access and see what it says?

ghell
01-12-2008, 07:07 PM
Try
... WHERE Counter = " & CLng(vCounter)(without the apostrophes) The CLng is there just to stop SQL injection (always check data before inserting it directly into a query) The CLng also insures that a number is being used, which could cause the problem (if it is a numeric type and you try to use something like a letter or punctuation, this may be causing the error)

Edit: oops, sorry. Just read that you have tried using no apostrophes for Counter and it has not worked. Make sure you do it for all the numeric types though and any dates are properly formatted etc. Are you sure that the problem lies in the counter? It's good practice to only use apostrophes for database types that require them.

Also, print out vCounter to make sure that it is actually getting assigned. You may wish to use Option Explicit at the top of your code and Dim all variables to ensure that you are not just making a typo in a variable name or anything like that.

IMO DW is a POS (mmm acronyms)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum