[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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.
0
achernob
Asked:
achernob
  • 5
  • 3
1 Solution
 
lrygielCommented:
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.

0
 
lrygielCommented:
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
 
 %>
0
 
achernobAuthor 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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lrygielCommented:
What is the error message you are getting?
0
 
achernobAuthor Commented:
I am not getting any errors, it always goes to the redirect page, even if the database is empty
0
 
lrygielCommented:
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
0
 
lrygielCommented:
A couple of things.

1) How is the USERID column defined? From your code I am assuming it is integer (or long).

2) Also, can you display the SQL being executed, then cut and paste it in Access and execute it there?
Throw the following right after the oDBC.Open, then copy the resulting sql and execute it in Access.

     Response.write "SELECT COUNT(*) FROM RESULTS WHERE USERID = " & iUserID:Response.End
0
 
achernobAuthor 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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now