Display data from EXCEL datasource on ASP Page
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
conn.open = "Provider=Microsoft.Jet.OL
'// 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:
<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>
<input type="submit" name="B1" value="Find Locations"><br>
</td>
</tr>
<tr>
<td><span class="boldgrey">
</span></td>
<td> </td>
</tr>
</table>
</div>
</form>
</body>
</html>
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
conn.open = "Provider=Microsoft.Jet.OL
'// 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:
<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>
<input type="submit" name="B1" value="Find Locations"><br>
</td>
</tr>
<tr>
<td><span class="boldgrey">
</span></td>
<td> </td>
</tr>
</table>
</div>
</form>
</body>
</html>
ASKER
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
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
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
conn.open = "Provider=Microsoft.Jet.OL
'// 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"
<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:
<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>
<input type="submit" name="B1" value="Find Locations"><br>
</td>
</tr>
<tr>
<td><span class="boldgrey">
</span></td>
<td> </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
<%@ 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
conn.open = "Provider=Microsoft.Jet.OL
'// 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"
<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:
<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>
<input type="submit" name="B1" value="Find Locations"><br>
</td>
</tr>
<tr>
<td><span class="boldgrey">
</span></td>
<td> </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
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
conn.open = "Provider=Microsoft.Jet.OL
'// 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"
<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:
<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>
<input type="submit" name="B1" value="Find Locations"><br>
</td>
</tr>
<tr>
<td><span class="boldgrey">
</span></td>
<td> </td>
</tr>
</table>
</div>
</form>
</body>
</html>
<%@ 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
conn.open = "Provider=Microsoft.Jet.OL
'// 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"
<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:
<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>
<input type="submit" name="B1" value="Find Locations"><br>
</td>
</tr>
<tr>
<td><span class="boldgrey">
</span></td>
<td> </td>
</tr>
</table>
</div>
</form>
</body>
</html>
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
glad I could help, thanks for the grade! =D
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....