dklps
Posts: 1
|
| Posted: 03/25/2014, 10:15 AM |
|
I have created an asp page where it will search the an excel file and return the corresponding result to screen.
The code is the following
<% Option Explicit %>
<html>
<body>
<form method="get" action="get_val.asp">
Search: <input type="text" name="search"><br>
<input type="submit" value="Submit">
<input type="reset" value="Reset" onClick="changeLink()">
</form>
<div id="result">
<%
Dim search
search = Request.querystring("search")
%>
<%
if search ="" then
response.write("")
else
Dim objConn, objRS, strSQL
Dim x, curValue
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionTimeout = 5
objConn.CommandTimeout = 10
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("Book2.xls") & "; "
strSQL = "SELECT * FROM [Book2$] where [Id] like " & search & ""
Set objRS = objConn.Execute (strSQL)
Dim temp
temp = ""
curValue = "No value found for '" + search + "'"
Do Until objRS.EOF
curValue = objRS.Fields(1).Value
If curValue <> "" Then
curValue = temp & "<br />" & curValue
Else
curValue = "Cell Value Empty"
curValue = temp & "<br />" & curValue
End If
temp = curValue
objRS.MoveNext
Loop
Response.Write("" & curValue & "")
objRS.Close
objConn.Close
Set objRS=Nothing
Set objConn=Nothing
End If
%>
</div>
<script>
function changeLink()
{
document.getElementById('result').innerHTML="";
}
</script>
</body>
</html>
The problem lies on the fact that the file (book2.xls) is generated from a stored procedure and when i try to open it i receive a warning "The file format differs from the format that the file name extension specifies" . This causes problem to my asp page.
I have tried to use the
<%
string fileName = "Book2.xls";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
%>
it does open the file in the browser and i am able to see my data but this is not exactly what i need.
i want to be able to open and search inside the file
is there a way that i can manage this
|
 |
 |
eratech
Posts: 513
|
| Posted: 03/27/2014, 1:43 AM |
|
ODBC direct connections to Excel files - I haven't done that in years......
Odd that the error message "The file format differs from the format that the file name extension specifies" is the same as I get when I download an XLS file and Excel 2010 tries to open it (which expects XLSX).
So I wonder if it's something in the Excel version that it's expecting. I see you have selected Excel 8, but the ADODB/ODBC connections have been known to allow parameters and then ignore them entirely (I vaguely recall one of the options for rows to check for data types to be allowed, but ignored - so if your first 8 rows has integer data, but it was text after that, it would break even if you told it to look at 20 rows)
Not really helpful I'm sorry, but I'll try to.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
|
