We help IT Professionals succeed at work.

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

222 Views
Last Modified: 2013-12-24
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.
Comment
Watch Question

Commented:
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.

Commented:
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
 
 %>

Author

Commented:
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

Commented:
What is the error message you are getting?

Author

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

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.