We help IT Professionals succeed at work.

Display data from EXCEL datasource on ASP Page

trondina
trondina asked
on
258 Views
Last Modified: 2008-01-09
I am trying to create an ASP page using VBSCRIPT that will retrieve all stores located in a state (as selected in a listbox). I have about 50 stores in an excel (.xls) file that I will use as the datasource.
My objective is to allow the user to select the state from the drop down, hit the SUBMIT button, and then a listing of all stores located in the selected state will be returned grouped and sorted by city.

To help you with the datasource, the fields are zip, retailer, address1, city, state, country, phone. The first row of the file is a header row and the number or rows coudl increase or decrease as retailers are added and deleted from eth file.


The output to the asp page shoudl be something like:

XYZ SPORTING GOODS
123 N Main St.
New York, NY. 02223
(315) 221-4352

ABC SPORTING GOODS
987 73rd St.
New York, NY. 02223
(315) 211-1322

I have started the page, but have not scripted in a few years and would like to make thsi a quick turn around page So i have come to the EXPERTS.


Below is the ASP page I have started. It may be wrong but ist a start.
Thanks in advance



<%@ language="vbscript"%>

<%
option explicit

const adUseClient = 3
const adOpenStatic = 3
const adLockReadOnly = 1


dim state

'// get the input from the form
state = Trim(Request.Form("states"))


sub ShowLocations()
      dim conn, RS
      
      '// create connection object
    set conn = Server.CreateObject("ADODB.Connection")
      conn.open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("./_datasource/City Locs retailers.xls") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
      
      '// Create Command
      SQLstmt="SELECT * FROM [City Locs retailers$]"    
      
      '// Execute command
    set RS = conn.Execute(SQLstmt)
      
      while not RS EOF
            response.write RS("State")
            RS.MoveNext
      wend
      
      conn.Close
      
end sub
%>


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
  <title>Store Locator</title>

<style type="text/css">
      <!--
      .grey, ol {
            color: #999;
      }
      
      .boldgrey {
            color: #999;
            font-weight: bold;
      }
      
      .blue {
            color: #3366ff;
      }
      -->
</style>

</head>
<body style="background-color: rgb(51, 51, 51); color: rgb(0, 0, 0);" alink="#ee0000" link="#0000ee" vlink="#551a8b">
      <div style="text-align: center;">
          <br><br>
            <br style="color: rgb(51, 102, 255); font-weight: bold;">
            <big style="color: rgb(51, 102, 255); font-weight: bold;"><big>Eyewear Retail Locator</big></big><br>
            <br>
      </div>
<%

if LenB(state) <> 0 then
      ShowLocations
end if

%>
<form method="POST" action="" name="form1">
  <div align="center">
    <table border="0" cellpadding="2" cellspacing="0" style="border-style: solid; border-width: 1px" bordercolor="#808080">
      <tr>
        <td colspan="2" align="center">
          <p class="boldgrey">Find retail locations in:&nbsp;
            <select name="states">
            <option value="" selected>Choose a State
              <option value="Canada">Canada
              <option value="AL">Alabama
              <option value="AK">Alaska
              <option value="AZ">Arizona
              <option value="AR">Arkansas
              <option value="CA">California
              <option value="CO">Colorado
              <option value="CT">Connecticut
              <option value="DE">Delaware
              <option value="DC">District Of Columbia
              <option value="FL">Florida
              <option value="GA">Georgia
              <option value="HI">Hawaii
              <option value="ID">Idaho
              <option value="IL">Illinois
              <option value="IN">Indiana
              <option value="IA">Iowa
              <option value="KS">Kansas
              <option value="KY">Kentucky
              <option value="LA">Louisiana
              <option value="ME">Maine
              <option value="MD">Maryland
              <option value="MA">Massachusetts
              <option value="MI">Michigan
              <option value="MN">Minnesota
              <option value="MS">Mississippi
              <option value="MO">Missouri
              <option value="MT">Montana
              <option value="NE">Nebraska
              <option value="NV">Nevada
              <option value="NH">New Hampshire
              <option value="NJ">New Jersey
              <option value="NM">New Mexico
              <option value="NY">New York
              <option value="NC">North Carolina
              <option value="ND">North Dakota
              <option value="OH">Ohio
              <option value="OK">Oklahoma
              <option value="OR">Oregon            
              <option value="PA">Pennsylvania            
              <option value="RI">Rhode Island            
              <option value="SC">South Carolina
              <option value="SD">South Dakota
              <option value="TN">Tennessee
              <option value="TX">Texas
              <option value="UT">Utah
              <option value="VT">Vermont
              <option value="VA">Virginia
              <option value="WA">Washington
              <option value="WV">West Virginia
              <option value="WI">Wisconsin
              <option value="WY">Wyoming
            </select>&nbsp;
                <input type="submit" name="B1" value="Find Locations"><br>
        </td>
      </tr>
      <tr>
        <td><span class="boldgrey">&nbsp;
       
        </span></td>
          <td>&nbsp;</td>
    </tr>
    </table>
  </div>
