Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 1st, 2008, 10:37 AM
Authorized User
 
Join Date: Jul 2008
Location: Deneysville, Free State, South Africa.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default If statement in WHERE clause

I have a recordset generated by an third-party extension that I would like to manipulate. I would like to show the records based on the “access level” of the person logging in. Either as administrator or as a user. If administrator show all records or if user show only the specific user entered records.

SELECT approvedFlag, category, id, memberId, postDate, title, userText
FROM News

I would like to manipulate this by adding the following in the WHERE clause:

If session"AccessLevel" = "1" (administrator), then
show all records

Else

If session"AccessLevel" = "2" (user), then
show only this user records

End if

I'm not sure about the exact coding or syntax, is the following code and syntax correct:

SELECT approvedFlag, category, id, memberId, postDate, title, userText
FROM News
WHERE
If session"AccessLevel" = "1" then
memberId = All (to display all records)
Else
If session"AccessLevel" = "2" then
memberId = session"UserID"
End If
ORDER BY postDate

I look forward to some assistance and advice.


Reply With Quote
  #2 (permalink)  
Old July 1st, 2008, 04:06 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Could you show some *REAL* code???

Since you are asking this as an ASP question, and since you seem to be using VBScript syntax, I would hope you are aware that none of THIS code is VBScript:

SELECT approvedFlag, category, id, memberId, postDate, title, userText
FROM News
WHERE
ORDER BY postDate

I'll take a STAB at this, just *GUESSING* what your REAL code might be like, but until/unless you show what you really have now it is just a guess.

