SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: ASP search page

  1. #1
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a simple little database with information about where to buy our magazines.

    I made a form so users can select "country" and/or "state".

    [This message has been edited by djh (edited July 13, 2000).]

  2. #2
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You need to initialize the variable "Newstands" because currently in your code, your not doing that. This sends a Null for the table name so your Select really reads:

    Select * from Null

    Which is well you guessed it, Null. This is why your result set is empty. If your table name is Newstands then put that in your query string and drop the &. So now your SELECT would read:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    sql = "select * from newstands"
    [/code]
    Your code for determining country could read:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    WhereSQL = WhereSQL & " AND (Country=" & trim(strCountry) &")"
    [/code]
    Also for your states why not simply put:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    WhereSQL = WhereSQL & " and (State=" & trim(strState) & ")"
    [/code]
    Those two lines would eliminate a lot of if/else statements and shrink your code greatly. I put the trim around the variables because I think it is a very good idea to get rid of potential white space in my query strings otherwise you'll need to worry about using LIKE instead of '='.

    The whole IF statement where your finish building your SQL statement is really unnecessary. Change your original SQL statement to read:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    WhereSQL = "select * from &lt;&lt;tablename&gt;&gt; where"
    [/code]

    Then it is already built and your saving system resources by cutting out one more variant.

    Now the whole:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    If WhereSQL &lt;&gt; "" then
    ' Not exactly sure why your doing this
    WhereSQL = Right(WhereSQL, len(WhereSQL)-4)
    SQL = SQL & " WHERE" & WhereSQL
    end if
    [/code]
    can be deleted.

    The following line would have to be changed from:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    rsSearch.Open sql, conn, 3, 3
    [/code]
    to:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    rsSearch.Open WhereSQL, conn, 3, 3
    [/code]

    I hope this helps.

    ------------------
    Wayne Luke - Sitepoint Forums Administrator
    Digital Magician Studios - Making Magic with Web Applications
    sitepoint@digitalmagician.com

    [This message has been edited by wluke (edited July 08, 2000).]

  3. #3
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know ya'll must be pretty fed up with my lack of coding skills...but I at least feel like I'm getting better!

    I'm using the below code - MUCH CLEANER thank heavens to WLUKE - but I get the strange feeling that I'm not getting the desired recordset. When the search returns, every record ends up being shown instead of just what was submitted by form.

    Now I know I've just failed to do something properly...

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>

    Set objRec = Server.CreateObject("ADODB.Connection")
    conString = "DBQ=" & Server.MapPath("@@@@@.mdb")

    objRec.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & conString

    Set rsRec = Server.CreateObject("ADODB.Recordset")

    sql = "select * from newsstands"
    WhereSQL = WhereSQL & " AND (Country=" & trim(strCountry) &")"
    WhereSQL = WhereSQL & " AND (State=" & trim(strState) &")"

    rsRec.Open SQL, objRec, 3, 3
    [/code]

    Now, this is only a snippet. My guess is that my rsRec doesn't include the SQL conditional statements. But if I try "WhereSQL" instead of "SQL", for the line "rsRec.Open SQL, objRec....." it returns an Invalid SQL statement error!!!

    What's wrong with this recordset?

    Thanks!

    Dave Han - webmaster@goboatingamerica.com
    - Oh we just so love coding...
    Go Boating America.com - Official Home of Sea and Go Boating Magazines.
    Everything related to boating...and then some. Visit goboatingamerica.com

    [This message has been edited by djh (edited July 13, 2000).]

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Your code should look something like this:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    strSql = "SELECT * FROM Newstands"
    strSql = strSql & " WHERE Country='" & trim(strCountry) & "'"
    strSql = strSql & " AND State="'" & trim(strState) & "'"
    rsRec.Open strSql, objRec, 3, 3
    [/code]

    The reason you were getting a syntax error is because the WhereSql was equal to "AND (COUNTRY = trim(strCountry)) AND State= trim(strCountry))". There was no select statement because you have the select going to SQL, not to WhereSQL. If you use the statement I just placed above, it should get you the results you want...



    [This message has been edited by DaveMaxwell (edited July 14, 2000).]

  5. #5
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I am sorry.. I thought I made it clearer that you should use WhereSQL throughout the entire query building process.

    ------------------
    Wayne Luke - Sitepoint Forums Administrator
    Digital Magician Magazine - MetaQuark Creations (Coming Soon)
    sitepoint@digitalmagician.com

  6. #6
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, after all your help, it finally works!!! (Well, for the most part anyway...read more below).

    After doing some crash SQL syntax research, and all your suggestions, the culprit was that in the previous snippet of code, the conditional statement was not separated correctly by double quotes. It read:

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>strSql = strSql & " WHERE Country='" & trim(strCountry) & "'"
    strSql = strSql & " AND State="'" & trim(strState) & "'"
    [/code]

    but it should, instead have read:

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    strSql = strSql & " WHERE Country='" & trim(strCountry) & "'"
    strSql = strSql & " AND " & "State='" & trim(strState) & "'"
    [/code]

    It works beautifully now! Check it out - I'm not sure if I'm allowed to do this but...- http://goboatingamerica.com/news.asp .

    Here is the only problem - although I must admit I am quite relieved that it can do this much. I changed the AND statement to an OR statement because it ended up that I wanted to have more search fields. Now, the code is:

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    strSql = "SELECT * FROM Newsstands"
    strSql = strSql & " WHERE Country='" & trim(strCountry) & "'"
    strSql = strSql & " OR " & "State='" & trim(strState) & "'"
    strSql = strSql & " OR " & "Mag='" & trim(strMag) & "'"
    strSql = strSql & " OR " & "City='" & trim(strCity) & "'"
    strSql = strSql & " OR " & "Zip='" & trim(strZip) & "'"
    [/code]

    But the thing about this, as I'm sure all you experts can see, is that when I perform a search for let's say, US and Irvine (city) and 12345 (zip), it returns all three not the logically restricted recordset that one is looking for. Might this call for IF statements?

    Cheers!




    ------------------
    Dave Han - webmaster@goboatingamerica.com
    - Oh we just so love coding...
    Go Boating America.com - Official Home of Sea and Go Boating Magazines.
    Everything related to boating...and then some. Visit goboatingamerica.com

  7. #7
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you used the AND keyword it will restrict your results better. But you have to account for empty strings using IF statements in your VB code.

    example:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    IF trim(len(strCity)) &gt; 0 then
    strSql = strSql & " AND WHERE city ='" & trim(strCity) & "'"
    End IF
    [/code]

    I would also do a Replace on your fields before you stick them into the query statement to eliminate any possible apostrophes in them. Apostrophes will cause your SQL to blow up on you.

    Like this:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    IF trim(len(strCity)) &gt; 0 then
    strSql = strSql & " AND WHERE city ='" & replace(trim(strCity),"'","''") & "'"
    End IF
    [/code]

    One other change I would make is use LIKE instead of "=" in the query building.

    People can be terrible at spelling and forcing an exact match can really limit your results to nothing when there really is a good match. If your using SQL Server instead of Access you can SOUNDEX your queries for even better results and rank them by using MS-SQL's DIFFERENCE functions. Soundex and Difference comparisons can be used in ACCESS and VBscript as well, you just have to build the functions.

    Further optimization can be achieved by using the Form collection of the Request Object if you use the POST method on your form.

    The code for all your form elements could be reduced to:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    For each key in Request.Form
    If trim(len(Request.Form(key))) &gt; 0 then
    strSql = strSql & " AND WHERE " & key & _
    "='" & replace(trim(Request.Form(key)),"'","''") & "'"
    End IF
    NEXT
    [/code]

    Then you only put the fields on the form that you want to search on. Though get the first way working first then you can optimize it at your leisure.


    ------------------
    Wayne Luke - Sitepoint Forums Administrator
    Digital Magician Magazine - MetaQuark Creations (Coming Soon)
    sitepoint@digitalmagician.com



    [This message has been edited by wluke (edited July 14, 2000).]


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
  •