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

    Question 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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Autonumber problem Error "Data type mismatch in criteria expression"

    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.

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

  4. #4
    Join Date
    Nov 2002
    Posts
    10

    Re: Autonumber problem Error "Data type mismatch in criteria expression"

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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Autonumber problem Error "Data type mismatch in criteria expression"

    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?

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

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

Posting Permissions

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