asked on

Filter data ifrom excel fto a web page

I have an ASP page that when I filter by Ctegory, it works fine but when I try to filter by year i get an error saying

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/energysysvc/4key/4KeyrecEdit.asp, line 51

and when i response.wrote the sql it is

SELECT * FROM tbl4key WHERE Year = '2009' ORDER BY 'Year';

the asp pages code is attached below

<%@ Language=VBScript %>
<!--#include file="Tools/userinfo.inc"-->
<!--#include file="../includes/adovbs.inc"-->


<%


dim Rs
Dim strSQL

		
		'Response.Write "servername = " & servername
	
	UID = Request.ServerVariables("LOGON_USER")
	Uname = ucase(right(UID,7))	
	response.write Uname
	vXlsFile = "\virtual\documents\energysysvc\EHS List.xls"
		

		vXlsFile = Server.Mappath(vXlsFile)

		ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 8.0;"& chr(34)

		'Open Database Connection
		
		Set Conn = Server.CreateObject("ADODB.Connection") 
                Conn.Open ExcelConnString 
		
		
set Rs=server.createobject("ADODB.recordset")	
	
strSQL = "SELECT * FROM tbl4key "
If Request.Form("Category") <> "" OR Request.Form("Year") <> "" Then
     strSQL = strSQL & "WHERE"
End If
If Request.Form("Category") <> "" Then
      strSQL = strSQL & " Category = '" & Request.Form("Category") & "'"
      If Request.Form("Year") <> "" Then
            strSQL = strSQL & " and"
      End If
End If
If Request.Form("Year") <> "" Then
      strSQL = strSQL & "  Year = '" & Request.Form("Year") & "'"
      
End If
strSQL = strSQL & " ORDER BY 'Year';"
response.write strSQL
Rs.open strSQL,Conn,1,3
%>



<html>

<head>

<title></title>
</head>
<body onload="">





<table width="100%" cellspacing="0" cellpadding="0">



<tr>
        <td width="81%" colspan="2"><div align="left"><table name="ContentTable" id="ContentTable" border="0" cellpadding="0" cellspacing="0" width="100%">
         

<form name="myForm" method="post" action="../4key/4KeyrecEdit.asp">
<table border=0>
<tr><td align="right"><b>Category:</b></td><td>
					<SELECT id=category style="WIDTH: 173px" name=category size="1"> 
 					<OPTION value=""selected></OPTION>
 					<OPTION value="Injury">Injury</OPTION>
 					<OPTION value="LOPC">LOPC</OPTION>
 					<OPTION value="Process Safety Incident">Process Safety Incident</OPTION>
 					<OPTION value="Near Miss(injury/LOPC/PSC)">Near Miss(injury/LOPC/PSC)</OPTION>
 					</SELECT>
	(<font size="1">To select all categories scroll to the bottom and choose the 
	blank option.</font>)</tr></td>


<tr><td align="right"><b>Year:</b></td><td>
						<select name="Year" id="Year">
      		            <option></option>
                        <option value="2007">2007</option>
                        <option value="2008">2008</option>
                        <option value="2009">2009</option>
                        <option value="2010">2010</option>
                        <option value="2011">2011</option>
      
</select>
	<tr><td>
<input type="submit" name="submit" value="Filter">
</form>
</tr></td>

<%

if Rs.eof <> true then
response.write "<table border=1>" & _
"<tr><td><strong>ID</strong></td><td>" & _
"<strong>Year</strong></td><td><strong>Business</strong>" & _
"</td><td><strong>Category</strong></td></tr>"


 while not Rs.eof

'response.write "<tr><td>" & Rs.fields("ID") & "&nbsp;</td><td>" & Rs.fields("Year") & _
response.write "<tr><td><a href=""4KeyrecEdit1.asp?ID=" & Rs.fields("ID") & """>"&Rs("ID")&"</a></td><td>" & Rs.fields("Year") & _
"&nbsp;</td><td>" & Rs.fields("Business") & "&nbsp;</td><td>" & Rs.fields("Category") & _
"</td></tr>"

Rs.movenext
wend
response.write "</table>"
end if
'mth = Request.Form("formMonth")
'Ste = Request.Form("formSite")
'Yr = Request.Form("formYear")
'response.write strSQL
%>
</td></tr>

</table>
<%

'rs.close
'set rs=nothing



%>
</body>
</html>

Open in new window

ASKER CERTIFIED SOLUTION

Paul MacDonald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jlcannon

ASKER

Ok so when I edited the strSQL to remove the ' ' around Year it initially loads sorted correctly then when I try to filter by year it gives me the same error then I tried the [] and it is reflected in the response.write of the sql statement but it still gives the same error. let me change the name to FDate and see if it impacts it.

Big Monty

what kind of database? what is the data type of this field?

jlcannon

ASKER

I am connecting to and excel sheet

jlcannon

ASKER

hmm still getting the error. the column is numbers so it should work. when I open the page before selecting any filters it dispalys all the data just fine. and if I filter by category it functions just fine but when I try to filter by year it gives me the error.

jlcannon

ASKER

It hit me like a train out of the blue. I was using tick marks around a numeric value. when I removed the ' around the year it works!.

jlcannon

ASKER

Just wanted to thank you guys for helping me thinkn through the process. I did find the issue which was I was using '' marks around a numeric value.