• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

data type mismatch with an Access autonumber field

I'm trying to pull a record from an Access2K database to edit it.  The field "ID" is the autonumbered key.  Here's the code...

<% ID = Request.querystring("ID")
   ID = CLng(ID)

Set DataConn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

DataConn.Open "DBQ=" & Server.Mappath("disclosure.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

sql = ("SELECT * FROM track WHERE [ID] = '" & ID & "'")
rs.Open sql, DataConn, 1, 3 %>

A VarType command confirms that "ID" is a long integer whether from the querystring and when it comes in from the database.  

TIA,

Ken
0
KenAdney
Asked:
KenAdney
  • 3
  • 2
  • 2
  • +1
1 Solution
 
ejrhodesCommented:
The only thing I can think of is the quotes around ID are messing you up. I could be mistaken, but I believe if you remove the quotes, it will recognize ID and your query will work.
0
 
mberumenCommented:
I agree with Ejrhodes.    The apostrophe ' around the ID variable makes it become a string instead of a number

Try this instead

sql = ("SELECT * FROM track WHERE [ID] =" & ID & ")"
0
 
mberumenCommented:
I mean try this

sql = "SELECT * FROM track WHERE [ID] =" & ID
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
John844Commented:
get rid of the parenthesis as well.  I have also seen some weird things happen with spacing in the where statements.  Don't add any spaces before or after the = sign.

change
sql = ("SELECT * FROM track WHERE [ID] = '" & ID & "'")
to
sql = "SELECT * FROM track WHERE [ID]=" & ID
0
 
John844Commented:
If this does not fix your problem, post us the exact error message.
0
 
ejrhodesCommented:
John I believe the ' are the big problem he is having and should fix it.  That being said,  I did not know about problems with ()  Thanks for the heads up
0
 
John844Commented:
no problem.  Just here to learn and try to help where I can
0
 
KenAdneyAuthor Commented:
Yep, that was ticket.  Thanks!

I guess I was reconverting the querystring back into a string, eh?  I have a hard time getting the syntax on those SQL statements right...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now