Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts

    connecting to excel file

    I have some reports that are generated as excel files off of an AS/400 system and then are uploaded to the webserver. From there the information is displayed as html in a table via asp. One of these files in particular is only 400 or so rows but for some reason even though i am using Do Until objRs.EOF / Loop it is going way past that and giving me another 2200 empty rows. Needless to say this is really slowing the display of this down. I Tried looking for 2 consecutive empty rows and then using exit Do but that didnt work either Any one else have an idea? here is what the code looks like now.

    Code:
    <%
    Dim objConn, objRs, SQL, connString, strSearch, strState, i
    strSearch = Request.Form("State") 
    connString = Server.MapPath("AgentList.xls") & ";"
    'Response.Write connString & "<br>"
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open ("DRIVER={Microsoft Excel Driver (*.xls)}; DBQ=" & connString)
    SQL = "SELECT DISTINCT Mid([#,state,city],6,2) AS state FROM [AgentList$]"
    Set objRs = objConn.Execute(SQL)  
    %>
    <form method="post" action="Agent_lists.asp" name="thisForm">
    <table width="99%">
    	<tr>
    		<td>
    To Narrow the Results, Select the State from the List
    		</td>
    		<td title="If you don't want to see all results, then select the state to see only those agents in that state">
    			<select name="state" onChange="NarrowResults();" class="select">
    				<option value="">Select</option>
    <%Do Until objRs.EOF
    	strState = objRs("state")
    	If strState <> "" Then
    %>
    				<option value="<%=strState%>" <%If strState = strSearch Then Response.Write "selected"%>><%=strState%></option>													
    <%
    	End If
    	strState = ""
    	objRs.MoveNext
    Loop
    %>
    			</select>
    		</td>
    	</tr>
    </table>
    </form>
    <%
    objRs.close
    Set objRs = Nothing
    If Len(strSearch) = 0 Then
    	SQL = "SELECT * FROM [AgentList$]"
    Else
    	SQL = "SELECT * FROM [AgentList$] WHERE Mid([#,state,city],6,2) ='" & strSearch & "'"
    End If
    'Response.Write SQL
    Set objRS = objConn.Execute(SQL)
    %>
    <center>
    	* Designates Agents with Terminal Pay
    	<hr width="90%">
    </center>
    <TABLE border=0 CellPadding=3 width="99%"> 
    <TR align=left> 
    	<TH class="hdr" width="25%"># State City</TH> 
        <TH class="hdr" width="25%">Contact</TH> 
    	<TH class="hdr" width="20%">Equip</TH> 
    	<TH class="hdr" width="30%">Phones</TH> 
    </TR>
    <%
    objRs.MoveFirst
    Do While Not objRs.eof %> 
        <TR align=left>  
            <TD class="tblBody" width="25%"><% = objRs("#,State,City") %></TD> 
    		<TD class="tblBody" width="25%"><% = objRs("Contact")%></TD>
    		<TD class="tblBody" width="20%"><% = objRs("equip") %></TD>
    		<TD class="tblBody" width="30%"><% = objRs("phones")%></TD>
        </TR> 
    <%
    	objRs.MoveNext
                    If Len(objRs("#,State,City")) = 0 Then objRs.MoveNext
                    If Len(objRs("#,State,City")) = 0 Then Exit Do
    Loop 
    %>

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's likely the spreadsheet has the empty rows in itself and you're getting them in the returned recordset object. Try changing the SQL statements that pull the data to limit the recordset to the records with actual data.

    SELECT * FROM [AgentList$] WHERE Len([#,state,city]) > 0 -- may work.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #3
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts

    great Idea!!

    That did the trick. Thank you. it really sped up the loading.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •