Hello! Trying to figure out how and when to value a session variable from a database table at logon. Already know how to value it from form input but have read where you can value it from a database table column but have not found any examples yet.

This session variable would be interrogated within the site to determine navigation paths. The database table column, Production, for example, is valued with a Y when the user registers with the site the very first time.

For subsequent logins to the site, would like to put the value of Production from the User Table to a session variable. The value can only be changed to N by an administrative function depending on certain conditions. For navigation purposes, different links will be displayed depending on the value in the Production column.

Would guess that some type of SQL is needed to read the table column for a particular user that logged in and it would have to then be set to a session variable name.

Not sure if this needs to be done on the login page with a Visual Basic function in the head section or if there is a UD4 behavior that can do this.

Would appreciate greatly any examples or help with this. Thanks!

Create a recordset on the page displayed after logon page (eg. your Welcome back Joe Bloggs - page) using a SELECT statement something like:

SELECT MyNavigationMarker
From MyUserInfoTable
WHERE UserName LIKE "varUserName"

then after the recordset:

Session("NavigationMarker" = (rsTblMyUserInfoTable.Fields.Item("MyNavigationMarker".Value)


Regards

Owen.

Hey! Have tried that and the session variable does not display on the page. Went into the Live Data view and tested the recordset to verify that part.
The only difference in your sample code and the current code is that you have extra parentheses around the whole recordset variable. Have run across other examples where those are not needed but will try that.

The session variables generated by UltraDev from the Login behavior such as MM_Username do appear when placed on the page. However, this manually created one is not working. Have also tried putting the assignment statement for the session variable right after the record set before the head section and also in the body section but that is not working.

Does it matter where the recordset is created on the page, it looks like UltraDev puts the code at the the top before the head section so would assume that placement is correct. This site also has another page that has a repeated region on a table that displays almost the same recordset but no session variables are used on that page. That particular page displays all the recordset information correctly.

If the additional parentheses do not fix this then maybe their might be some
other potential errors that need correcting as well. Maybe UltraDev has a way to display all the current session variables that are in use on the site in addition to the ones it shows in the Data Bindings behavior tab. That would indicate if this manually created one is indeed working.

Will be able to test out the recommended syntax changes tonight. Will post the results as soon as possible. Thanks!

If you just want to display the session variable just use ResponseWrite to display it on the page:


<pre id=code><font face=courier size=2 id=code>&lt; % = NavigationMarker %&gt;</font id=code></pre id=code> - Note take out the space between &lt; and %, had to put that in to get it to display on the forum.

Oh and change the quotes to single quotes - my mistake.


Regards

Owen.

Hey! Well it worked and it did not work. As it turns out the syntax was correct. Nothing was displaying because the recordset was a simple and not from the advanced component. Therefore, a specific where condition was not on the original SQL and it brought back all the rows from the table.

The first row is the default and it just happens that the column that was used to value the session variable was null. With that, the next question is how to code the Session(MM_Username) correct in the SQL so that the row matching the logged on Username returns and will bring back the value in the Production column which will be set to the new session variable field.

Looking at an example in UltraDev 4, The Complete Reference book, it has a recordset advanced dialog shown with some SQL text. The example on page 505 shows the name, default and run time values needed. Looks like name is the session variable name, default is a percent sign and run time value is the session variable created at logon. This is MM_Username but not sure how this and the other values are used here.

Their sample Where clause has WHERE (Seekers.SeekUsername = 'svUsername') plus another condition. It looks like svUsername is the variable assigned to reference the MM_Username session variable already created but not sure what the percent sign in the default value is using here. Is it true that the name parameter is always the variable to be passed to the SQL WHERE clause and the runtime field is the name of the actual field on your page that contains the value being checked for at this point?
If that is true then what is the default value's purpose? Also could the Session(MM_Username) be coded directly in the SQL instead and if so what is the correct syntax for the example WHERE above if indeed MM_Username was used here.

Finally, does the recordset return a row total value somewhere to determine if a query is successful in order for it to be interrogated and used to determine the navigation path? Looks like so far this software is great but still trying to learn the foundations in order to perform some basic functions.

Very much appreciate any help that can be provided. Thanks!

Wait a minute,

I've just re-read what you are trying to do. As I understand it you wish to restrict access to cirtain parts of the site until the user has registered.

Ultrdev can do this for you, look at <b>Server Behaviours - User Authentication - Restrict Access to Page</b>. It's pretty good you can set it on every page that you wish to be restricted so that even if someone access that page directly by typing in the URL you can set it up to redirect them to another page, the login page for instance. You can also set up different access levels for specific areas of the site. For instance you may have user pages and administrator pages.

Had a look through my Copy of the Complete Reference, no mention of it that I can find. Maybe it's not as complete as it should be.

Regards

Owen.

Hey! Yes certain parts of the site have restricted access and have used that behavior a few times.

However, the last post was regarding pulling a value from a database via a recordset and based on that value the user will be allowed to navigate to some pages but not all contained in the whole web. Technically it is restricting access but not completely.

The basis for this is that the value accessed from the database via the recordset and put into a session variable can change over time to different values depending on what level the user has attained so far.

This value will only be updated by the web administrator manually or via a stored procedure. For this application it is needed to be put into a session variable based upon some other necessary functionality.

The previous post was asking how an existing session variable, MM_Username, created at logon can be used in the SQL to access that user's row and pull another value from a database column which would be put into a new session variable. The questions were regarding the examples in the book, Dreamweaver Ultradev 4 The Complete Reference by West, Muck and Allen on the indicated page.

Was not totally understanding some of the dialog box requirements when an advanced query is being created.

Hope this clears up the previous post. Very much appreciate all your help so far. Thanks!

OK, think I've got it.


First set up a recordset to SELECT your navigation marker from the user table based on the session variable MM_Username

<pre id=code><font face=courier size=2 id=code>
&lt;%
Dim MyRecordsetName__MMColParam
rsTblUsers__MMColParam = "%"
if (Session("MM_Username" &lt;&gt; "" then MyRecordsetName__MMColParam = Session("MM_Username"
%&gt;
&lt;%
set MyRecordsetName = Server.CreateObject("ADODB.Recordset"
MyRecordsetName.ActiveConnection = MM_GBsounds2OLE_STRING
MyRecordsetName.Source = "SELECT MyNavigationMarkerField FROM MyUserInfoTable WHERE UserName = '" + Replace(MyRecordsetName__MMColParam, "'", "''" + "'"
MyRecordsetName.CursorType = 0
MyRecordsetName.CursorLocation = 2
MyRecordsetName.LockType = 3
MyRecordsetName.Open()
MyRecordsetName_numRows = 0
%&gt;</font id=code></pre id=code>

Then set another session variable to your recordset field value:
<pre id=code><font face=courier size=2 id=code>
Session("NavigationMarker"=(MyRecordsetName.Fields.Item("MyNavigationMarkerField".Value)
</font id=code></pre id=code>

Is that what you want?


Regards

Owen.



Edited by - oeastwick on 08/24/2001 03:08:00

Hey! Pretty much have it working. Appreciate the great help.

For future reference what is the MM_ColParam variable used for or how and why does UltraDev have it created. Realize in your example that you define it but remembering running across a similar variable in UD at some point.

Also, is this one of those cases where manual coding is required or could what you did be done via a behavior dialog box or the like? Have another question on a related topic if you have time as well.

Actually it is more than one question but trying to determine the best method possible.

First, have set up an Insert Record form to insert a new record on table2 and the insert record behavior does a straight map from form fields to the table columns. Also hidden fields can be mapped too. Was trying to map the hidden field which is the Session(MM_Username) created at logon to the table2 Username column. Cannot get the value to come across via the dialog box, it thinks the value is actually the literal Session(MM_Username), it is not resolving this.

How does this need to be coded in the Insert Record behavior dialog box so that it resolves the variable to the value correctly? If it cannot be done in the dialog box then guess it would need some manual additions to the insert code on the page.

Related to the above, would like to also carry across the UserID which is an autonumber column on the Usertable created at registration time to the UserID column on table2 upon the insert as well. According to the book it says to not explicitly reference this autonumber field as it will do the create. However, the UserID column on table2 is created upon the insert and therefore is different than the one on the User table. Would like to use the UserID column instead of the Username for future table join purposes. Can this be done and what is the best method to accomplish this. Would prefer dialog box input but will add manual code if necessary.

Finally, instead of doing an insert to table2 after a user logs in an chooses the appropriate function, would possible want to do an insert when the user registers so that they have a row on both the Usertable and table2 when registration is complete. This way only updates to table2 would be needed in the future instead of inserts as it is being done now. Is this more efficient, inserting to two tables at the same time, can it be done via a behavior or is manual code needed on the registration page? The database is not large and the user audience may be around 100 so contention issues should not come into play.

Realize have a lot of questions here but any help or recommendations would be tremendous. Thanks!