Professional Web Applications Themes

Anyone know whats wrong with this? - ASP Database

hi all, I have a form with a text box and a checkbox. If the text box is empty the page retreives records based on this text and if the check box is checked it does the same. But im having trouble retreiveing records based on the text and the checkbox at the same time. PLease see my code below. Am i contructing the code right? If strSearch <= "" and Request.QueryString("Upcoming") = "on" Then strSQL = "SELECT * FROM tblTours WHERE fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC" objRS.Open strSQL, objConn, 3, 1, 1 Else If ...

  1. #1

    Default Anyone know whats wrong with this?

    hi all,

    I have a form with a text box and a checkbox. If the text box is empty the
    page retreives records based on this text and if the check box is checked it
    does the same. But im having trouble retreiveing records based on the text
    and the checkbox at the same time. PLease see my code below. Am i
    contructing the code right?


    If strSearch <= "" and Request.QueryString("Upcoming") = "on" Then
    strSQL = "SELECT * FROM tblTours WHERE fldUpcoming = 1 ORDER BY
    fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
    objRS.Open strSQL, objConn, 3, 1, 1
    Else
    If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
    strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch & "%'
    OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch &
    "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
    fldGigDateMonth, fldVenue ASC"
    'Response.Write strSQL
    objRS.Open strSQL, objConn, 3, 1, 1
    Else
    strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
    "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch
    & "%' ORDER BY fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
    objRS.Open strSQL, objConn, 3, 1, 1
    End If
    End If



    Mike M Guest

  2. #2

    Default Re: Anyone know whats wrong with this?

    Are you having a problem with your SQL string, or is it the programme flow
    you are having a problem with? For what it's worth I'd try

    if strSearch = "" and Request.QueryString("Upcomping") <> "" then
    ' search is empty, upcoming is checked
    elseif strSearch <> "" and Request.QueryString("Upcomping") <> "" then
    ' search has data, upcoming is checked
    else
    ' search might have data, it might be empty but upcoming is unchecked
    end if

    Note that your three conditions might not be exactly what you intended.

    "Mike M" <mikemack1.karoo.co.uk> wrote in message
    news:D5ycnc8C7MYVObLdSa8jmAkaroo.co.uk...
    > hi all,
    >
    > I have a form with a text box and a checkbox. If the text box is empty the
    > page retreives records based on this text and if the check box is checked
    it
    > does the same. But im having trouble retreiveing records based on the text
    > and the checkbox at the same time. PLease see my code below. Am i
    > contructing the code right?
    >
    >
    > If strSearch <= "" and Request.QueryString("Upcoming") = "on" Then
    > strSQL = "SELECT * FROM tblTours WHERE fldUpcoming = 1 ORDER BY
    > fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
    > objRS.Open strSQL, objConn, 3, 1, 1
    > Else
    > If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
    > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
    "%'
    > OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch &
    > "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
    > fldGigDateMonth, fldVenue ASC"
    > 'Response.Write strSQL
    > objRS.Open strSQL, objConn, 3, 1, 1
    > Else
    > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
    > "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &
    strSearch
    > & "%' ORDER BY fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue
    ASC"
    > objRS.Open strSQL, objConn, 3, 1, 1
    > End If
    > End If
    >
    >
    >

    Adrian Forbes [ASP MVP] Guest

  3. #3

    Default Re: Anyone know whats wrong with this?


    "Adrian Forbes [ASP MVP]" <sorrynoemail.zzz> wrote in message
    news:e5t#nPO9DHA.2308TK2MSFTNGP11.phx.gbl...
    > Are you having a problem with your SQL string, or is it the programme flow
    > you are having a problem with? For what it's worth I'd try
    >
    > if strSearch = "" and Request.QueryString("Upcomping") <> "" then
    > ' search is empty, upcoming is checked
    > elseif strSearch <> "" and Request.QueryString("Upcomping") <> "" then
    > ' search has data, upcoming is checked
    > else
    > ' search might have data, it might be empty but upcoming is unchecked
    > end if
    >
    > Note that your three conditions might not be exactly what you intended.
    >
    > "Mike M" <mikemack1.karoo.co.uk> wrote in message
    > news:D5ycnc8C7MYVObLdSa8jmAkaroo.co.uk...
    > > hi all,
    > >
    > > I have a form with a text box and a checkbox. If the text box is empty
    the
    > > page retreives records based on this text and if the check box is
    checked
    > it
    > > does the same. But im having trouble retreiveing records based on the
    text
    > > and the checkbox at the same time. PLease see my code below. Am i
    > > contructing the code right?
    > >
    > >
    > > If strSearch <= "" and Request.QueryString("Upcoming") = "on" Then
    > > strSQL = "SELECT * FROM tblTours WHERE fldUpcoming = 1 ORDER BY
    > > fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
    > > objRS.Open strSQL, objConn, 3, 1, 1
    > > Else
    > > If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
    > > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
    > "%'
    > > OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch
    &
    > > "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
    > > fldGigDateMonth, fldVenue ASC"
    > > 'Response.Write strSQL
    > > objRS.Open strSQL, objConn, 3, 1, 1
    > > Else
    > > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch
    &
    > > "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &
    > strSearch
    > > & "%' ORDER BY fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue
    > ASC"
    > > objRS.Open strSQL, objConn, 3, 1, 1
    > > End If
    > > End If
    > >
    > >
    > >
    >
    >
    Hi,

    The section im having trouble with is the :

    If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
    strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
    "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch
    & "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
    fldGigDateMonth, fldVenue ASC"

    It works fine in showing any record that contains data that i entered to
    search but not the ones that are marked as upcoming.

    such as If i enter aldershot without the upcoming check box ticked i return
    4 records but if i just tick upcoming and do not type any text i get back 3
    records (which is correct) but in theory i should retreive 3 records if i
    type Aldershot and tick the box? I'll try your changes tomorow, right now i
    need sleep. Thanks

    Mike



    Mike M Guest

  4. #4

    Default Re: Anyone know whats wrong with this?

    > If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
    > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
    > "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &
    strSearch
    > & "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
    > fldGigDateMonth, fldVenue ASC"
    >
    > It works fine in showing any record that contains data that i entered to
    > search but not the ones that are marked as upcoming.
    >
    > such as If i enter aldershot without the upcoming check box ticked i
    return
    > 4 records but if i just tick upcoming and do not type any text i get back
    3
    > records (which is correct) but in theory i should retreive 3 records if i
    > type Aldershot and tick the box? I'll try your changes tomorow, right now
    i
    > need sleep. Thanks
    Maybe it is clever enough to know that Aldershot can't possibly be upcoming
    in anything :)

    Try and group your OR statements as one unit and your AND as seperate

    WHERE (fldVenue LIKE... OR fldTown LIKE...OR fldTour LIKE ...) AND
    fldUpcoming = 1

    BTW is dlfUpcoming a bit or yes/no? If so you should use "= 0" for false
    and "<> 0" for true. Access treats a bit as 0/1 and SQL Server 0/-1


    Adrian Forbes [ASP MVP] Guest

  5. #5

    Default Re: Anyone know whats wrong with this?


    "Adrian Forbes [ASP MVP]" <sorrynoemail.zzz> wrote in message
    news:uc#NarO9DHA.2472TK2MSFTNGP10.phx.gbl...
    > > If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
    > > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
    > > "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &
    > strSearch
    > > & "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
    > > fldGigDateMonth, fldVenue ASC"
    > >
    > > It works fine in showing any record that contains data that i entered to
    > > search but not the ones that are marked as upcoming.
    > >
    > > such as If i enter aldershot without the upcoming check box ticked i
    > return
    > > 4 records but if i just tick upcoming and do not type any text i get
    back
    > 3
    > > records (which is correct) but in theory i should retreive 3 records if
    i
    > > type Aldershot and tick the box? I'll try your changes tomorow, right
    now
    > i
    > > need sleep. Thanks
    >
    > Maybe it is clever enough to know that Aldershot can't possibly be
    upcoming
    > in anything :)
    >
    > Try and group your OR statements as one unit and your AND as seperate
    >
    > WHERE (fldVenue LIKE... OR fldTown LIKE...OR fldTour LIKE ...) AND
    > fldUpcoming = 1
    >
    > BTW is dlfUpcoming a bit or yes/no? If so you should use "= 0" for false
    > and "<> 0" for true. Access treats a bit as 0/1 and SQL Server 0/-1
    >
    >
    Its a number field with a Field size of "long integer" Im using An access
    database. Not upgraded to SQWL yet as i may me buying some hosting


    Mike M Guest

  6. #6

    Default Re: Anyone know whats wrong with this?

    > > Try and group your OR statements as one unit and your AND as seperate
    > >
    > > WHERE (fldVenue LIKE... OR fldTown LIKE...OR fldTour LIKE ...) AND
    > > fldUpcoming = 1
    > >
    > > BTW is dlfUpcoming a bit or yes/no? If so you should use "= 0" for
    false
    > > and "<> 0" for true. Access treats a bit as 0/1 and SQL Server 0/-1
    > >
    > >
    > Its a number field with a Field size of "long integer" Im using An access
    > database. Not upgraded to SQWL yet as i may me buying some hosting
    Have you tried grouping the ORs in their own block yet?


    Adrian Forbes [ASP MVP] Guest

Similar Threads

  1. hmm whats wrong ??
    By mak999 in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: February 16th, 10:05 PM
  2. whats wrong with my sql?
    By loofa in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 25th, 08:47 AM
  3. [PHP] Whats wrong with my code?
    By Brad Pauly in forum PHP Development
    Replies: 9
    Last Post: September 20th, 10:42 PM
  4. [PHP] Whats wrong?
    By Ryan A in forum PHP Development
    Replies: 0
    Last Post: September 13th, 01:07 PM
  5. whats wrong with this querry
    By Sundial Services in forum PHP Development
    Replies: 1
    Last Post: August 31st, 12:55 AM

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