Conditional SELECT statement

This short tutorial will show you how to create different recordsets based on a UserLevel (Session Variable = MM_UserAuthorization).

This comes in handy when you are creating a front-end administration website and will save you the trouble of creating a lot of conditional regions within the body of your document to show or not to show data which is based on different UserLevels.

It takes some minor handcoding to accomplish this.

Let's say you wanted the administrator, that logs-in with the UserLevel "admin", see more data displayed on a page than the average user sees.

If we take a product overview page that contains active and in-active products (based on a database field, ACTIVE = Yes/No), we only want to show the active products to the visitor of the webpage, but we we want to show the administrator the active products as well as the in-active products.

You would have to create a If/Then/Else statement within the SELECT statement:

STANDARD SELECT STATEMENT

(selects all products from the Product table)

<%
set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.ActiveConnection = MM_local_STRING
rsProducts.Source = "SELECT* FROM tblProducts WHERE CatName = " + Replace(rsProducts__MMColParam, "'", "''") + ""
rsProducts.CursorType = 0
rsProducts.CursorLocation = 2
rsProducts.LockType = 3
rsProducts.Open()
rsProducts_numRows = 0
%>

ADJUSTED SELECT STATEMENT

(selects all products from the Product table for the logged-in administrator and selects only the active (AND Active = '1') for the visitor)

<%
set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.ActiveConnection = MM_local_STRING
If Session("MM_UserAuthorization") = "admin" Then
rsProducts.Source = "SELECT* FROM tblProducts WHERE CatName = " + Replace(rsProducts__MMColParam, "'", "''") + ""
Else
rsProduct.Source = "SELECT* FROM tblProducts WHERE CatName = " + Replace(rsProducts__MMColParam, "'", "''") + " AND Active = '1' + ""
End If
rsProducts.CursorType = 0
rsProducts.CursorLocation = 2
rsProducts.LockType = 3
rsProducts.Open()
rsProducts_numRows = 0
%>

Now you have a dynamic recordset based on a UserLevel and do not need to create seperate conditional regions within the body of the page.

Note: when adjusting the recordset in the Data Bindings panel the If/Then/Else statement stays intact, but both your SELECT statements will be the same.