Can't display the data through the sql statement

[scriptblur]

hi guys, can anyone help me????

i kept getting this error...


Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/sze_sean/html/staff_content.asp, line 162

What is wrong with my program?

<form name="form" method="post" action="staff_content.asp">
<table width="100%" border="1" bordercolor="#f1fdc8" bgcolor="#fdffdd">
<tr>
<td class="verdana"><font size=2><b>Search:</b></font></td>
<td height="24">
<div align="right" class="verdana" onclick="openwindow()"><u style="cursor:hand;">Help</u style>
</div>
</td>
</tr>
</table>
<table width="100%" border="1" bordercolor="#f1fdc8" bgcolor="#fdffdd">
<tr><td>
<select name=location>
<option value="" selected
>Zones
<option value="">--------------------
<option value="E">Zone E
<option value="F">Zone F
<option value="G">Zone G
<option value="H">Zone H
<option value="J">Zone J
<option value="K">Zone K
<option value="L">Zone L
<option value="N">Zone N
<option value="P">Zone P
<option value="Q">Zone Q
<option value="R">Zone R
<option value="S">Zone S
<option value="T">Zone T
<option value="U">Zone U
<option value="V">Zone V
<option value="W">Zone W
<option value="X">Zone X</option>
</select>
<select name=weeks>
<option value="" selected> Weeks Order
<option value="">-------------------------
<option value="1">Week 1
<option value="2">Week 2
<option value="3">Week 3
<option value="4">Week 4
<option value="5">Week 5
<option value="6">Week 6
<option value="7">Week 7
<option value="8">Week 8
</option></select>
<select name=days>
<option value="" selected> Days Order
<option value="">-------------------------
<option value="Monday">Monday
<option value="Tuesday">Tuesday
<option value="Wednesday">Wednesday
<option value="Thursday">Thursday
<option value="Friday">Friday
<option value="Saturday">Saturday
</option></select>

<select name=times>
<option value="" selected
> Time Slots
<option value="">-------------------------
<option value="0800-0850">Time: 0800-0850
<option value="0900-0950">Time: 0900-0950
<option value="1010-1100">Time: 1010-1100
<option value="1110-1200">Time: 1110-1200
<option value="1205-1255">Time: 1205-1255
<option value="1300-1350">Time: 1300-1350
<option value="1400-1450">Time: 1400-1450
<option value="1510-1600">Time: 1510-1600
<option value="1610-1700">Time: 1610-1700
<option value="1705-1755">Time: 1705-1755
<option value="1840-1930">Time: 1840-1930
<option value="1940-2030">Time: 1940-2030
<option value="2040-2130">Time: 2040-2130</option>
</select>
<input type="submit" name="button" value="Search"></td>
</tr>
</table>
</form>
<%
Dim conn,rs,sql
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("database.mdb"))

set rs=Server.CreateObject("ADODB.Recordset")

sql = "SELECT * FROM Zones WHERE Time_ID='"&Request.Form("times")&"' AND Day='"&Request.Form("days")&"' AND Week='"&Request.Form("weeks")&"' AND Character='"&Request.Form("location")&"' ORDER BY Week ASC, Character ASC"

rs.Open sql, conn

%>

<h3><font face="verdana">Your Search Result:</font></h3>
<table border="1" bordercolor="#f1fdc8" bgcolor="#fdffdd" width=100%>
<tr>
<td align="center"><font face="verdana" size=1><b>Week</b></font></td>
<td align="center"><font face="verdana" size=1><b>Day</b></font></td>
<td align="center"><font face="verdana" size=1><b>Zone</b></font></td>
<td align="center"><font face="verdana" size=1><b>Time</b></font></td>
<td align="center"><font face="verdana" size=1><b>Availability</b></font></td>

<%do while not rs.EOF%>
<tr><td align="center"><font face="verdana" size=1><%=rs("Week")%></font></td>
<td align="center"><font face="verdana" size=1><%=rs("Day")%></font></td>
<td align="center"><font face="verdana" size=1><%=rs("Character")%></font></td>
<td align="center"><font face="verdana" size=1><%=rs("Time_ID")%></font></td>
<td align="center"><font face="verdana" size=1><%=rs("Avail")%></font></td>
</tr>

<%rs.MoveNext%>
<%loop%>
</table>

</body>
</html>

[whammy]

Unfortunately the code you posted doesn't help... what's on

/sze_sean/html/staff_content.asp, line 162

?!?

If it's a Query, try

Response.Write(queryname) : Response.End

to write the query to the page instead of executing it. That's the easiest way to debug a SQL statement (along with using SQL Query Analyzer).

However, it's likely that you're trying to insert a string into an integer field or vice versa, as that's a common error.

[raf]

whammy,
I doen't quite understand your post (why doesn't code help ?)

scriptblur,
I might be completely wrong buth:
- I see you use a Time_ID variable in the sql statement. I presume this is a number (autonumber variable) in your database so the value for it in the sql-staement should also be a number.
So the quotes should be left out
"&Request.Form("times")&" and not '"&Request.Form("times")&"'
(also chech the datatypes of the other variables)
Note : the error probably leads to this line
rs.Open sql, conn
buth the real problem will be in the sql staement that got read here.
- I wouldn't spend so much time on coding. Check out the chr() function for instance. Using this function in a loop could really speed up things for what your doing

[whammy]

The code he posted doesn't help because there is no line 162. ;)

[raf]

whammy,

there's more to life then numbers :). The code starts with a formtag so i can immagen their are some preceding lines.
i'm not a bulletproof coder (so i also get a lot of errormessages) and if i see scriptblurs errormessage, i know that the error leeds to the lines i pointed out in my previous post. (and usually its a quotes problem in the sql statement)

buth more importantly :
scritpblur,

is your problem solved with this ?

[scriptblur]

Ok. Thank for both of your helps....

i had fixed the problem.... :thumbsup:

[whammy]

raf, basically you said the same thing I did anyway...


However, it's likely that you're trying to insert a string into an integer field or vice versa, as that's a common error.


:)