Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Data type mismatch in criteria expression.

    Hi all. I'm getting error message can someone show me where i have gone wrong. I cant figure out what i'm doing wrong.

    IIS Error Message:
    Error Type:
    Microsoft JET Database Engine (0x80040E07)
    Data type mismatch in criteria expression.
    read.asp, line 43
    Here is my code:

    Code:
    <html>
    <head>
    <title></title>
    <style>
    <!--
    font         { font-family: Arial; font-size: 10pt; font-weight: bold }
    table        { color: #800000 }
    -->
    </style>
    </head>
    <body bgcolor="#FFFFFF" text="#000000">
    
    
    <!--#INCLUDE file="dropdown.asp"-->
    
    
    <hr color="#000000">
    
    &nbsp; 
    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsGuestbook 'Holds the recordset for the records in the database
    Dim strSQL 'Holds the SQL query for the database
    
    
    'Create an ADO connection odject
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    adoCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database.mdb")
    
    NameID= Clng(request.QueryString("NameID"))
    
    'Create an ADO recordset object
    Set rsGuestbook = Server.CreateObject("ADODB.Recordset")
    
    'Initialise the strSQL variable with an SQL statement to query the database
    
    strSQL = "SELECT * FROM Data WHERE NameID=" & NameID
    'strSQL = "SELECT * FROM Data"
    
    'Open the recordset with the SQL query 
    rsGuestbook.Open strSQL, adoCon
    
    'Loop through the recordset, create a loop counter and set to zero
    Dim loopCounter
    loopCounter = 0
    Do While not rsGuestbook.EOF
    %>
    
    
    <%
    If loopCounter < 1 Then
    %>
    
     <center><b>ID Number <% = rsGuestbook("NameID")  %>        
    <!--<table border="0" width="656" height="61" 0"" 486"" 106"" cellspacing="1" cellpadding="0">-->
     </b>
    <div style="width: 987; height: 410; overflow: auto">
    <table width="617" border="0"  height="73" cellspacing="1">
    <tr>
    <td width="18" height="25" align="center" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font size="2">Edit</font></td>
    <td width="123" height="25" align="left" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font face="Arial" size="2" color #ffffff""><nobr>Name ID</font></b></font></td>
    <td width="72" height="25" align="left" bgcolor="#66CCFF" 160"" 35""  #000000""><font color="" #FFFFFF""><b><font face="Arial"><nobr>Name</nobr></font></b></font></td>
    </tr>
    <%	
    End If 
    %>
    
        <tr>
    <td width="16" height="40" align="center" bgcolor="#66CCFF">Edit.</td>
    <td width="61" height="40" align="left" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b>
    <p align="center"><font size="2" face="Arial"><% = rsGuestbook("NameID") %></font></b></nobr></td>
    <td width="61" height="40" align="left" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b><font size="2" face="Arial"><% = rsGuestbook("NameID")  %></font></b></td>
    
    
    <%
    	'Increment the counter
    	loopCounter = loopCounter + 1
    
    	'Move to the next record in the recordset
    	rsGuestbook.MoveNext
    	'rsGuestbook.Close
    Loop
    If loopCounter > 0 Then	'Logic is if there were records, the counter would have incremented, if greater than zero, close the table
    	Response.Write ("</div>")
    	Response.Write ("</table>")
    	Response.Write("</center>")
    End IF
    
    'Reset server objects
    rsGuestbook.Close
    Set rsGuestbook = Nothing
    Set adoCon = Nothing
    %>
    
          </table>
    
            </div>
    
    </center>
    
    
    <% if NameID < 1 then %>
        
        <p><font size"2" face="Arial">Not Ready to Edit.</font>
        
    <% else %>
    
        <p align="center"><nobr><b><font size="2" face="Arial"><a href="quiz.asp?NameID=<% = NameID %>">More</a></font></b></nobr></p>
    
    <% end if %>
    Last edited by hughesmi; 12-14-2004 at 09:49 AM. Reason: spelling mistake
    Kind regards,
    Mike Hughes

  • #2
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    the error is in this line:
    strSQL = "SELECT * FROM Data WHERE NameID=" & NameID

    the field NameID is definately a number datatype isnt it (byte, long etc).. if its a string you need to enclose it in 's

    i noticed the sql you used under this to test it (the commented line under with no where clause) .. this worked right?

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks for your help and pointers.

    The database field "NameID" was set to "text". So i changed it to "Number" now I can see what to look out for when this happesn again.

    Mikey


    p.s yes my remmed sql line did work.
    Last edited by hughesmi; 12-14-2004 at 08:34 AM. Reason: spelling mistake
    Kind regards,
    Mike Hughes

  • #4
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi it's me again. I know my SQL is weak. Can you help me tidy up or give more advice on my sql statement?

    Here is my error message. I know it's my "Where" clause that causing the probs but i fail to see the logic.

    Error Type:
    Microsoft JET Database Engine (0x80004005)
    The specified field 'NameID' could refer to more than one table listed in the FROM clause of your SQL statement.
    TESTread.asp, line 44


    Code:
    <html>
    <head>
    <title></title>
    <style>
    <!--
    font         { font-family: Arial; font-size: 10pt; font-weight: bold }
    table        { color: #800000 }
    -->
    </style>
    </head>
    <body bgcolor="#FFFFFF" text="#000000">
    
    
    <!--#INCLUDE file="dropdown.asp"-->
    
    
    <hr color="#000000">
    
    &nbsp; 
    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsGuestbook 'Holds the recordset for the records in the database
    Dim strSQL 'Holds the SQL query for the database
    
    
    'Create an ADO connection odject
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    adoCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database.mdb")
    
    NameID= Clng(request.QueryString("NameID"))
    
    'Create an ADO recordset object
    Set rsGuestbook = Server.CreateObject("ADODB.Recordset")
    
    'Initialise the strSQL variable with an SQL statement to query the database
    
    'strSQL = "SELECT * FROM Data WHERE NameID=" & NameID
    'strSQL = "SELECT * FROM Data"
    strSQL ="SELECT [Name].[NameID], [Name].[Name], [Name].[Phone_Login], [Data].[Date_Late], [Data].[Late], [Data].[Early], [Data].[Reason], [Data].[Date_Entered] FROM Name INNER JOIN Data ON [Name].[NameID]=[Data].[NameID] WHERE NameID=" & NameID
    
    'Open the recordset with the SQL query 
    rsGuestbook.Open strSQL, adoCon
    
    'Loop through the recordset, create a loop counter and set to zero
    Dim loopCounter
    loopCounter = 0
    Do While not rsGuestbook.EOF
    %>
    
    
    <%
    If loopCounter < 1 Then
    %>
    
    &nbsp;
    
     <center>
    <div style="width: 987; height: 410; overflow: auto">
    <table width="617" border="0"  height="73" cellspacing="1">
    <tr>
    <td width="87" height="36" align="center" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font size="2">Edit</font></td>
    <td width="88" height="36" align="center" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font face="Arial" size="2" color #ffffff""><nobr>Agent
      Name</nobr></font></td>
    <td width="88" height="36" align="center" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font face="Arial" color #ffffff""><b><nobr>Phone
      Logon</nobr></b></font></td>
    <td width="88" height="36" align="center" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font face="Arial" color #ffffff""><b><nobr>Date
      Late</nobr></b></font></td>
    <td width="88" height="36" align="center" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font face="Arial" color #ffffff""><b><nobr>Early&nbsp;</nobr></b></font></td>
    <td width="88" height="36" align="center" bgcolor="#66CCFF" 160"" 35"" center"" #000000""><font face="Arial" color #ffffff""><b><nobr>Reason</nobr></b></font></td>
    <td width="88" height="36" align="center" bgcolor="#66CCFF" 160"" 35""  #000000""><font face="Arial" color #ffffff""><b><nobr>Date
      Entered</nobr></b></font></td>
    </tr>
    <%	
    End If 
    %>
    
        <tr>
    <td width="87" height="37" align="center" bgcolor="#66CCFF">Edit.</td>
    <td width="88" height="37" align="center" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b>
    <p align="center"><font size="2" face="Arial"><% = rsGuestbook("Name") %></font></b></nobr></td>
    <td width="88" height="37" align="center" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b>
    <font size="2" face="Arial"><% = rsGuestbook("Phone_Login") %></font></b></nobr></td>
    <td width="88" height="37" align="center" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b>
    <font size="2" face="Arial"><% = rsGuestbook("Date_Late") %></font></b></nobr></td>
    <td width="88" height="37" align="center" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b>
    <font size="2" face="Arial"><% = rsGuestbook("Early") %></font></b></nobr></td>
    <td width="88" height="37" align="center" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b>
    <font size="2" face="Arial"><% = rsGuestbook("Reason") %></font></b></nobr></td>
    <td width="88" height="37" align="center" bgcolor="#CCCCCC" 160"" 71"" center"" #66CCFF""><nobr><b><font size="2" face="Arial"><% = rsGuestbook("Date_Entered")  %></font></b></nobr></td>
    
    
    <%
    	'Increment the counter
    	loopCounter = loopCounter + 1
    
    	'Move to the next record in the recordset
    	rsGuestbook.MoveNext
    	'rsGuestbook.Close
    Loop
    If loopCounter > 0 Then	'Logic is if there were records, the counter would have incremented, if greater than zero, close the table
    	Response.Write ("</div>")
    	Response.Write ("</table>")
    	Response.Write("</center>")
    End IF
    
    'Reset server objects
    rsGuestbook.Close
    Set rsGuestbook = Nothing
    Set adoCon = Nothing
    %>
    
          </table>
    
            </div>
    
    </center>
    
    
    <% if NameID < 1 then %>
        
        <p><font size"2" face="Arial">Not Ready to Edit.</font>
        
    <% else %>
    
        <p align="center"><nobr><b><font size="2" face="Arial"><a href="quiz.asp?NameID=<% = NameID %>">More</a></font></b></nobr></p>
    
    <% end if %>
    Kind regards,
    Mike Hughes

  • #5
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Well your immediate problem is in:

    WHERE NameID=" & NameID

    Cos you're not telling it whether it's [Name].[NameID] or [Data].[NameID]

    But if I were you I'd consider renaming most of your database objects. Even just putting prefixes in front of names (like "tblName" and "fldNameID") can help avoid naming conflicts. And not having two fields with the same name in different tables. It's not always possible but it can help keep it clearer when you can.

  • #6
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    strSQL ="SELECT a.NameID " _
    & " , a.Name " _
    & " , a.Phone_Login " _
    & " , b.Date_Late ' _
    & " , b.Late " _
    & " , b.Early " _
    & " , b.Reason " _
    & " , b.Date_Entered " _
    & " FROM Name a " _
    & " , Data b " _
    & " WHERE a.NameID = b.NameID " _
    & " AND a.NameID=" & NameID

    Makes it easier to read and simpler

    fv


  •  

    Posting Permissions

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