Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Using AutoNumber in SQL query...
01-08-2008, 07:00 PM #1
- Join Date
- May 2005
- Thanked 0 Times in 0 Posts
Using AutoNumber in SQL query...
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 & "'"
Data type mismatch in criteria expression.
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,
01-09-2008, 01:12 PM #2
- Join Date
- Jun 2002
- London, UK
- Thanked 110 Times in 109 Posts
response.write your SQL and run it directly in Access and see what it says?
01-12-2008, 08:07 PM #3
- Join Date
- Apr 2003
- Thanked 13 Times in 13 Posts
Try(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)Code:... WHERE Counter = " & CLng(vCounter)
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)
Last edited by ghell; 01-12-2008 at 08:17 PM.