reduce number of sql statements and set EOF parameter

[axLW]

Hello, my website is here:

http://londonheathrowcars.com/01input.asp"]

http://londonheathrowcars.com/01input.asp

[/URL]

It retrieves prices from an access database.

The form can give you a price TO or FROM any of the five airports listed.

For example, if you choose:

Heathrow Airport to London Euston - it gives you a price

If you choose:

SW1 to Gatwick Airport - it gives you a price

etc, etc

The form DOES NOT give you a price if neither the pickup nor dropoff is any of the five airports listed. That's fine.

So if you choose:

London Euston to SW1 - it gives an error:



ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/02process.asp, line 63



I simply want to display a snippet of text saying 'No Record Found'. Whenever the form cannot locate a matching record I want this message displayed. I can then adjust that section of the HTML accordingly.

As an added bonus, I have used several SQL statements. The code works fine but if anybody can suggest a more efficient way doing it then feel free.

Thank you all. My full ASP code is below:

<%

sTempVar = "&#"

pickup = request.form("frm-pick")
dropoff = request.form("frm-drop")

set myconn = Server.CreateObject("ADODB.connection")
connection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("x-db.mdb") & ";"
myconn.open (connection)

set rs=Server.CreateObject("ADODB.recordset")

If pickup = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & pickup & "' ", myconn

End If

%>

<!--HTML START -->

<h1><%=pickup%> to <%=dropoff%></h1>

<p>SB: <%=sTempVar & "163;" & trim(rs("salbrnz"))%></p>

<p>SS: <%=sTempVar & "163;" & trim(rs("salsilv"))%></p>

<p>SG: <%=sTempVar & "163;" & trim(rs("salgold"))%></p>

<p>EB: <%=sTempVar & "163;" & trim(rs("estbrnz"))%></p>

<p>MB: <%=sTempVar & "163;" & trim(rs("mpvbrnz"))%></p>

<p>MS: <%=sTempVar & "163;" & trim(rs("mpvsilv"))%></p>

<p>VB: <%=sTempVar & "163;" & trim(rs("vanbrnz"))%></p>

<p>VS: <%=sTempVar & "163;" & trim(rs("vansilv"))%></p>

<!--HTML FINISH -->

<%
rs.Close
Set rs = Nothing
myconn.Close
Set myconn = Nothing
%>

 

[ChrisHirst]

Check that the recordset actually contains at least one record before displaying it.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[axLW]

Hi Chris, I am aware that no record will be found. I just want to display an actual message NO RECORD FOUND when no record is found rather than the error itself.

I'm sure I need to use if EOF then .... etc

Not sure how I go about it.

Thanks

 

[ChrisHirst]

if [records] > 0 then
[indent]do this[/indent]
else 
[indent]do this instead[/indent]
end if

http://www.w3schools.com/vbscript/vbscript_conditionals.asp

http://www.devguru.com/technologies/ado/home

http://www.w3schools.com/asp/prop_rs_recordcount.asp

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[axLW]

Sorry Chris, you aren't really helping me here.

I'll look online.

Thanks

 

[gmmastros]

Try this:

<%

sTempVar = "&#"

pickup = request.form("frm-pick")
dropoff = request.form("frm-drop")

set myconn = Server.CreateObject("ADODB.connection")
connection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("x-db.mdb") & ";"
myconn.open (connection)

set rs=Server.CreateObject("ADODB.recordset")

If pickup = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & pickup & "' ", myconn

[!]Else
[indent]%><p>No Record Found</p><%[/indent]
[/!]
End If

[!]If Not RS Is Nothing Then
[indent]If Not RS.EOF Then[/indent][/!]
%>

<!--HTML START -->

<h1><%=pickup%> to <%=dropoff%></h1>

<p>SB: <%=sTempVar & "163;" & trim(rs("salbrnz"))%></p>

<p>SS: <%=sTempVar & "163;" & trim(rs("salsilv"))%></p>

