Improve company productivity with a Business Account.Sign Up

x
?
Solved

Filter data ifrom excel fto a web page

Posted on 2011-03-16
8
Medium Priority
?
357 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 35

Accepted Solution

by:
Paul MacDonald earned 1000 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 34

Assisted Solution

by:Big Monty
Big Monty earned 1000 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 34

Expert Comment

by:Big Monty
ID: 35150821
what kind of database? what is the data type of this field?
0
 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming…
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

584 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