Professional Web Applications Themes

building dynamic SQL string from multiple search values - ASP Database

Ok I have to create on the fly a dynamic search query when the user completes a form, this could have from 1 up to about 15 search values. Is there any pages or sites that recommend a good way to do it. currently i do it the following way 'set aWhere to false so we know when to add the and into the SQL statement aWhere = "False" 'builds the SQL string strSQL = "SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE, STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE " IF txtComp <> "" then strSQL = strSQL & "(CMPNY_NAME like '" & ...

  1. #1

    Default building dynamic SQL string from multiple search values

    Ok I have to create on the fly a dynamic search query when the user
    completes a form, this could have from 1 up to about 15 search values. Is
    there any pages or sites that recommend a good way to do it. currently i do
    it the following way

    'set aWhere to false so we know when to add the and into the SQL statement
    aWhere = "False"

    'builds the SQL string

    strSQL = "SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,
    STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE "


    IF txtComp <> "" then
    strSQL = strSQL & "(CMPNY_NAME like '" & txtComp & "%' OR PREV_CMPNY_NAME
    like '" & txtComp & "%') "
    aWhere = "True"
    END IF

    IF CoNo <> "" then
    IF aWhere = "True" then
    strSQL = strSQL & "AND (CMPNY_NO like '" & CoNo & "%') "
    ELSE
    strSQL = strSQL & "(CMPNY_NO like '" & CoNo & "%') "
    aWhere = "True"
    END IF
    END IF

    IF regPcode <> "" THEN
    IF aWhere = "True" then
    strSQL = strSQL & "AND (REG_POSTCODE like '" & regPcode & "%') "
    ELSE
    strSQL = strSQL & "(REG_POSTCODE like '" & regPcode & "%') "
    aWhere = "True"
    END IF
    END IF

    IF tradPcode <> "" then
    IF aWhere = "True" then
    strSQL = strSQL & "AND (TRAD_POSTCODE like '" & tradPcode & "%') "
    ELSE
    strSQL = strSQL & "(TRAD_POSTCODE like '" & tradPcode & "%') "
    aWhere = "True"
    END IF
    END IF

    IF telNo <> "" then
    IF aWhere = "True" then
    strSQL = strSQL & "AND (TELEPHONE like '" & telNo & "%') "
    ELSE
    strSQL = strSQL & "(TELEPHONE like '" & telNo & "%') "
    aWhere = "True"
    END If
    END IF

    'response.write strSQL & "<br><br>"
    'response.end

    set RS = conn.execute(strSQL)

    Only problem is I don't think this is the most efficient way of doing it. I
    have been commissioned to re-write the program (currently written in C++)
    into ASP, there are several pages that build a SQL string from the form
    inputs. The database is SQL server 2000, can anyone suggest a better way or
    point me in the right direction.

    thanks in advance for any help


    Steven Scaife Guest

  2. #2

    Default Re: building dynamic SQL string from multiple search values

    Steven Scaife wrote:
    > Ok I have to create on the fly a dynamic search query when the user
    > completes a form, this could have from 1 up to about 15 search
    > values. Is there any pages or sites that recommend a good way to do
    > it. currently i do it the following way
    >
    > 'set aWhere to false so we know when to add the and into the SQL
    > statement aWhere = "False"
    >
    > 'builds the SQL string
    >
    > strSQL = "SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,
    Stop right there. If performance is your goal, then you cannot contemplate
    using dynamic sql. Pass the values to a stored procedure and process them
    there. Here is some information about doing dynamic search conditions:
    [url]http://www.sommarskog.se/[/url]

    Just click the "Dynamic Search Conditions" link.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

Similar Threads

  1. One Search String/Multiple Fields
    By KYMatt in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 20th, 07:16 PM
  2. Search on Multiple Values
    By Rsootarsing in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 5th, 09:38 PM
  3. ANN: InterAKT Site Search - search in multiple tables
    By Alexandro Colorado in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 29th, 04:23 PM
  4. Replies: 4
    Last Post: August 14th, 06:04 AM
  5. better way of building string from hash
    By mike solomon in forum PERL Miscellaneous
    Replies: 4
    Last Post: July 17th, 06:15 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139