Retrieving data from Excel

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic


   I have the following code to retrieve info from an Excel file through ASP.  
-----search.htm-------
 
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Search Farm</title>
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1" height="237">
  <tr>
    <td width="14%" height="237">&nbsp;</td>
    <td width="62%" height="237" valign="top">
   <form method="POST" action="searchfarm2.asp" target="foo" onsubmit="window.open('', 'foo', 'width=600,height=300,status=yes,resizable=no,scrollbars=no')">
  <!--   <form method="POST" action="searchfarm2.asp">-->
      <p><font face="Arial" size="2">Farm </font> <input type="text" name="T1" size="20"></p>
      <p><input type="submit" value="Submit" name="B1"></p>
    </form>
    </td>
    <td width="24%" height="237">&nbsp;</td>
  </tr>
</table>
</body>
</html>
 
 
-----searchfarm2.asp----
<html>
<head>

<META http-equiv=Content-Type content="text/html; charset=windows-1256">
<title>Farms Database</title>
<style>
th {background:black;color:white}
.value {background:white;color:black}
.count {background:silver;color:black}
</style>
</head>
<body bgcolor=white >
<%
varfarm=request.form("T1")
 
 
' Change the following variables to reflect
' the names and locations for your file and
' table(s)
PATH="D:\agrdb\Update\tables\"
'PATH="D:\work\"
FILE="FARMOWN2.xls"
TABLE="farms"
' Change this SQL statement as required
SQL="SELECT * FROM farms WHERE Farm_no=" & varfarm
DRIVER="{Microsoft Excel Driver (*.xls)}"
DB="DBQ=" & PATH & FILE & ";"
DB= DB & "DefaultDir=" & PATH & ";"
DB= DB & "Driver=" & DRIVER & ";"
DB = DB & "FIL=excel 5.0;ReadOnly=1;"
Set DataConn=Server.CreateObject("ADODB.Connection")
DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30
DataConn.Open DB
Set RS_item = DataConn.Execute(SQL)
%>
<%
response.write "<table border cellspacing=0><tr>"
if RS_item.EOF then
     response.write "    <td colspan=10>No Records found</td></table>"
else
    response.write "</table>"
 response.write "<p><font face=Arial size=1>Record no. :"%>
 <%=RS_item("no") %>
<% response.write "</font></p>"
 response.write "<p><font face=Arial size=2>Details of Farm No. <B>"%> 
 <%=RS_item("Farm_no")%> 
 <%response.write "</B></font></p>"
  response.write "<table border=0 cellpadding=3 cellspacing=0 style='border-collapse: collapse' bordercolor=#111111 width=100% id=AutoNumber1>"
response.write "  <tr>"
response.write "    <td width='8%'>&nbsp;</td>"
response.write "   <td width='25%' valign=top><font face=Arial size=2>Owner</font></td>"
response.write "    <td width='67%'>"%> 
<%=RS_item("Ename")%> 
<%response.write"<br>"%>
<%=RS_item("Aname")%>
<%response.write "    </td>"
response.write "  </tr>"
response.write "  <tr>"
response.write "    <td width='8%'>&nbsp;</td>"
response.write "    <td width='25%' valign='top'><font face='Arial' size='2'>Agricultural District</font></td>"
response.write "    <td width='67%'><p>"%>
<%=RS_item("Dist")%>
<%response.write"<br>"%>
<%=RS_item("Edist")%>    
<%response.write "    </td>"
response.write "  </tr>"
response.write "  <tr>"
response.write "    <td width='8%'>&nbsp;</td>"
response.write "    <td width='25%' valign='top'><font face='Arial' size='2'>Location</font></td>"
response.write "    <td width='67%'>"%>
 <%=RS_item("AArea")%>
 <%response.write"</td>"
response.write "  </tr>"
response.write "  <tr>"
response.write "    <td width='8%'>&nbsp;</td>"
response.write "    <td width='25%' valign='top'><font face='Arial' size='2'>Area (Donum)</font></td>"
response.write "    <td width='67%'> "%>
<%=RS_item("GArea")%>
<%response.write"</td>"
response.write "  </tr>"
response.write "</table>"
 
-------------------------
The code executes fine the first time, but when i give a second number to the excel file to retrieve another data, it gives an error in connection.  Any ideas??
thanks