<p>SG: <%=sTempVar & "163;" & trim(rs("salgold"))%></p>

<p>EB: <%=sTempVar & "163;" & trim(rs("estbrnz"))%></p>

<p>MB: <%=sTempVar & "163;" & trim(rs("mpvbrnz"))%></p>

<p>MS: <%=sTempVar & "163;" & trim(rs("mpvsilv"))%></p>

<p>VB: <%=sTempVar & "163;" & trim(rs("vanbrnz"))%></p>

<p>VS: <%=sTempVar & "163;" & trim(rs("vansilv"))%></p>

<!--HTML FINISH -->

<%
rs.Close
Set rs = Nothing
myconn.Close
Set myconn = Nothing
[!][indent]End If[/indent]
End If[/!]
%>

As far as the number of different SQL statements....

It appears as though you have 5 different tables to store the data you need. Instead of 5 tables, I would suggest you use just 1 table instead. To make this work, you will need to add another column to the combined table. Basically, you'll want to have FromPostcode and ToPostcode columns. Then your SQL would be:

Select Column(s)
From   CombinedTable
Where  FromPostCode = Pickup
       And ToPostCode = Dropoff

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[axLW]

Hello George, thank you very much for that.

It is working now but still producing an error:

Here is the updated code:

<%

sTempVar = "&#"

pickup = request.form("frm-pick")
dropoff = request.form("frm-drop")

set myconn = Server.CreateObject("ADODB.connection")
connection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("x-db.mdb") & ";"
myconn.open (connection)

set rs=Server.CreateObject("ADODB.recordset")

If pickup = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & pickup & "' ", myconn

Else
%><p>No Record Found</p><%
End If

If Not RS Is Nothing Then
If Not RS.EOF Then
%>

<!--HTML START -->

<h1><%=pickup%> to <%=dropoff%></h1>

<p>SB: <%=sTempVar & "163;" & trim(rs("salbrnz"))%></p>

<p>SS: <%=sTempVar & "163;" & trim(rs("salsilv"))%></p>

<p>SG: <%=sTempVar & "163;" & trim(rs("salgold"))%></p>

<p>EB: <%=sTempVar & "163;" & trim(rs("estbrnz"))%></p>

<p>MB: <%=sTempVar & "163;" & trim(rs("mpvbrnz"))%></p>

<p>MS: <%=sTempVar & "163;" & trim(rs("mpvsilv"))%></p>

<p>VB: <%=sTempVar & "163;" & trim(rs("vanbrnz"))%></p>

<p>VS: <%=sTempVar & "163;" & trim(rs("vansilv"))%></p>

<!--HTML FINISH -->

<%
rs.Close
Set rs = Nothing
myconn.Close
Set myconn = Nothing
End If
End If
%>

The page does display 'No Record Found' however, it also says the recordset is closed..

The error is:



No Record Found

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/02process.asp, line 60


Line 60 is If Not RS.EOF Then


I see what you are saying with regards to the database but I think I'll probably leave it as it is because it seems to be working fine and I think I'm just being anal

 

[guitarzan]

If Not RS Is Nothing Then" will not show you if the recordset has never been opened. The easist way I can think of right now is to use a flag, for example, before the long IF/ELSEIF, add:

Dim bFound
bFound = True

Then change your Else to:

