Solved

Filter data ifrom excel fto a web page

Posted on 2011-03-16
8
340 Views
Last Modified: 2012-05-11
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
Comment
Question by:jlcannon
  • 5
  • 2
8 Comments
 
LVL 33

Accepted Solution

by:
paulmacd earned 250 total points
ID: 35150616
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
 
LVL 32

Assisted Solution

by:Big Monty
Big Monty earned 250 total points
ID: 35150685
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
 

Author Comment

by:jlcannon
ID: 35150808
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 35150821
what kind of database? what is the data type of this field?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:jlcannon
ID: 35150869
I am connecting to and excel sheet
0
 

Author Comment

by:jlcannon
ID: 35160371
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
 

Author Comment

by:jlcannon
ID: 35160397
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
 

Author Closing Comment

by:jlcannon
ID: 35190808
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now