Autonumber problem Error "Data type mismatch in criteria expression"

jvdzwaan · #1 (**permalink**) 11-22-02, 06:00

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

?

andrewst · #2 (**permalink**) 11-22-02, 08:49

This is not a SQL problem - it looks to me like your VBScript VARIABLE called PMID is empty. Try putting this debug message in your code instead of the Execute:

Response.Write( "PMID = " & PMID )

I'll bet when you run it you see this:

PMID =

So the problem is you need to assign a value to PMID.

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

Hi andrewst

You Are right

, It is emty then... How is this posible.

How so i fix that. THis field is a AUTONUMBER.
And there are numbers in it....

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 VIEW in the table PMID it give just the normal Numbers automaticly generated. 1,2,3,4,5,6,7,8,9,10... until 86

jvdzwaan · #4 (**permalink**) 11-22-02, 09:05

PS
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>

andrewst · #5 (**permalink**) 11-22-02, 09:12

Quote:

Originally posted by jvdzwaan
Hi andrewst

You Are right

, It is emty then... How is this posible.

How so i fix that. THis field is a AUTONUMBER.
And there are numbers in it....

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 VIEW in the table PMID it give just the normal Numbers automaticly generated. 1,2,3,4,5,6,7,8,9,10... until 86

Yes, but the problem has nothing to do with the COLUMN called PMID in the table, it is to do with the VBSCript VARIABLE that happens to have the same name. The variable is not AUTONUMBER, it is just a variable. If you don't assign a value to it, it will not get one for itself!

andrewst · #6 (**permalink**) 11-22-02, 09:14

Quote:

Originally posted by jvdzwaan
PS
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>

So the question is: why has the field called "ID" in the form not been populated? Is this field displayed? Did you type a value into it before hitting Submit?

jvdzwaan · #7 (**permalink**) 11-22-02, 09:30

yes... I got a form with 2 fields
one called ID
and one called passw

when i fill in the form I entered for the ID 61 (is in the DB)
in the passw field i entered test

Then i get the error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/PM/login.asp, line 14

andrewst · #8 (**permalink**) 11-22-02, 09:51

Quote:

Originally posted by jvdzwaan
yes... I got a form with 2 fields
one called ID
and one called passw

when i fill in the form I entered for the ID 61 (is in the DB)
in the passw field i entered test

Then i get the error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/PM/login.asp, line 14

So have you solved the variable problem yet? I mean, if you put:

Response.Write "PMID = " & PMID

in your program, does it now say:

a) PMID = 61

or:

b) PMID =

If the answer is still (b) then you are sure to get an error since the SQL you send to Access is:

Select PMID, Password From Checklist Where PMID = And Password = 'test'

which is invalid. In this case, you need to debug the variable assignment, not the SQL!

If it seems OK, then try writing out the whole SQL statement:

Response.Write SQL

and see if it a sensible SQL statement.

Internet Services

Web Development

Digital Marketing

Consumer Tech