| | |
| |
Active Server Pages asp search engine active server page asp application components tutorial CJWSoft ASPProtect ASPBanner ASPClassifieds
(Advanced)
(Components)
(Database)
(General)
(Vbscript)
|
|
|
|
Subject: |
Re: Access database selection problem |
|
From: |
Harlan |
|
Date: |
3/10/1999 9:48:48 AM |
|
IP: |
206.222.20.2 |
there is a lot in here ;) But its better to give to much info then not enough I guess. Because I don't know your database structure, or your app, and you didn't post any error messages. Its hard to know what exactly is wrong. I'm going to repost your code and make comments on it. I'm really really picky about my own code, so some of the comments may just be personal prefferences. :)
Hope some of it helps though.
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Da Search Results!</title>
</head>
<body>
<%
Dim SqlJunk
Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.Open("general")
'try using .Open("DSN=General"), your way
'may work, I'm not sure
'oh and also, I noticed you don't have any
'comments in your code at all.. you should
'probably do that :)
marketgrp = Request.Form("MarketingGroup")
HotelName = Request.Form("HotelName")
Location = Request.Form("Location")
Price = Request.Form("Price1")
'I usually post all my request.form variables
'at the top for easy referencing.
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"
'its much faster to select only the columns
'you need then *, even if you need all the
'columns in a database, its much faster to
'list them all out cmnOne,cmnTwo then using *
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
'ok, bunch of stuff here.. you should probably
'use a select case, and you have overlapping
'values. Even if you use if/elseif you only
'need one end if for each if, not elseif
'example
'if blah = blahblah then
'do this
'elseif blah = blahblahblah then
'do this
'elseif blah = blabamOwwey
'do this
'else
'do this
'end if
'but I believe a select case is better for here.
'Price1 = request.Form("Price1")
'select case Price1
'case 1 to 39
'do this
'case 40 to 79
'do this
'case else
'do this
'end select
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
'I would use a select case here too..
'but now that I'm here.. you are building your
'sql statement, and it seems the hard way to
'do it.. it will probably be easier on you to
'debug if you can group your functionality
'together.. here is what I would suggest
'first check what variables you got back
'make sure you have any requried. Then
'create a boolean flag and run threw your
'variables and build your statement. Something
'like this...
'example code...
'variables
strVar1 'passed from user
strVar2 'passed from user
sqlCMNs 'list of columns needed for the db
sqlSelect 'dynamic select statement
'set variables
If Request.Form("strVar1") Then
strVar1 = Request.Form("strVar1")
End If
If Request.Form("strVar2") Then
strVar2 = Request.Form("strVar2")
End If
'just run threw and build your list..
'I would suggest using coding standards as well.
'str before strings, int before intergers
'rs before recordsets, ect.. even though in
'script they are variants, its nice to know what
'they should be.
flgAnd = False
'flgAnd is the flag I'll use to build the
'sql statement..
If strVar1 <> "" Then
If Not flgAnd then
sqlString = sqlString & " WHERE cmnOne='" & strVar1 & "' "
sqlCMNs = "o.cmnOne"
flgAnd = True
Else
sqlString = sqlString & " AND cmnTwo='" & strVar1 & "' "
sqlCMNs = sqlCMNs & ", o.cmnOne"
End If
End If
If strVar2 <> "" Then
If Not flgAnd then
sqlString = sqlString & " WHERE cmnTwo='" & strVar2 & "' "
sqlCMNs = "o.cmnTwo"
flgAnd = True
Else
sqlString = sqlString & " AND cmnTwo='" & strVar2 & "' "
sqlCMNs = sqlCMNs & ", o.cmnTwo"
End If
End If
'this way you can add your where.. or and
'depending on if its needed or not.. you can
'also build your list of columns needed as you
'go..
'at the very end, make sure we at least had
'a where clause once..
If Not flgAnd Then
response.write "Error: no items selected.."
response.end 'stop the script from finishing.
End
sqlSelect = "SELECT " & sqlCMNs & _
" FROM one AS o, two AS t, three as h " & _
sqlWhere & " and o.cmnOne=t.cmnOne "
'you can append your required information to
'the end as needed.
'end example code
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
'but back to code.. if you mean to be checking
'for null.. request.form("HotelName") <> "" checks
'that a field is blank.
if request.Form("Location") <> "Not Selected" then
SqlJunk = SqlJunk & " and tblRegi&n.RegionName like '%" &_
Location & "%'"
End If
Sqlflag = "OFF"
'Its easier to use True/False
'sqlFlag = True or sqlFlag = False
'so you can use "If sqlFlag Then" meaning
'if sqlFlag = true then, only shorter ;)
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 & "'"
'I would make this reference in the top query
'its much faster to have one query, then nested
'if its at all possible.
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>"
'you are checking a recordset here that
'it doesn't seem to me you have even run
'yet.. you probably need to do this..
set rsGlobalWeb = dbGlobalWeb.Execute(sqlString)
'in other words, you need to run your query.
'you can also do something like this, for
'testing if you need..
set rsGlobalWeb = dbGlobal.Web.Execute("Select * from tblOne")
'just to test your recordset, or whatever.
If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then
%>
<h2 align="center">We did not find a match!</h2>
<%
Else
'not sure what the if you had here was, I removed
'it.
%>
<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
'i'm really not sure what this stuff is with
'rsGlobalWb2.. but it could be ok.. my statements 'look like the one I showed above. I set 'cursortype and other properties seperate from
'my open statement. But it doesn't seem you
'are looping threw that recordset to me. But
'anyway, I think you can probably get a join
'to give you the info you need from that query
'in your first query.
%>
</tr>
<% rsGlobalWeb.MoveNext
Loop
%>
</table>
<%End If
rsGlobalWeb.Close
dbGlobalWeb.Close
%>
</body>
</html> |
Previous Message
|
|
|
|
|
|
