Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    10

    Unhappy Unanswered: Autonumber problem Error "Data type mismatch in criteria expression"

    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 ;)?
    just joost :-)

  2. #2
    Join Date
    Jul 2002
    Posts
    55
    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...

  3. #3
    Join Date
    Nov 2002
    Posts
    10
    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>
    just joost :-)

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    The request.form("ID") is not returning anything - How is the value ID being stored in the form ?

Posting Permissions

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