</form>
</body>
</html>
 
Comment
Watch Question

Commented:
i'm pretty sure you can execute a condition in your query....

for example:
SQLstmt="SELECT * FROM [City Locs retailers$] WHERE state = '" & state & "'"

can you post some example data so we can experimant?  or maybe even an excerpt from the file?  you could upload it to http://www.ee-stuff.com    just post a link here in the question to the file....

Author

Commented:
KEVp75

I have uploaded the datasource file to EE. Because of site requirements on EE, i had to change teh extension from .xls to .txt. Make sure to change it back before hooking it up.

Link:
https://filedb.experts-exchange.com/incoming/ee-stuff/4910-City-Locs-retailers.txt

If you can please provide me with full code, I woudl very much appreciate it.
Thanks
 

Commented:
ok.  wanted to give you an update with how I am currently testing:

<%@ language="vbscript"%>

<%
option explicit

const adUseClient = 3
const adOpenStatic = 3
const adLockReadOnly = 1


dim state

'// get the input from the form
state = Trim(Request.Form("states"))


sub ShowLocations()
      dim conn, RS, SQLstmt, rsArr, i, iC
     
      '// create connection object
    set conn = Server.CreateObject("ADODB.Connection")
      conn.open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/temp/City_Locs_retailers.xls") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
     
      '// Create Command
      SQLstmt="SELECT * FROM [City Locs retailers$]"    
     
      '// Execute command
    set RS = conn.Execute(SQLstmt)
      if not RS.EOF then
              rsArr = RS.GetRows()
      end if
      set RS = nothing
      conn.close
      set conn = nothing
      iC = 0
    for i = 0 to ubound(rsArr,2)
            if instr(1,state,rsArr(4,i)) > 0 then
                  iC = iC + 1
            end if
      next
      response.write(iC)
end sub
%>


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
  <title>Store Locator</title>

<style type="text/css">
      <!--
      .grey, ol {
            color: #999;
      }
     
      .boldgrey {
            color: #999;
            font-weight: bold;
      }
     
      .blue {
            color: #3366ff;
      }
      -->
</style>

</head>
<body style="background-color: rgb(51, 51, 51); color: rgb(0, 0, 0);" alink="#ee0000" link="#0000ee" vlink="#551a8b">
      <div style="text-align: center;">
          <br><br>
            <br style="color: rgb(51, 102, 255); font-weight: bold;">
            <big style="color: rgb(51, 102, 255); font-weight: bold;"><big>Eyewear Retail Locator</big></big><br>
            <br>
      </div>
<%

if LenB(state) <> 0 then
      ShowLocations
end if

