ASP Search Syntax Question

seberts · #1 (**permalink**) 10-02-03, 15:59

What I'm trying to do is have the search results show only records where dname = "LB Smith" but also give the user to input values for the other 2 variables. If nothing is found with the user variables it should show all values for "LB Smith".

The idea here is when a LB Smith user is using the database they can only see their records. Here's what I've used for my search critira:

select * from warrantyclaims where dname = '("LB_Smith"))' and attachserial like '%{Request.QueryString("keyword")}%' or cname like '%{Request.QueryString("keyword")}%'

NOTE: Keyword is the text box they enter the search term into. It works fine if I don't declare LB Smith in the code but that's not what I need ....

THANKS IN ADVANCE!!!

Patrick Chua · #2 (**permalink**) 10-07-03, 08:49

Do you mean when the user does not key in the 2 optional search criteria, u want to show all records cocerning dname="LB Smith" ?

If what I understand correctly,
first I need to know what database are u using? Is it just Access or MS SQL Server?

I'm not sure if Access SQL syantax supports "CASE ...WHEN..ELSE".
But MS SQL Server does. I'll give u a sample of how it can be done with only SQL Server syantax and how to go around it if u'r using Access.
Please note that I'm just writing on dry run, no testing of my code is done.

Code:

-- MS SQL Server Syantax

declare @attachserial as varchar(50),
            @cname as varchar(50),

set @attachserial='"& Request.QueryString("keyword") &"'
set @cname='"&Request.QueryString("keyword")&"'

select * from warrantyclaims where dname = '("LB_Smith"))' and
case when @attachserial='' then '' else attachserial end like '%'+@attachserial+'%'
and case when @cname='' then '' else cname end like '%'+@cname+'%' 

-- here, the syantax uses 'case' syantax to determine if the variable  
--  is empty or not.

OR

Code:

-- If it is done via other DB like Access ( not sure if it supports the case --syantax anyway)

-- You assign your value to a ASP variable first and then do checking 
-- and see if you need the additional "and statments for u'r SQL Syantax


Rough example
Dim AttachSerial,CName

AttachSerial=Request.QueryString("keyword")
CName=Request.QueryString("keyword")



SQL_Query="select * from warrantyclaims where dname = '("LB_Smith"))'"
if  AttachSerial <>"" then

SQL_Query=SQL_Query + "and attachserial like  "& AttachSerial &"
elseif CName <>"" then
SQL_Query=SQL_Query + "and Cname like  "& CName &"
end if


-- I can't remember if the operator should be "+" or "&&" , you have to check .

All the above are just a rough concept. Hope it helps.

cyrus · #3 (**permalink**) 10-09-03, 00:24

Hello,
If ur are doing it in asp then u can do this by using IF else condition
depending on the condition u can get the resultant sql query and then execute it.

Now it depends what u want to build a query.
so u should be very clear when u r dynamically building a query depending on user input

cyrus

Patrick Chua · #4 (**permalink**) 10-09-03, 04:48

err...cyrus, haven't I've given a rough example on what u'r saying already?

Internet Services

Web Development

Digital Marketing

Consumer Tech