Autonumber problem Error "Data type mismatch in criteria expression"

jvdzwaan · #1 (**permalink**) 11-22-02, 05:46

Hi All,

I am already busy for several hours on this problem WHO CAN HELP ME ;(
I got a Access database with with the field "Password", "Title", And the field PMID (PMID is a AUTONUMBER FIELD in ACCESS 1,2,3,4,5,6,7)

I got this script
SQL = "Select Title, Password From Checklist " _
& "Where Title = '"&Title&"' And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

THIS WORKS GREATTTTTTTTTTTTTTT!!!!!!!!!
But now here it comes.
I want to change TITLE to PMID in this script.

SQL = "Select PMID, Password From Checklist " _
& "Where PMID = '"&PMID&"' And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

Know i think is should remove the singel '. So i did that. I canged the script to
SQL = "Select PMID, Password From Checklist " _
& "Where PMID = #" & PMID & "# And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

But i keep getting errors like
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'PMID = ## And Password = 'test''.
/pm/login.asp, line 17

It seems that it doesn't reed the PMID...
WHO CAN HELP ME ;)?

Mulligan · #2 (**permalink**) 11-22-02, 07:23

Code:

SQL = "Select PMID, Password From Checklist " _
& "Where PMID = " & PMID & " And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

should do it.

The Autonumber field is a Long data type. IIRC, in Access the # symbols are used to encapsulate a DateTime field.

edit: I take it PMID is a variable with a number in it...? Otherwise, the SQL is going to fail as no value will be embedded in the where statement for PMID...

jvdzwaan · #3 (**permalink**) 11-22-02, 07:50

i get now the error
--------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'PMID = And Password = 'test''.
/pm/login.asp, line 17
--------------------------------------------------

When i look in Access and go with the DESIGNS VIEW
to the "PMID" field. It tells me this properties:
- Field Size : Long Integer
- New Values : Increment
- Format :
- Caption :
- Indexed : YES (No Duplicades)

When i look in the normal in the table it give just the normal Numbers automaticly generated. 1,2,3,4,5,6,7,8,9,10... until 86

Is there someting wrong with that?

Here is the whole script

----------------------------------------------------
<HTML>
<BODY>

<%
PMID = Request.Form("ID")
Password = Request.Form("passw")
'grab the form contents

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open ("problemman")

SQL = "Select PMID, Password From Checklist " _
& "Where PMID = " & PMID & " And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

If Not RS.EOF Then
Session("allow") = True
'if there is a match then show the page
%>

<html>
'HERE IS MY HTML PAGE
</html>

<%
Else
Response.Redirect "http://www.testl.com/main.htm"
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
End If
%>

</BODY>
</HTML>

rnealejr · #4 (**permalink**) 11-22-02, 12:24

The request.form("ID") is not returning anything - How is the value ID being stored in the form ?

Internet Services

Web Development

Digital Marketing

Consumer Tech