...
PDA

Data type mismatch in criteria expression.

hughesmi
12-13-2004, 01:32 PM
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:


<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 %>
ghell
12-13-2004, 03:50 PM
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?
hughesmi
12-14-2004, 07:22 AM
Thanks for your help and pointers. :thumbsup:

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 :thumbsup:


p.s yes my remmed sql line did work.
hughesmi
12-14-2004, 08:47 AM
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



<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 %>
Spudhead
12-14-2004, 10:29 AM
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.
fractalvibes
12-16-2004, 09:49 PM
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

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum