Asp variables in sql select

[HelLind]

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

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" <u8712123@cc.nctu.edu.tw> wrote in message
news:9a6fe908.0408061238.7f1d2076@posting.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]

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.xoc@dnartreb.noraa> wrote in message news:<OIB3nb$eEHA.3928@TK2MSFTNGP11.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" <u8712123@cc.nctu.edu.tw> wrote in message
> news:9a6fe908.0408061238.7f1d2076@posting.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]

[email]u8712123@cc.nctu.edu.tw[/email] (HelLind) wrote in message news:<9a6fe908.0408061238.7f1d2076@posting.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") <> "" )"

[Bob Barrows [MVP]]

HelLind wrote:

> [email]u8712123@cc.nctu.edu.tw[/email] (HelLind) wrote in message
> news:<9a6fe908.0408061238.7f1d2076@posting.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"