%>
<form method="POST" action="/temp/default.asp" name="form1">
  <div align="center">
    <table border="0" cellpadding="2" cellspacing="0" style="border-style: solid; border-width: 1px" bordercolor="#808080">
      <tr>
        <td colspan="2" align="center">
          <p class="boldgrey">Find retail locations in:&nbsp;
            <select name="states">
            <option value="" selected>Choose a State
              <option value="Canada">Canada
              <option value="AL">Alabama
              <option value="AK">Alaska
              <option value="AZ">Arizona
              <option value="AR">Arkansas
              <option value="CA">California
              <option value="CO">Colorado
              <option value="CT">Connecticut
              <option value="DE">Delaware
              <option value="DC">District Of Columbia
              <option value="FL">Florida
              <option value="GA">Georgia
              <option value="HI">Hawaii
              <option value="ID">Idaho
              <option value="IL">Illinois
              <option value="IN">Indiana
              <option value="IA">Iowa
              <option value="KS">Kansas
              <option value="KY">Kentucky
              <option value="LA">Louisiana
              <option value="ME">Maine
              <option value="MD">Maryland
              <option value="MA">Massachusetts
              <option value="MI">Michigan
              <option value="MN">Minnesota
              <option value="MS">Mississippi
              <option value="MO">Missouri
              <option value="MT">Montana
              <option value="NE">Nebraska
              <option value="NV">Nevada
              <option value="NH">New Hampshire
              <option value="NJ">New Jersey
              <option value="NM">New Mexico
              <option value="NY">New York
              <option value="NC">North Carolina
              <option value="ND">North Dakota
              <option value="OH">Ohio
              <option value="OK">Oklahoma
              <option value="OR">Oregon            
              <option value="PA">Pennsylvania            
              <option value="RI">Rhode Island            
              <option value="SC">South Carolina
              <option value="SD">South Dakota
              <option value="TN">Tennessee
              <option value="TX">Texas
              <option value="UT">Utah
              <option value="VT">Vermont
              <option value="VA">Virginia
              <option value="WA">Washington
              <option value="WV">West Virginia
              <option value="WI">Wisconsin
              <option value="WY">Wyoming
            </select>&nbsp;
                <input type="submit" name="B1" value="Find Locations"><br>
        </td>
      </tr>
      <tr>
        <td><span class="boldgrey">&nbsp;
       
        </span></td>
          <td>&nbsp;</td>
    </tr>
    </table>
  </div>
</form>
</body>
</html>



gives me the correct # of records that should be displayed.  I will get back to you in a few with the real results.  For now, you can got to http://www.07th.com/temp/

to test it out

Commented:
ok.....here ya go...please format and change the connection string to how you need it:

<%@ language="vbscript"%>

<%
option explicit

const adUseClient = 3
const adOpenStatic = 3
const adLockReadOnly = 1


dim state

'// get the input from the form
state = Trim(Request.Form("states"))


sub ShowLocations()
      dim conn, RS, SQLstmt, rsArr, i, iC
     
      '// create connection object
    set conn = Server.CreateObject("ADODB.Connection")
      conn.open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/temp/City_Locs_retailers.xls") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
     
      '// Create Command
      SQLstmt="SELECT * FROM [City Locs retailers$]"    
     
      '// Execute command
    set RS = conn.Execute(SQLstmt)
      if not RS.EOF then
              rsArr = RS.GetRows()
      end if
      set RS = nothing
      conn.close
      set conn = nothing
      iC = 0
    for i = 0 to ubound(rsArr,2)
            if instr(1,state,rsArr(4,i)) > 0 then
                  iC = iC + 1
                  With Response
                        .write("Retailer: " & rsArr(1,i) & "<br />")
                        .write("Street: " & rsArr(2,i) & "<br />")
                        .write("City State, Zip, Country: " & rsArr(3,i) & " " & rsArr(4,i) & ", " & rsArr(0,i) & ", " & rsArr(5,i) & "<br />")
                        .write("Phone: " & rsArr(6,i) & "<hr />")
                  End With
            end if
      next
      response.write("NUMBER OF RECORDS:" & iC & "<br />")
