Professional Web Applications Themes

Asp variables in sql select - ASP Database

Hello, Is such select sql statment allowed? " SELECT * FROM Photo_SubCategory Where (Status = 1 And Session("id") <> "") or (Status = 0 And session("id") <> "" )" Status is a field in the table. Basically I want to selct a record that satisfy when the field Status = 1 and session("id") <> "" Or how do i achieve it ? thanks roger...

  1. #1

    Default Asp variables in sql select

    Hello,

    Is such select sql statment allowed?

    " SELECT * FROM Photo_SubCategory Where (Status = 1 And
    Session("id") <> "") or (Status = 0 And session("id") <> "" )"

    Status is a field in the table.

    Basically I want to selct a record that satisfy when the field Status
    = 1 and session("id") <> ""

    Or how do i achieve it ?

    thanks
    roger
    HelLind Guest

  2. #2

    Default Re: Asp variables in sql select

    Since session("id") does not change per row in your table, I don't
    understand why this has to be part of the SQL Statement at all.

    What are the possible values for status? 0 and 1 only, or are there other
    values as well?

    What do you want to happen when session("id") = "" ?

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)




    "HelLind" <u8712123cc.nctu.edu.tw> wrote in message
    news:9a6fe908.0408061238.7f1d2076posting.google.c om...
    > Hello,
    >
    > Is such select sql statment allowed?
    >
    > " SELECT * FROM Photo_SubCategory Where (Status = 1 And
    > Session("id") <> "") or (Status = 0 And session("id") <> "" )"
    >
    > Status is a field in the table.
    >
    > Basically I want to selct a record that satisfy when the field Status
    > = 1 and session("id") <> ""
    >
    > Or how do i achieve it ?
    >
    > thanks
    > roger

    Aaron [SQL Server MVP] Guest

  3. #3

    Default Re: Asp variables in sql select

    Basically, when a person upload a picture to my site, the status of
    the picture = 0 when it is set to 'show only to members' and status =
    1 when it is to be made public.

    So when another visitor is logged in with a session variable <> "", it
    will display the pictures with status = 0 & 1, orelse it will only
    show pictures with status = 1.

    Thats' why the SQL select will show only pics with (status=1) or
    (status=0 and session("ID") <> "")

    I hope you get what i mean. :-)

    Yes I can achieve this if I loop the whole recordset and validate it
    with an IF statement, but it is tedious, I thought it can be achieved
    with a single SQL statement. Looks tidy and the proper way to code it.

    Thanks.



    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message news:<OIB3nb$eEHA.3928TK2MSFTNGP11.phx.gbl>...
    > Since session("id") does not change per row in your table, I don't
    > understand why this has to be part of the SQL Statement at all.
    >
    > What are the possible values for status? 0 and 1 only, or are there other
    > values as well?
    >
    > What do you want to happen when session("id") = "" ?
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >
    >
    >
    > "HelLind" <u8712123cc.nctu.edu.tw> wrote in message
    > news:9a6fe908.0408061238.7f1d2076posting.google.c om...
    > > Hello,
    > >
    > > Is such select sql statment allowed?
    > >
    > > " SELECT * FROM Photo_SubCategory Where (Status = 1 And
    > > Session("id") <> "") or (Status = 0 And session("id") <> "" )"
    > >
    > > Status is a field in the table.
    > >
    > > Basically I want to selct a record that satisfy when the field Status
    > > = 1 and session("id") <> ""
    > >
    > > Or how do i achieve it ?
    > >
    > > thanks
    > > roger
    HelLind Guest

  4. #4

    Default Re: Asp variables in sql select

    [email]u8712123cc.nctu.edu.tw[/email] (HelLind) wrote in message news:<9a6fe908.0408061238.7f1d2076posting.google. com>...
    > Hello,
    >
    > Is such select sql statment allowed?
    >
    > " SELECT * FROM Photo_SubCategory Where (Status = 1 And
    > Session("id") <> "") or (Status = 0 And session("id") <> "" )"
    >
    > Status is a field in the table.
    >
    > Basically I want to selct a record that satisfy when the field Status
    > = 1 and session("id") <> ""
    >
    > Or how do i achieve it ?
    >
    > thanks
    > roger

    Oh the SQL should be

    " SELECT * FROM Photo_SubCategory Where (Status = 1 And) or (Status
    = 0 And session("id") <> "" )"
    HelLind Guest

  5. #5

    Default Re: Asp variables in sql select

    HelLind wrote:
    > [email]u8712123cc.nctu.edu.tw[/email] (HelLind) wrote in message
    > news:<9a6fe908.0408061238.7f1d2076posting.google. com>...
    >> Hello,
    >>
    >> Is such select sql statment allowed?
    >>
    >> " SELECT * FROM Photo_SubCategory Where (Status = 1 And
    >> Session("id") <> "") or (Status = 0 And session("id") <> "" )"
    There seems to be a logical problem here. This WHERE clause will only reject
    a record when the session("id") is blank. There seems to be no reason to
    bring in the value of the Status field here. I'm assuming there are only two
    possible values for the Status field, 0 and 1, correct?

    Are you sure this is what you want? The effect would be the same as if you
    used an IF clause in your vbscript code and only ran this sql statement if
    the session("id") had a value:

    SELECT * FROM Photo_SubCategory
    >>
    >> Status is a field in the table.
    >>
    >> Basically I want to selct a record that satisfy when the field Status
    >> = 1 and session("id") <> ""
    >>
    >
    >
    > Oh the SQL should be
    >
    > " SELECT * FROM Photo_SubCategory Where (Status = 1 And) or (Status
    > = 0 And session("id") <> "" )"
    Why did you put the incorrect version of the statement into your post? You
    do have a Delete key, you know. You made me waste time explaining something
    you apparently already knew.


    Just like any other variable value, you need to concatenate the VALUE of the
    variable into the sql statement, not the name of the variable. Your database
    will not have a clue about what is meant by "session("id").

    sSQL = " ... WHERE (Status = 0 AND '" & Session("id") & "' > "'') " & _
    "OR (Status = 1)"

    Response.Write sSQL 'for debugging

    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. passing variables from 'select' element in a form
    By Thunderbirds in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: February 23rd, 09:05 AM
  2. passing variables from select elements in form
    By Thunderbirds in forum Macromedia ColdFusion
    Replies: 0
    Last Post: February 18th, 03:15 PM
  3. Replies: 0
    Last Post: September 24th, 03:24 AM
  4. Replies: 4
    Last Post: August 7th, 07:41 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 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