<%
SELECT CASE CINT(session("AccessLevel")
    CASE 1 : where = "" ' admin sees all records
    CASE 2 : where = " WHERE memberid = " & CLNG(Session("UserID"))
    CASE ELSE
         Response.Write "Invalid Access Level...aborting"
         Response.End
END SELECT

SQL = "SELECT approvedFlag, category, id, memberId, postDate, title, userText " _
    & " FROM News " _
    & where _
    & " ORDER BY postDate, memberid "
...
%>
Reply With Quote
  #3 (permalink)  
Old July 2nd, 2008, 12:11 AM
Authorized User
 
Join Date: Jul 2008
Location: Deneysville, Free State, South Africa.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your response.

I feel rather stupid, this is my very first post ever as an absolute novice to programming. I use a wysiwyg editor to help me develop websites. As far as coding goes, I'm absolutely clueless. Please excuse my ignorance, I hope I can learn.

I have cut and paste the code from my webpage in question. Hopefully this helps. If not, let me know exactly how I can supply you the information you need.

<%
Dim WADANews
Dim WADANews_cmd
Dim WADANews_numRows

Set WADANews_cmd = Server.CreateObject ("ADODB.Command")
WADANews_cmd.ActiveConnection = MM_conn_glen_STRING
WADANews_cmd.CommandText = "SELECT approvedFlag, category, id, memberId, postDate, title, userText FROM News WHERE If session("AccessLevel") = "1" then memberId = "" Else If session("AccessLevel") = "2" then memberId = session("UserID") End If ORDER BY postDate"
WADANews_cmd.Prepared = true

setQueryBuilderSource WADANews_cmd, WADbSearch1_whereClause, false
Set WADANews = WADANews_cmd.Execute
WADANews_numRows = 0
%>
<%
Dim WADANews_1__numRows
Dim WADANews_1__index

WADANews_1__numRows = 10
WADANews_1__index = 0
WADANews_numRows = WADANews_numRows + WADANews_1__numRows
%>

I have had a stab at adding the underlined text above to try and achieve my goal. Still clueless.

I highly value and appreciate your willingness to help me. Thank you.

Reply With Quote
  #4 (permalink)  
Old July 2nd, 2008, 12:17 AM
Authorized User
 
Join Date: Jul 2008
Location: Deneysville, Free State, South Africa.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oops... underlined text not shown.

[u]WHERE If session("AccessLevel") = "1" then memberId = "" Else If session("AccessLevel") = "2" then memberId = session("UserID") End If ORDER BY postDate"</u>

The above text is what I have added.

Reply With Quote
  #5 (permalink)  
Old July 2nd, 2008, 01:06 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

No, you can't do that.

Please understand that you are actually working with *TWO* computer languages at once here: VBScript and SQL.

But the "trick" here is that the SQL is in the form of a *STRING* that VBScript creates.

VBS has NO KNOWLEDGE of what is legal SQL. For all it cares, you could create a SQL query that says
SQL = "Polish the Furniture"
and when you ended up trying to do
Set WADANews = WADANews_cmd.Execute
*ONLY THEN* would you find out that the database wants to give you a nasty message telling what you can do with the completely bogus SQL.

Okay, so that's part one of the answer.

The second part is that you can NOT have an IF that belongs to VBScript in the middle of *EITHER* a line of code (has to be at the beginning of the line) *OR* in the middle of the string that makes up the SQL.

So I'm assuming that the *original* code looked like this:
Code:
    WADANews_cmd.CommandText = _
    "SELECT approvedFlag, category, id, memberId, postDate, title, userText " _
  & " FROM News " _
  & " WHERE memberId = " &  session("UserID") & " ORDER BY postDate"

If we assume that the value of
    session("UserID")
is (example only)
    7713
Then the *ACTUAL* SQL we are asking the DB to handle is
    SELECT approvedFlag, category, id, memberId, postDate, title, userText FROM News WHERE memberId = 7713 ORDER BY postDate

Now, what we *NEED* to do is simply remove that WHERE clause when the use is an admin! By doing that, you are no longer restricting the SQL to only get news from a single memberID. In short, it will get *ALL* news. Period.

So that's what the code I gave you will do for you.

And you *can* slip it in place in your code, but there is some other bogus stuff in there I don't understand, so I'm going to just rewrite the whole thing to make it faster and simpler.
Code:
<%
Dim conn, WADANews

Set conn = Server.CreateObject ("ADODB.Connection")
conn.Open MM_conn_glen_STRING

SELECT CASE CINT(session("AccessLevel"))
    CASE 1 : where = ""  ' admin sees all records
    CASE 2 : where = " WHERE memberid = " & CLNG(Session("UserID")) 
    CASE ELSE 
         Response.Write "Invalid Access Level...aborting"
         Response.End
END SELECT

SQL = "SELECT approvedFlag, category, id, memberId, postDate, title, userText " _
    & " FROM News " _
    & where _
    & " ORDER BY postDate, memberid "

Set WADANews = conn.Execute( SQL )

' and I doubt seriously that you have any need for the silly DreamWeaver variables 
'    Dim WADANews_numRows
'    Dim WADANews_1__numRows
'    Dim WADANews_1__index
' but you can leave them in if you want to
%>
But I need to give you fair warning that if you expect to continue altering stuff that DreamWeaver creates for you, you're going to have to bite the bullet at some point and learn how SQL and VBScript work. And the sad part about it is that the code DW produces is actually *harder* to read and understand and modify that code you might write yourself, once you learn the basics.
Reply With Quote
  #6 (permalink)  
Old July 2nd, 2008, 03:44 PM
Authorized User
 
Join Date: Jul 2008
Location: Deneysville, Free State, South Africa.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good day.

Thank you for the recoding. I have inserted your coding instead of the Dreamweaver coding. At first, I had no joy. I'll then added response.write statements to check both session variables, I get the correct results. How ever, I don't seem to get further than your response.write "invalid access level... Aborting".

When I remove both your response.write, I do have all the results of the recordset being displayed. It seems that the case statement is not functioning. Unfortunately, with my coding experience I can't see or detect any errors. Kindly validate your coding.

I am grateful for your advice regarding changing the Dreamweaver coding. Yes, it is difficult to read.

With all the information available on the web I am not sure of where to start learning the basics. I have on occasion visited the w3schools.com website to do some reading. Very interesting. Do you have any other learning websites that you can recommend.

As a quadriplegic I avoid typing at all costs and that is the basis of why I selected Dreamweaver as wysiwyg editor to help me develop some websites. I suppose in the long run I had better bite the bullet and learned to do some hand coding.

Thank you kindly for all your help and advice.

Reply With Quote
  #7 (permalink)  
Old July 2nd, 2008, 03:55 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, if you got this message:
    Response.Write "Invalid Access Level...aborting"

Then it means that the value of
    session("AccessLevel")
is neither 1 nor 2. I can't fix that. Apparently, you have some path through your code/pages that arrives at this spot with some other value of AccessLevel. (But just noticed your comment...see last paragraph.)

Yet it must be a *number* else my use of CINT() in
    SELECT CASE CINT(session("AccessLevel"))
woul have given you an error.

SO...

Perhaps the easiest fix here is to simply say "if the AccessLevel is *ANYTHING* other than 1 [admin], then select only the single memberID."

Is that good enough??

If so, just change this code:
Code:
SELECT CASE CINT(session("AccessLevel"))
    CASE 1 : where = ""  ' admin sees all records
    CASE 2 : where = " WHERE memberid = " & CLNG(Session("UserID")) 
    CASE ELSE 
         Response.Write "Invalid Access Level...aborting"
         Response.End
END SELECT
to this, instead:
Code:
level = Trim( "" & session("AccessLevel") )
where = " WHERE memberid = " & CLNG(Session("UserID")) ' assume *NOT* an admin
' but if *IS* admin, then get rid of the where
If isNumeric(level) Then If CDBL(level) = 1 Then where = ""
You said that you *DID* add Response.Write's to check that the value of AccessLevel is correct. So I'm more than a little puzzled as to why the SELECT CASE didn't work. But this code should be more flexible and be able to handle slighly off-kilter values from Session("accessLevel"). Let me know.
Reply With Quote
  #8 (permalink)  
Old July 2nd, 2008, 03:57 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi frakey,

Since you wrote me a personal message yesterday, I'll join the discussion here.

Are you sure session("AccessLevel")) contains the right number? What happens when you try this:

