• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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

0
jlcannon
Asked:
jlcannon
  • 5
  • 2
2 Solutions
 
Paul MacDonaldDirector, Information SystemsCommented:
This may be because "year" is a keyword and shouldn't be used as a column name, but you can also try:
     ...
     strSQL = strSQL & " ORDER BY Year;"
     ...
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you want to use Year as a column name, just put brackets around it:

If Request.Form("Year") <> "" Then
      strSQL = strSQL & "  [Year] = '" & Request.Form("Year") & "'"
0
 
jlcannonAuthor Commented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
what kind of database? what is the data type of this field?
0
 
jlcannonAuthor Commented:
I am connecting to and excel sheet
0
 
jlcannonAuthor Commented:
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.
0
 
jlcannonAuthor Commented:
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!.
0
 
jlcannonAuthor Commented:
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.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now