asked on

Quetion how to write a SQL Query for an Access database in .ASP

I am in no way shape or form a web guy, so I have almost no clue how to do this. I wrote the page that does the form/db portion using Frontpage, if that's any indication of my skill level :)

I have a web page, that asks a user a question, and then logs the answer, along with the user ID in an access database. (Using NT Authentication)

I do not want users to be able to keep coming to the page and adding more records in with answers. I only want each user to have one record in the database.

What I want to be able to do, is set up a SQL query at the top of the page, that looks into the database after the user authenticates, then if it finds a record with that user's ID it redirects them to a different page that would say something like "You already have a record in the database, go away". If no record is found then it displays the page, and allows them to complete the form.

lrygiel

Use something like this

Select count(*) as CNT from AnsewerTable where userID = 12345
check the recordset "CNT" for a value greater than 0 and if it is greater than 0 redirect them to the proper page.

ASP code is as follows:

<%
Dim oDBC
Set oDBC = server.CreateObject("ADODB.CONNECTION")
dim oRST = server.CreateObject("adodb.recordset")

odbc.Open "<your connection string>"
orst.Open "SELECT COUNT(*) FROM ANSWERS WHERE USERID = " & iUserID

if oRST(0) > 0 then response.Redirect "alreadyaswered.htm"

oRST.close
oDBC.close

Set oRST = nothing
Set oDBC = nothing

%>

Just use the proper connection string and change the query to reflect the correct table name and UserID properties.

lrygiel

OOPS. The last one contained a coule of errors. Use this one

<%
Dim oDBC
Set oDBC = server.CreateObject("ADODB.CONNECTION")
dim oRST
Set oRST = server.CreateObject("ADODB.RECORDSET")

oDBC.Open "<your connection string>"
oRST.Open "SELECT COUNT(*) FROM ANSWERS WHERE USERID = " & iUserID, oDBC

if oRST(0) > 0 then response.Redirect "alreadyaswered.htm"

oRST.close
oDBC.close

Set oRST = nothing
Set oDBC = nothing

%>

achernob

ASKER

What am I doing wrong? Here is the code I am using:
<%
Dim oDBC
Set oDBC = server.CreateObject("ADODB.CONNECTION")
dim oRST
Set oRST = server.CreateObject("ADODB.RECORDSET")

oDBC.Open "DSN=Security"
oRST.Open "SELECT COUNT(*) FROM RESULTS WHERE USERID = " & iUserID, oDBC

if oRST(0) > 0 then response.Redirect "answered.htm"
oRST.close
oDBC.close

Set oRST = nothing
Set oDBC = nothing

%>

There is an System DSN called Security
The database has the following colums in a table called Results:
UserID, Question, Answer

lrygiel

What is the error message you are getting?

achernob

ASKER

I am not getting any errors, it always goes to the redirect page, even if the database is empty

lrygiel

I just created an access database called security.mdb with a table to your specifications. I created a odbc DSN = security

i plugged a record in the table with a USERID = 1 (existing row), ran your code (after assigning iUserID = 1) and it redirected fine. When I assigned iUserID = 2 (no row exists), it didn't redirect.

Are you sure you are assigning the proper value to iUserID?

Lee

ASKER CERTIFIED SOLUTION

lrygiel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

achernob

ASKER

Here's what I used to get it working:
<%
Dim oDBC
Set oDBC = server.CreateObject("ADODB.CONNECTION")
dim oRST
Set oRST = server.CreateObject("ADODB.RECORDSET")
iUserID=lcase(request.servervariables("LOGON_USER"))
oDBC.Open "Security"
oRST.Open "SELECT COUNT(*) FROM RESULTS WHERE USERID ='" & iUserID & "'", oDBC

if oRST(0) > 0 then response.Redirect "answered.htm"
oRST.close
oDBC.close

Set oRST = nothing
Set oDBC = nothing
%>

Thanks for all your help. Greatly appreciated.