Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    215
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Using AutoNumber in SQL query...

    Hello all. I have the following Update statement:

    Code:
    [... 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:

    Code:
    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

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    response.write your SQL and run it directly in Access and see what it says?

  • #3
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    Try
    Code:
    ... 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)
    Last edited by ghell; 01-12-2008 at 08:17 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •