• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

"Store Locator" from CSV File using File System Object

The existing ASP page allows a user to search for stores by state. State values are determined by the drop down box selected value located on the page. The locator returns the result such in the following format:

ABC STORES
19777 N 71st Way
Phoenix, AZ. 85288, US
(602) 888-2315


We were using an ODBC connection to an excel spread sheet as the data source.
Unfortunately the hosting provider no longer allows ODBC connections so we need an alternative solution.

It seems like the next logical solution is to read from the data source using the filesystem object.
I have converted the .xls file we were using to a comma delimited csv file and will provide the link in my next post.

Below is the existing code utilizing the ODBC connection. Please convert code so that the locator will still return the results  using the FSO. 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, 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("./_datasource/City Locs retailers.xls") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
     
      '// Create Command
      SQLstmt="SELECT * FROM [City Locs retailers$] ORDER BY City"    
     
      '// 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
        
      response.write "<table width="&chr(34)&"400"&chr(34)&"align="&chr(34)&"center"&chr(34)&">"
      response.write "<tr><td><ol>"
      
    for i = 0 to ubound(rsArr,2)
            if instr(1,state,rsArr(4,i)) > 0 then
                  iC = iC + 1
                  With Response
                                      
                                      .Write "<li><p>"
                                    .Write "<span class="&chr(34)&"boldgrey"&chr(34)& ">" & rsArr(1,i) & "</span><br />"
                                    .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & rsArr(2,i) & "</span><br />"
                                    .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & rsArr(3,i) & ", "& rsArr(4,i) & ". " & rsArr(0,i) & ", " & rsArr(5,i) & "</span><br />"
                                    .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & rsArr(6,i) & "</span><br />"
                                    .Write "</p></li>"
                         
                  End With
            end if
      next
        Response.write("<br /><p>Number of Stores in " & state & ":  " & iC & "</p>")
        Response.Write "</ol></td></tr></table>"
     
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">
      <!--
        body {
              background-color: #000;
            }
            
        p, h2 {
              font-family: Verdana, Arial, Helvetica, sans-serif;
            }
            
        p {
              font-size:11px;
            color: #fff;
            }
        
      .grey, ol {
            color: #999;
      }
     
      .boldgrey {
            color: #999;
            font-weight: bold;
      }
        
        .boldwhite {
                    color: #eee;
            font-weight: bold;
                  }
     
      .blue, h2 {
            color: #3366ff;
      }
      -->
</style>

</head>
<body>
        
     
      <div style="text-align: center;">
          <br><br>
            <h2>Eyewear Retail Locator</h2>
            <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">
      <tr>
        <td align="center">
          <hr>
          <p class="boldwhite">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>
          <hr>
             
                </td>
      </tr>
    </table>
  </div>
</form>


</body>
</html>


0
trondina
Asked:
trondina
  • 9
  • 4
  • 3
  • +1
1 Solution
 
trondinaAuthor Commented:
csv file can be downloaded at

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

i had to change it to a .txt file because of EE requirements. Be sure to rename it back to .csv extension after download
0
 
lrygielCommented:
why keep it excel and use an OLE DB connections string instead of ODBC. The OLE DB connection string is

<%
dim oDBCONN
set oDBCONN = server.CreateObjcet("ADODB.CONNECTION")
oDBCONN.OPEN "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
%>

NOTE The escaped "" in the string, these are required.
0
 
lrygielCommented:
trondina:
Disregard that last post. I've beemn up since 4am and just noticed you were using the extended provider.
0
Industry Leaders: 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!

 
kevp75Commented:
just for reference....
http://www.connectionstrings.com/
0
 
trondinaAuthor Commented:
Gentleman....

PLEASE RE-READ MY PROBLEM -

The code i provided works for ODBC connections. Hwwever the hosting provider no longer allows ODBC connections so i need an alternative solution. I am thinking use the file system object since it does not require ODBC. I supplied to code because I need one of the experts to convert it so it uses the FSO.

Can anybody help me?
0
 
lrygielCommented:
Firstly, you are not using an ODBC, you are using DB OLE. The solution I provided works for NON ODBC connections. It is a DNS less connection string and does not require ODBC to be installed. The premise of your problem was your ISP no longer allows ODBC and  I assumed you wanted a NON ODBC solution.

However if you wasnt an FSO solution it will follow.
0
 
trondinaAuthor Commented:
Iryqiel

My apologies, I am obviously wet behind the ears when it comes to the differences between an ODBC and NON ODBC connections. I am 100% sure that I would like an FSO solution because I have confirmed with the hosting provider that this will work on their server.

I would very much appreciate it if you coudl convert my existing code over.
Please view my original post that provided you with the csv fiel you will need for development.

Thanks
0
 
lrygielCommented:
will do. give me a few
0
 
kevp75Commented:
>>I have confirmed with the hosting provider that this will work on their server<<

have you tried it out?  Alot of times hosts will lie through their teeth when it comes to this stuff.  (trust me, I am one...)

You really do want to stay away from an FSO solution to this, firstly you would have to change the file to be a .csv file and not an excel file, because a .xls file read as straight text is funky (with version prior to 2007, after 2007 it is a form of XML)

secondly, FSO accessing this would be considerably slower
0
 
lrygielCommented:
replace your ShowLocations sub with this and make sure your City_Locs_retailers.csv is in the root directory or you willhave to modify the mappath statement.

=========================
sub ShowLocations()
    dim f, fs, i, iC
     
      Set fs=Server.CreateObject("Scripting.FileSystemObject")
      Set f=fs.OpenTextFile(Server.MapPath("City_Locs_retailers.csv"), 1)
     
      iC = 0
       
      response.write "<table width="&chr(34)&"400"&chr(34)&"align="&chr(34)&"center"&chr(34)&">"
      response.write "<tr><td><ol>"
     
      while not f.atEndOfStream
      rec = f.ReadLine
      farray = split(rec,",")
        if instr(1,state,farray(4)) > 0 then
              iC = iC + 1
                  iC = iC + 1
                  With Response
                                     
                                    .Write "<li><p>"
                                    .Write "<span class="&chr(34)&"boldgrey"&chr(34)& ">" & farray(0) & "</span><br />"
                                    .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & farray(2) & "</span><br />"
                                    .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & farray(3) & ", "& farray(4) & ". " & farray(0) & ", " & farray(5) & "</span><br />"
                                    .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & farray(6) & "</span><br />"
                                    .Write "</p></li>"
                         
                  End With
            end if
      Wend
      Response.write("<br /><p>Number of Stores in " & state & ":  " & iC & "</p>")
      Response.Write "</ol></td></tr></table>"
     
end sub
====================
ALSO, Make sure the file is pre-sorted, as you will not have a SQL sort to work with.
0
 
lrygielCommented:
OOOPS... please correct the following lines:

             iC = iC + 1
                  iC = iC + 1


I stutter. :)
0
 
lrygielCommented:
Actually use this one, I left the phone number out the last one..

sub ShowLocations()
    dim f, fs, i, iC
     
      Set fs=Server.CreateObject("Scripting.FileSystemObject")
      Set f=fs.OpenTextFile(Server.MapPath("City_Locs_retailers.csv"), 1)
     
      iC = 0
       
      response.write "<table width="&chr(34)&"400"&chr(34)&"align="&chr(34)&"center"&chr(34)&">"
      response.write "<tr><td><ol>"
     
      while not f.atEndOfStream
      rec = f.ReadLine
      farray = split(rec,",")
            if instr(1,state,farray(4)) > 0 then
                iC = iC + 1
                With Response
                                   
                                  .Write "<li><p>"
                                  .Write "<span class="&chr(34)&"boldgrey"&chr(34)& ">" & farray(0) & "</span><br />"
                                  .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & farray(2) & "</span><br />"
                                  .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & farray(3) & ", "& farray(4) & ". " & farray(5) & ", " & farray(6) & "</span><br />"
                                  .Write "<span class="&chr(34)&"grey"&chr(34)& ">" & farray(7) & "</span><br />"
                                  .Write "</p></li>"
                       
                End With
            end if
      Wend
      Response.write("<br /><p>Number of Stores in " & state & ":  " & iC & "</p>")
      Response.Write "</ol></td></tr></table>"
     
end sub

0
 
lrygielCommented:
kevp75:
     >have you tried it out?  Alot of times hosts will lie through their teeth when it comes to this stuff.  (trust me, I am one...)

I think I used you're service ;)
0
 
kevp75Commented:
lol!  ;-)

it's possible.  Thankfully enough, I'm only an employee, and not the owner...however ti still reflects on me when we have to do stuff like that  (but, a pay check is a pay check...and I have a 4yr old to think about)
0
 
chisholmdCommented:
Not to throw a wrench in the works but I can't help but mention that unless your going to move your table into a database the next best solution would be an XML based solution.  In fact if its not very big you could load your store list into an XMLDom object in your global.asa then a simple xpath statement could be used to retrieve it for the search results.
0
 
trondinaAuthor Commented:
Thanks for all the input guys.

I did weight out the options originally.
Whiel FSO might be closwer....i dont think there will be a performance hit because teh csv file is only 80 records long with 6 columns per record.

I did consider the XML solution as well, but this is one of those template ecommerce package sites that you build on the hosting server. They seem to be very sensitive to custom ASP pages, so i am keeping it as simple as possible. 2 #includes for header nav and footer plus the ASP code. I really dont want to get my hands dirty in their pages.

In addition, it will be easier for teh store owner to modify the csv file that he is familiar with and just upload it as he makes store location additions and deletions.
I already converted the xls file to CSV...thats the least of the worries.

I will try to test the code friday. I attempted to test it Thursday on my XP machine  running IIS, but I get a Error Creating object of the FSO on the ASP page. Did some research and it seems to be a security permissions issue. I need to sort it out and then let you guys know if it worked.

Oh BTW..not to diss the HOST but the host is Volusion.com if you want to check them out. I really hate these ecommerce hosts who have developed these templated ecommerce packages. Never run across one yet that was easy to customize. And when i say customize, I dont mean customize within their created options. I mean customize like you would for any custom project.

Keep your eyeball to the monitor, I will update you guys friday or saturday.
0
 
lrygielCommented:
trodina:

Just a note to you. In your CSV file you have 2 columns of "address information". I didn't edit the file to remove the redundant column so the solution is based upon your 7 column CSV cile. If you want to fix the file, you will need to adjust the subscripts in the "IF" and "WRITE" statements accordingly.

Lee

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now