Else
[highlight #FCE94F]bFound = False[/highlight]
%><p>No Record Found</p><%

And replace "If Not RS Is Nothing Then" with:

[s]If Not RS Is Nothing Then[/s]
If bFound Then

 

[ChrisHirst]

Sorry Chris, you aren't really helping me here.

I'm not here to write your code for you, I'm simply going to give you hints on how you can LEARN to code it for yourself so that at some point in the future you will not need to ask some anonymous stranger at an discussion forum that your boss may ALSO be reading and wondering why the hell were you hired in the first place.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[axLW]

I'm 32 years old and am my own boss. I have also been using forums for ten years (note my very simplified and detailed request). I am not asking you to write my code for me. I produced some code and detailed the error. If you do not have the time or the inclination to give me a working example then that's fine, but please don't talk to me like I don't know how forums work.

 

[ChrisHirst]

Maybe, but you don't know how how I work.


I prefer the "Teach some one to fish" method rather than presenting them with a fish that has already been cleaned, filleted, battered and fried then served with a side order of chips AND peas.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[axLW]

No desert?

 

[axLW]

Hi Guitarzan, that has solved my problem.

Here is the full code

<%

sTempVar = "&#"

pickup = request.form("frm-pick")
dropoff = request.form("frm-drop")

set myconn = Server.CreateObject("ADODB.connection")
connection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("x-db.mdb") & ";"
myconn.open (connection)

set rs=Server.CreateObject("ADODB.recordset")

Dim bFound
bFound = True 

If pickup = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Heathrow Airport" Then

rs.Open "SELECT tbllhr.postcode, tbllhr.salbrnz, tbllhr.salsilv, tbllhr.salgold, tbllhr.estbrnz, tbllhr.mpvbrnz, tbllhr.mpvsilv, tbllhr.vanbrnz, tbllhr.vansilv FROM tbllhr WHERE tbllhr.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Gatwick Airport" Then

rs.Open "SELECT tbllgw.postcode, tbllgw.salbrnz, tbllgw.salsilv, tbllgw.salgold, tbllgw.estbrnz, tbllgw.mpvbrnz, tbllgw.mpvsilv, tbllgw.vanbrnz, tbllgw.vansilv FROM tbllgw WHERE tbllgw.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Stansted Airport" Then

rs.Open "SELECT tblstn.postcode, tblstn.salbrnz, tblstn.salsilv, tblstn.salgold, tblstn.estbrnz, tblstn.mpvbrnz, tblstn.mpvsilv, tblstn.vanbrnz, tblstn.vansilv FROM tblstn WHERE tblstn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "Luton Airport" Then

rs.Open "SELECT tblltn.postcode, tblltn.salbrnz, tblltn.salsilv, tblltn.salgold, tblltn.estbrnz, tblltn.mpvbrnz, tblltn.mpvsilv, tblltn.vanbrnz, tblltn.vansilv FROM tblltn WHERE tblltn.postcode = '" & pickup & "' ", myconn

ElseIf pickup = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & dropoff & "' ", myconn

ElseIf dropoff = "London City Airport" Then

rs.Open "SELECT tbllcy.postcode, tbllcy.salbrnz, tbllcy.salsilv, tbllcy.salgold, tbllcy.estbrnz, tbllcy.mpvbrnz, tbllcy.mpvsilv, tbllcy.vanbrnz, tbllcy.vansilv FROM tbllcy WHERE tbllcy.postcode = '" & pickup & "' ", myconn

Else
bFound = False
%><p>No Record Found</p><%
End If

If bFound Then 
If Not RS.EOF Then
%>

<!--HTML START -->

<h1><%=pickup%> to <%=dropoff%></h1>

<p>SB: <%=sTempVar & "163;" & trim(rs("salbrnz"))%></p>

<p>SS: <%=sTempVar & "163;" & trim(rs("salsilv"))%></p>

<p>SG: <%=sTempVar & "163;" & trim(rs("salgold"))%></p>

<p>EB: <%=sTempVar & "163;" & trim(rs("estbrnz"))%></p>

<p>MB: <%=sTempVar & "163;" & trim(rs("mpvbrnz"))%></p>

<p>MS: <%=sTempVar & "163;" & trim(rs("mpvsilv"))%></p>

<p>VB: <%=sTempVar & "163;" & trim(rs("vanbrnz"))%></p>

<p>VS: <%=sTempVar & "163;" & trim(rs("vansilv"))%></p>

<!--HTML FINISH -->

<%
rs.Close
Set rs = Nothing
myconn.Close
Set myconn = Nothing
End If
End If
%>

Many thanks.

 

[ChrisHirst]

No desert?

Only if you are paying.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum