Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 5th, 2008, 09:30 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2008
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Select Help Please

Hi
I'm struggling with this sql statement and hope someone can help me out.
Im running dw 8 in Classic ASP and Access DB

I have a search page and a results page.
My search page has several dropdown menus that pass values to my results
page.

I want my results page to display results for ANY of the values passed from
my search page and not depend on all values.
So if a visitor only makes selections from two or three dropdown options, i
get results based on those selections.

At present im thinking that my results page needs all values as i use the
AND in my select statement. Is this correct? if so how do i achieve my
goal??

Thank You
Andy

I have my results page sql select code below:

<%
Dim RSResults__MMColParam2
RSResults__MMColParam2 = "0"
If (Request("location") <> "") Then
  RSResults__MMColParam2 = Request("location")
End If
%>
<%
Dim RSResults__MMColParam3
RSResults__MMColParam3 = "0"
If (Request("Instrument") <> "") Then
  RSResults__MMColParam3 = Request("Instrument")
End If
%>
<%
Dim RSResults__MMColParam4
RSResults__MMColParam4 = "0"
If (Request("Person") <> "") Then
  RSResults__MMColParam4 = Request("Person")
End If
%>
<%
Dim RSResults__MMColParam5
RSResults__MMColParam5 = "0"
If (Request("AssetID") <> "") Then
  RSResults__MMColParam5 = Request("AssetID")
End If
%>
<%
Dim RSResults__MMColParam6
RSResults__MMColParam6 = "0"
If (Request("Description") <> "") Then
  RSResults__MMColParam6 = Request("Description")
End If
%>
<%
Dim RSResults__MMColParam7
RSResults__MMColParam7 = "0"
If (Request("TestStatus") <> "") Then
  RSResults__MMColParam7 = Request("TestStatus")
End If
%>
<%
Dim RSResults__MMColParam8
RSResults__MMColParam8 = "0"
If (Request("TestType") <> "") Then
  RSResults__MMColParam8 = Request("TestType")
End If
%>
<%
Dim RSResults__MMColParam9
RSResults__MMColParam9 = "0"
If (Request("FromDate") <> "") Then
  RSResults__MMColParam9 = Request("FromDate")
End If
%>
<%
Dim RSResults__MMColParam10
RSResults__MMColParam10 = "0"
If (Request("ToDate") <> "") Then
  RSResults__MMColParam10 = Request("ToDate")
End If
%>
<%
Dim RSResults
Dim RSResults_numRows

Set RSResults = Server.CreateObject("ADODB.Recordset")
RSResults.ActiveConnection = MM_calcert_STRING
RSResults.Source = "SELECT DISTINCT Assets.[Asset ID], Assets.Description,
Locations.[Location Name], [Asset Tests].[Test Date], [Asset Tests].[Test
Instrument Serial No], [Asset Tests].User, [Asset Test Records].[Test Type],
[Asset Test Records].Value, [Asset Test Records].Unit, [Asset Test
Records].[Passed Test] FROM Locations INNER JOIN ((Assets INNER JOIN [Asset
Tests] ON Assets.[Asset Key] = [Asset Tests].[Asset ID]) INNER JOIN [Asset
Test Records] ON [Asset Tests].[Test ID] = [Asset Test Records].[Test ID])
ON Locations.ID = Assets.Location WHERE Locations.[Location Name] = '" +
Replace(RSResults__MMColParam2, "'", "''") + "' AND [Asset Tests].[Test
Instrument Serial No] = '" + Replace(RSResults__MMColParam3, "'", "''") + "'
AND [Asset Tests].User = '" + Replace(RSResults__MMColParam4, "'", "''") +
"' AND Assets.[Asset ID] = '" + Replace(RSResults__MMColParam5, "'", "''")
+ "' AND Assets.Description = " + Replace(RSResults__MMColParam6, "'",
"''") + " AND [Asset Test Records].[Passed Test] = '" +
Replace(RSResults__MMColParam7, "'", "''") + "' AND [Asset Test
Records].[Test Type] = " + Replace(RSResults__MMColParam8, "'", "''") + "
AND [Asset Tests].[Test Date] BETWEEN '" + Replace(RSResults__MMColParam9,
"'", "''") + "' AND '" + Replace(RSResults__MMColParam10, "'", "''") + "'
ORDER BY Assets.[Asset ID] ASC"
RSResults.CursorType = 0
RSResults.CursorLocation = 2
RSResults.LockType = 1
RSResults.Open()

RSResults_numRows = 0
%>


Reply With Quote
  #2 (permalink)  
Old August 5th, 2008, 12:55 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Change your ANDs to ORs.

AND means "must match all conditions".

OR means "must match any one condition".
Reply With Quote
  #3 (permalink)  
Old August 5th, 2008, 07:59 PM
Authorized User
 
Join Date: Jul 2008
Location: Brisbane, Queensland, Australia.
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to ramone_johnny
Default

God thats horrible code!!

www.webdesigndocuments.com
Reply With Quote
  #4 (permalink)  
Old August 5th, 2008, 09:47 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
quote:Originally posted by ramone_johnny
 God thats horrible code!!
LOL! Well, it's Drugged'n'Wasted code, so what do you expect?

It's actually better than average DumbWhacker code, I think.
Reply With Quote
  #5 (permalink)  
Old August 6th, 2008, 01:10 AM
Authorized User
 
Join Date: Jul 2008
Location: Brisbane, Queensland, Australia.
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to ramone_johnny
Default

Quote:
quote:Originally posted by Old Pedant
 
Quote:
quote:Originally posted by ramone_johnny
Quote:
 God thats horrible code!!
LOL! Well, it's Drugged'n'Wasted code, so what do you expect?

It's actually better than average DumbWhacker code, I think.

Nope. Its DumbWhacker code Bill. I know because I started in Dreamweaver drag and drop crap.

Yuck!

John

www.webdesigndocuments.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using COLLATE in SQL SELECT Statements sudhakaratp SQL Server 2000 8 September 6th, 2018 01:16 PM
Select Sql Query kk_kumar99 SQL Server 2000 5 May 18th, 2007 03:36 PM
SQL select statement problem shrisangeeta Classic ASP Databases 3 June 15th, 2006 10:28 AM
VB.net and SQL select skicrud VB.NET 1 November 4th, 2004 07:02 AM
SQL SELECT & aliases U.N.C.L.E. SQL Language 6 October 11th, 2003 12:59 PM



All times are GMT -4. The time now is 05:09 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.