Solved

Filter data ifrom excel fto a web page

Posted on 2011-03-16
8
345 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 34

Accepted Solution

by:
Paul MacDonald 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 33

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
LVL 33

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBSCRIPT string remove all characters to the right after extension 4 67
Microsoft SQL ADO Conn Issue 6 50
innerHTML 7 35
Html value of radio 14 31
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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/…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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