end sub
%>


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
  <title>Store Locator</title>

<style type="text/css">
      <!--
      .grey, ol {
            color: #999;
      }
     
      .boldgrey {
            color: #999;
            font-weight: bold;
      }
     
      .blue {
            color: #3366ff;
      }
      -->
</style>

</head>
<body style="background-color: rgb(51, 51, 51); color: rgb(0, 0, 0);" alink="#ee0000" link="#0000ee" vlink="#551a8b">
      <div style="text-align: center;">
          <br><br>
            <br style="color: rgb(51, 102, 255); font-weight: bold;">
            <big style="color: rgb(51, 102, 255); font-weight: bold;"><big>Eyewear Retail Locator</big></big><br>
            <br>
      </div>
<%

if LenB(state) <> 0 then
      ShowLocations
end if

%>
<form method="POST" action="/temp/default.asp" name="form1">
  <div align="center">
    <table border="0" cellpadding="2" cellspacing="0" style="border-style: solid; border-width: 1px" bordercolor="#808080">
      <tr>
        <td colspan="2" align="center">
          <p class="boldgrey">Find retail locations in:&nbsp;
            <select name="states">
            <option value="" selected>Choose a State
              <option value="Canada">Canada
              <option value="AL">Alabama
              <option value="AK">Alaska
              <option value="AZ">Arizona
              <option value="AR">Arkansas
              <option value="CA">California
              <option value="CO">Colorado
              <option value="CT">Connecticut
              <option value="DE">Delaware
              <option value="DC">District Of Columbia
              <option value="FL">Florida
              <option value="GA">Georgia
              <option value="HI">Hawaii
              <option value="ID">Idaho
              <option value="IL">Illinois
              <option value="IN">Indiana
              <option value="IA">Iowa
              <option value="KS">Kansas
              <option value="KY">Kentucky
              <option value="LA">Louisiana
              <option value="ME">Maine
              <option value="MD">Maryland
              <option value="MA">Massachusetts
              <option value="MI">Michigan
              <option value="MN">Minnesota
              <option value="MS">Mississippi
              <option value="MO">Missouri
              <option value="MT">Montana
              <option value="NE">Nebraska
              <option value="NV">Nevada
              <option value="NH">New Hampshire
              <option value="NJ">New Jersey
              <option value="NM">New Mexico
              <option value="NY">New York
              <option value="NC">North Carolina
              <option value="ND">North Dakota
              <option value="OH">Ohio
              <option value="OK">Oklahoma
              <option value="OR">Oregon            
              <option value="PA">Pennsylvania            
              <option value="RI">Rhode Island            
              <option value="SC">South Carolina
              <option value="SD">South Dakota
              <option value="TN">Tennessee
              <option value="TX">Texas
              <option value="UT">Utah
              <option value="VT">Vermont
              <option value="VA">Virginia
              <option value="WA">Washington
              <option value="WV">West Virginia
              <option value="WI">Wisconsin
              <option value="WY">Wyoming
            </select>&nbsp;
                <input type="submit" name="B1" value="Find Locations"><br>
        </td>
      </tr>
      <tr>
        <td><span class="boldgrey">&nbsp;
       
        </span></td>
          <td>&nbsp;</td>
    </tr>
    </table>
  </div>
</form>
</body>
</html>

Author

Commented:
Kev:

Thanks its looking good so far. The one addition things that i need:
1. results must be Groups and sorted by city name.
(You forgot to include the City name in the results, I added that no problem in my code here.)

I know it is just  a matter of adding GROUP BY and SORT BY claeses to the query, but I am not sure how to handle the syntax of passing the city parameter into those two clauses since VB SCRIPT is so wierd with escaping Qoutations.

Thsi will be it and then i will award solution and points to you.
And thanks for the help.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
glad I could help, thanks for the grade!  =D
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.