Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Question Unanswered: ASP Search Syntax Question

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


  2. #2
    Join Date
    Jul 2003
    Penang, Malaysia
    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.

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

    -- 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
    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.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Oct 2003
    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


  4. #4
    Join Date
    Jul 2003
    Penang, Malaysia
    err...cyrus, haven't I've given a rough example on what u'r saying already?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts