ASP VBScript , Remote_User server variable, and Access DB

Report · Mar 25, 2009

I need to recreate some ASP pages that were orginally created in MS FrontPage once upon a time.

I have a table in an Access DB that contains the user's login ID. I need to pull the date the user completed a quiz at the end of a web training module.

My connection looks OK, b/c I can pull other info. I can pull the quiz completion date if I put in my login ID. The login ID is called Email in the DB and the completion date is called Time.

This works:

SELECT [Time]
FROM QuizComplete
WHERE Email = 'OCSP/BUTLER_A' AND QuizID = 109

How do I change the WHERE Email so it filters by the Server Variable Remote_User?

Thanks!

Report · Mar 25, 2009

Amy_L wrote:
> I need to recreate some ASP pages that were orginally created in MS FrontPage
> once upon a time.
>
> I have a table in an Access DB that contains the user's login ID. I need to
> pull the date the user completed a quiz at the end of a web training module.
>
> My connection looks OK, b/c I can pull other info. I can pull the quiz
> completion date if I put in my login ID. The login ID is called Email in the
> DB and the completion date is called Time.
>
> This works:
>
> SELECT [Time]
> FROM QuizComplete
> WHERE Email = 'OCSP/BUTLER_A' AND QuizID = 109
>
> How do I change the WHERE Email so it filters by the Server Variable
> Remote_User?
>
> Thanks!

In the recordset, enter this as the value
<%=Server.Variable("REMOTE_USER")%>

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 25, 2009

Thanks Dooza

I tried it but I'm doing something wrong. I tried your value and a couple different variations- without the < >, with =%, with single quotes- and I get syntax error messages.

Can you walk me through it?

Report · Mar 26, 2009

Amy_L wrote:
> Thanks Dooza
>
> I tried it but I'm doing something wrong. I tried your value and a couple
> different variations- without the < >, with =%, with single quotes- and I get
> syntax error messages.
>
> Can you walk me through it?

It might need to be entered as just Server.Variable("REMOTE_USER")

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 26, 2009

> It might need to be entered as just Server.Variable("REMOTE_USER")
>

It's
request. ServerVariables("REMOTE_USER")

Cheers,
Jon

"Dooza" <doozadooza@gmail.com> wrote in message
news:gqfi3m$eaj$2@forums.macromedia.com...
> Amy_L wrote:
>> Thanks Dooza
>>
>> I tried it but I'm doing something wrong. I tried your value and a
>> couple different variations- without the < >, with =%, with single
>> quotes- and I get syntax error messages. Can you walk me through it?
>
> Dooza
> --
> Posting Guidelines
> http://www.adobe.com/support/forums/guidelines.html
> How To Ask Smart Questions
> http://www.catb.org/esr/faqs/smart-questions.html
> How To Report A Bug To Adobe
> http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 26, 2009

Jon Spivey wrote:
>> It might need to be entered as just Server.Variable("REMOTE_USER")
>>
>
> It's
> request. ServerVariables("REMOTE_USER")

Oops, school boy error, sorry!

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 26, 2009

Sorry! Still not working for me.

Tried this:

SELECT [Time]
FROM QuizComplete
WHERE Email = request. ServerVariables("REMOTE_USER") AND QuizID = 109

Got an error about the double quotes. Tried with single quotes and no quotes around REMOTE_USER. Tried with single quotes and double quotes around the whole value.

How should I adjust the SQL?

Report · Mar 26, 2009

Amy_L wrote:
> Sorry! Still not working for me.
>
> Tried this:
>
> SELECT [Time]
> FROM QuizComplete
> WHERE Email = request. ServerVariables("REMOTE_USER") AND QuizID = 109
>
> Got an error about the double quotes. Tried with single quotes and no quotes
> around REMOTE_USER. Tried with single quotes and double quotes around the
> whole value.
>
> How should I adjust the SQL?

You don't enter it in the URL, you enter it in the value field when you
add a parameter to the sql in advanced mode.

Your SQL will look like this:

SELECT [Time]
FROM QuizComplete
WHERE Email = Param1 AND QuizID = Param2

You then add parameters Param1 and Param2. Make sure you use the right
Type, text for param1 and numeric for param2.

The value for param1 is ServerVariables("REMOTE_USER") and the default
value can -1.

The value for param2 is Request.Form("nothing") and the default value is
109.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 26, 2009

Ah! Ok, I set it up with parameters, but still getting error messages.

It doesn't seem to like having the ServerVariables("REMOTE_USER") as the value for Param1. The webpage won't open in a browser at all.

I then tried it with the value as Request.ServerVariables("REMOTE_USER"). The web page opens and where the data should be there's an error message:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I'm sure my record is in the DB. Even it wasn't there I need where the recordset on the web page is to be blank. Is there an additional piece of code I am supposed to add?

Report · Mar 27, 2009

Amy_L wrote:
> Ah! Ok, I set it up with parameters, but still getting error messages.
>
> It doesn't seem to like having the ServerVariables("REMOTE_USER") as the value
> for Param1. The webpage won't open in a browser at all.
>
> I then tried it with the value as Request.ServerVariables("REMOTE_USER"). The
> web page opens and where the data should be there's an error message:

Ahh yes, thats the correct one.

> ADODB.Field error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been deleted. Requested
> operation requires a current record.

That means it not finding a record with the remote_user that matches.

> I'm sure my record is in the DB. Even it wasn't there I need where the
> recordset on the web page is to be blank. Is there an additional piece of code
> I am supposed to add?

As a test, create a blank page and put
<%=Request.ServerVariables("REMOTE_USER")%> on it and then display the
page in your browser. You will see what is returned so you can then
double check your database. If you get nothing back, then your IIS
server isn't configured properly, or you are using a browser that
doesn't use your authenticated login from your domain controller.

Firefox needs to have your servers names added to a special section in
advanced options for this to work, IE does it by default, Safari can't
do it at all and Opera will prompt for your username and password.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 27, 2009

I see what I did now, I intentially left out the code that splits the domain from the user's name just to figure out the recordset query. Only the name is in the DB. I manually added my domain to my name in a record of the DB with a / instead of a \.

So it works for records that are in the DB !!!! But not if there is no record!! Then I get that field error message.

How do I get it so it justs leaves a blank on the page if there is no record in the DB?

Report · Mar 27, 2009

Amy_L wrote:
> I see what I did now, I intentially left out the code that splits the domain
> from the user's name just to figure out the recordset query. Only the name is
> in the DB. I manually added my domain to my name in a record of the DB with a
> / instead of a \.
>
> So it works for records that are in the DB !!!! But not if there is no
> record!! Then I get that field error message.

Debugging is always fun!

> How do I get it so it justs leaves a blank on the page if there is no record
> in the DB?

Ok, what you need to look at is the Show Region options in the
Application Panel on the Server Behaviours tab.

What you do is first select the area where the results are shown, then
use Show Region If Recordset Is Not Empty.

Then, below that, put some text saying something like "record not
found", select it, then use Show Region If Recordset IS Empty.

These are very useful tools, learn to use them and your life will be
much easier, and you will find all sorts of things to use them on.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 27, 2009

Got it!

Awesome!

Thanks for bearing with me Dooza!

Report · Mar 27, 2009

Amy_L wrote:
> Got it!
>
> Awesome!
>
> Thanks for bearing with me Dooza!

Not a problem, I just gave you information that I learnt myself from
being in the same situation.

Have a great weekend!

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform