|
|
|
|
|
| | |
| |
Active Server Pages ASP ASP.NET .aspx .ascx Web HTML Developer Internet Microsoft Web Services Visual Studio .NET CJWSoft ASPProtect ASPBanner ASPClassifieds
(Advanced)
(Components)
(Database)
(General)
(Vbscript)
|
|
|
|
Subject: |
Re: Access database selection problem |
|
From: |
Tomas Breen |
|
Date: |
3/9/1999 5:43:48 PM |
|
IP: |
194.133.7.17 |
I will try explaining what my problem is again.
First of all I have a HTML form that allows a user to enter search criteria. The search criteria is used to search a hotel database( a database that contains many tables ). An example of some of the fields that the user may enter are
Hotel Name
Hotel Grade
Hotel Price
Hotel Facilities
The user can enter any of these fields as he/she wants. A hotel may have many facilities, and so the user can select hotel facilities by entering various check boxes that are supplied
e.g
swimming pool
Horse riding
sauna
etc....
We'll try and cut down the database so that I can explain the problem with simpler examples.
We pretend we have a Hotel table that contains the following fields
hotel name
hotel grade
hotel number
As we mentioned earlier, a hotel can have many facilities. We have 2 tables
a cross reference or connecting table
and a facilities table
the cross reference table has the following fields
hotel number
facility number
the above table will allow many facilities to be connected to a hotel
the facilities table has the following fields
facility number
facility name
now we have desrcibed how they can try and search for a hotel
I tried to implement this in an ASP page using VB script and I was unsuccessful.
I ran into problems when I tried to add the functionality to allow the hotel facility search along with the hotel name, grade etc..
I attempted it in various was and al were unsuccessful. I tried to open 2 different record sets and it does'nt seem to work. I am using an access database and PWS for win95.
Here is a copy of the APS code as it currently stands.
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Da Search Results!</title>
</head>
<body>
<%
Dim SqlJunk
Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.Open("general")
marketgrp = Request.Form("MarketingGroup")
HotelName = Request.Form("HotelName")
Location = Request.Form("Location")
Price = Request.Form("Price1")
response.write Price & "<br>"
response.write Location & "<br>"
response.write marketgrp & "<br>"
response.write HotelName & "<br>"
SqlJunk = "SELECT * FROM tblGroup, tblRegion, tblhotel" &_
" WHERE tblhotel.GroupNo = tblGroup.GroupNo" &_
" AND tblhotel.RegionNo = tblRegion.RegionNo"
if request.Form("Price1") = "V1" then
SqlJunk = SqlJunk & " and tblhotel.LowPrice <= 40"
else if request.form("Price1") = "V2" then
SqlJunk = SqlJunk & " and tblhotel.LowPrice >= 40 and tblhotel.LowPrice <= 80"
else if request.form("Price1") = "V3" then
SqlJunk = SqlJunk & " and tblhotel.LowPrice >= 80 and tblhotel.LowPrice <= 120"
else if request.form("Price1") = "V4" then
SqlJunk = SqlJunk & " and tblhotel.LowPrice > 120"
End If
End If
End If
End If
Grade = request.Form("Grade1")
response.write Grade & "<br>"
if Grade = 3 or Grade = 4 or Grade = 5 then
SqlJunk = SqlJunk & " and tblhotel.HotelGrade = " &_
Grade & ""
End If
if request.Form("MarketingGroup") <> "Not Selected" then
SqlJunk = SqlJunk & " and tblGroup.HotelGroupName like '%" &_
marketgrp & "%'"
End If
if request.Form("HotelName") <> "Not Selected" then
SqlJunk = SqlJunk & " and tblhotel.HotelName like '%" &_
HotelName & "%'"
End If
if request.Form("Location") <> "Not Selected" then
SqlJunk = SqlJunk & " and tblRegi&n.RegionName like '%" &_
Location & "%'"
End If
Sqlflag = "OFF"
activity = Request.Form("HotelFacility1")
response.write activity & "<br>"
test1 = "before "
response.write test1 & "<br>"
hotel = "H"
Sqlact = "SELECT * FROM tblActivityXRef, tblActivityDesc" &_
" WHERE tblActivityXRef.ActivityNo = tblActivityDesc.ActivityNo" &_
" and tblActivityXRef.ConnectID = '" &_
Hotel & "'"
if request.Form("HotelFacility1") <> "" then
test1 = "dfgdfgd"
if Sqlflag = "OFF" then
Sqlact = Sqlact & " and tblActivityDesc.ActivityName = '" &_
request.form("HotelFacility1") & "'"
Sqlflag = "ON"
else
Sqlact = Sqlact & " or tblActivityDesc.ActivityName = " &_
request.form("HotelFacility1") & ""
End If
End If
response.write test1 & "<br>"
response.write Sqljunk & "<br>"
response.write Sqlact & "<br>"
%>
<%
If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>
<h2 align="center">We did not find a match!</h2>
<%Else%>
<%If Not rsGlobalWeb.BOF Then%>
<h2>Here are the results of your search:</h2>
<table BORDER="0" width="100%" cellpadding="3">
<tr>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Hotel Name </font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Hotel Web Address </font></th>
</tr>
<%
Do While Not rsGlobalWeb.EOF
%>
<tr>
<td><%=rsGlobalWeb("HotelGroupName")%> </td>
<td><%=rsGlobalWeb("HotelWebAddress")%> </td>
<% Set rsGlobalWb2 = Server.CreateObject("ADODB.Recordset")
rsGlobalWb2.Open Sqlact, dbGlobalWeb, 3
rsGlobalWb2.MoveNext
rsGlobalWb2.Close %>
</tr>
<% rsGlobalWeb.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>
</body>
</html>
|
Previous Message
Follow Up - Re: Access database selection problem - Harlan 3/10/1999 9:48:48 AM
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
