INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

reduce number of sql statements and set EOF parameter

reduce number of sql statements and set EOF parameter

(OP)
Hello, my website is here:

[URL="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:

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

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
%> 

RE: reduce number of sql statements and set EOF parameter

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

RE: reduce number of sql statements and set EOF parameter

(OP)
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

RE: reduce number of sql statements and set EOF parameter

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

I'll look online.

Thanks

RE: reduce number of sql statements and set EOF parameter

Try this:

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
%>

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:

CODE

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

RE: reduce number of sql statements and set EOF parameter

(OP)
Hello George, thank you very much for that.

It is working now but still producing an error:

Here is the updated code:

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 ;)

RE: reduce number of sql statements and set EOF parameter

"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:

CODE

Dim bFound
bFound = True 

Then change your Else to:

CODE

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

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

CODE

If Not RS Is Nothing Then
If bFound Then 

RE: reduce number of sql statements and set EOF parameter

Quote:

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

RE: reduce number of sql statements and set EOF parameter

(OP)
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.

RE: reduce number of sql statements and set EOF parameter

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

RE: reduce number of sql statements and set EOF parameter

(OP)
No desert? ;)

RE: reduce number of sql statements and set EOF parameter

(OP)
Hi Guitarzan, that has solved my problem.

Here is the full code

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.

RE: reduce number of sql statements and set EOF parameter

Quote (axLW )

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close