?
Solved

Display data from EXCEL datasource on ASP Page

Posted on 2007-10-03
7
Medium Priority
?
241 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>
 
0
Comment
Question by:trondina
  • 5
  • 2
7 Comments
 
LVL 25

Expert Comment

by:kevp75
ID: 20007281
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....
0
 

Author Comment

by:trondina
ID: 20009567
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
 
0
 
LVL 25

Expert Comment

by:kevp75
ID: 20009903
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:kevp75
ID: 20009960
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>
0
 

Author Comment

by:trondina
ID: 20010613
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.
0
 
LVL 25

Accepted Solution

by:
kevp75 earned 2000 total points
ID: 20010960
i'm not sure what you need grouped, but your can sort by adding in ORDER BY City to the end of the query
0
 
LVL 25

Expert Comment

by:kevp75
ID: 20011168
glad I could help, thanks for the grade!  =D
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

864 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