Response.Write("Value is """ & session("AccessLevel")) & """")

This puts the value between quotes so you can whether it contains spaces or not. And where did you put the Response.Write statements to check the values? Right before the Case block?

Also, instead of Dreamweaver and "classic" ASP, you may want to invest your time in ASP.NET, the successor (since years) to classic ASP. Much more future proof, a much better technology and an IDE that generally creates cleaner code (or requires no code at all) than Dreamweaver does.

You may want to check out my book Beginning ASP.NET 3.5 which introduces you to this technology, together with the coding skills you need to accomplish the kind of thing you are building.

http://www.wrox.com/WileyCDA/WroxTit...47018759X.html

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
Reply With Quote
  #9 (permalink)  
Old July 2nd, 2008, 04:11 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

I 100% agree with Imar re ASP vs. ASP.NET.

You are quite likely to find that you need to do less typing if you use Visual Studio (including the *FREE* Visual Studio Express for ASP.NET ... see http://msdn.microsoft.com/express ) and you are also learning a skill for the future.

ASP is, if not quite dead, only barely alive. MS has announced there will be no further improvements, and that seems to include even bug fixes (not that I've seen many major bugs in the last few years...just annoyances).

Mind you, it's a different paradigm. You have to warp your mind in new ways of thinking. But the tutorials available (starting at that above url and at http://www.asp.net/learn ) are nothing short of wonderful. *FAR* in advance of any tutorial material ever produced for ASP.

Don't get me wrong, I love the simplicity of ASP. But I sure am not making a living writing in it, any more. [Actually, I've gone over to the "dark side" for the most part, in my current job. JSP on Linux. But we won't say that too loud in this forum.]
Reply With Quote
  #10 (permalink)  
Old July 2nd, 2008, 06:02 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

If I may give an opinion

There is so much classic ASP out there - IMO it will be around for a while yet. I am a web scripter, script kiddie or what ever they want to call us - In fact script kiddie just makes me feel young :o)

80% of our office carry out OO type programming, there are only guys who write classic ASP. We have so many clients with exisitng classic app's there is going to be work here for a long while yet. IMO if you are young and want to earn big money building high profile systems you need to be an OO programmer. If you are 'not so young' and dont want to be sitting in front of a computer fo more than the next 5-10 years stick with classic ASP. Alot of classic ASP guys switched here in Australia; now there is, and has been a demand for classic coders for a while now.

Anyhow over the years I have learnt alot from this forum, especially Imar (who I call Mr ASP). This is the reason I felt the need to place this post here. Clasic ASP still going strong in Aussie....have a good day all.

Wind is your friend
Matt
www.elitemarquees.com.au
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Case Statement In Where Clause Logic dwj119 SQL Server 2000 4 October 27th, 2011 03:14 PM
Problems using case statement in where clause vghiya SQL Server 2000 3 May 28th, 2007 05:12 AM
Select statement with where clause with 2 conditon Yasho VB.NET 2002/2003 Basics 7 May 16th, 2007 01:40 PM
If statement in Where Clause jdziggy SQL Server 2000 2 March 16th, 2007 08:08 AM
USING SQL IF Statement in WHERE clause rit01 SQL Server 2000 17 March 15th, 2007 08:33 AM



All times are GMT -4. The time now is 11:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.