If Record Exists - redirect 3

[Billybonga]

I've only begun playing with SQL and ASP this week so any code I've written may be completly incorrect.

What I'm creating is an e-poll system where users of our Intranet register this votes. I am logging thier domain usernames to a table and wish to query this table when the submission form is loaded.

If the page detects that the user has already submitted a record i.e. if their name is already in the table - then it should re-direct them to an alternative page to prevent them re-submitting. If their name is not in the table then it will display the submission form etc.

Here is what I have

<%
$username = request.servervariables("LOGON_USER")

Set MyConn = Server.CreateObject("ADODB.Connection")
	MyConn.Open "DSN=database1"

SQL_Query = "SELECT * from tbl_people WHERE Name '$username'"
%>

How do I finsih this e.g. if $username exists then response.redirect("Thank_You.htm")

Any help is appreciated.

 

[kss444]

Need to connect to DB and get a RS back.
Dim objConn
Dim RS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Driver={SQL Server};Server=servername;Database=PlayTime;Uid=sa;Pwd=;"
objConn.Open

set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open "SELECT * from tbl_people WHERE Name '$username'",objConn

IF RS.RecordCount >=1 then
Response.Redirect("newpage.asp")
End if

 

[Abraxus]

As far as I can tell, $ variables aren't allowed in ASP. You'll have to change $username to just username, and I would suggest doing your connection like this:

SQL_Query = "SELECT * from tbl_people WHERE Name='" & username & "'"
RS.Open SQL_Query,objConn

Hope this helps!

 

[Billybonga]

Cheers guys - I'm making some head way alright ....

I kow this is a right dumb @ss question - kss444 - it looks like you included code to connect to an sql database ??

Above my code I had an include for a lib.asp file which contains my database connections :

<%

Function getQuestionById(id2query)

	' Connect to database
	'
	Set MyConn = Server.CreateObject("ADODB.Connection")
	MyConn.Open "DSN=william"

	' Create and Execute SQL Query
	'
	SQL_query = "SELECT question FROM tbl_questions WHERE id = " & id2query
	Set RS = MyConn.Execute(SQL_query)

	getQuestionById = RS(0)

	Set MyConn = Nothing


End Function

%>

I've tried stripping out your database connections but no go ?

Sorry for all the newbie questions ....

 

[Billybonga]

p.s. I am pulling the questions of the poll from a Access database table - tbl_questions

The first that first loads should run the check if user has already submitted and if not submitted should then show the listed of questions ...

I hope this makes sense.

 

[Billybonga]

Ok - here is the changes that I made - I don't get errors - but I don't get the results either .

In index.asp i added a line :

<!--#include file="check.asp"-->

I then created a file called check.asp

In check.asp I have:

<%

username = request.servervariables("LOGON_USER")

Dim objConn
Dim RS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=william"
objConn.Open 

set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open "SQL_Query = "SELECT * from tbl_people WHERE Name='" & username & "'",objConn

IF RS.RecordCount >=1 then
  Response.Redirect("newpage.htm")
End if
%>

I am connected to an MS Access database should that be where i'm going wrong???

 

[Abraxus]

You should take a look at what username is returning. ASP is server-side and I've never had success getting it to tell me the username on a client machine. I do a large amount of ASP intranet programming as well, and we have found we need to have our usernames linked in a table based on the interal static ips of their computers. You may need to rethink your validation mechanism.

 

[kss444]

In the line RS.Open "SQL_Query = "SELECT * from tbl_people WHERE Name='" & username & "'",objConn

Take out "SQL_Query ="

RS.Open "SELECT * from tbl_people WHERE Name='" & username & "'",objConn

 

[Billybonga]

cheers guys - this is soooo close that I can taste it but yet it still isn't working

I have added a couple of things to see if I can find where things are going wrong but it doesn't help

<%

username = request.servervariables("LOGON_USER")

Dim objConn
Dim RS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=william"
objConn.Open 

set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open "SELECT * from tbl_people WHERE Name='" & username & "'",objConn

IF RS.RecordCount >=1 then
  Response.Write "username found"
End if

Response.Write username


SQL_Query = "INSERT INTO tbl_people (Name) VALUES ('" & username & "')"
	objConn.Execute(SQL_Query)
%>

What I was getting was just a blank screen - so I started adding some stuff to see where things failed.

What I get now is :

a page with my username displayed (same username as in the database) and then the SQL Insert has inserted a new record of my username in the database (so i know that it is connecting to the correct database)

I have been unsuccessful in locating the record containing my username and displaying "username found"

From my little experience with ASP it does seem as though the code is perfect thanks to kss444.

Is what I'm doing even possible ???

 

[Tarwn]

Instead of using RS.recordCount to check if you have areturned record, use If Not RS.EOF. The only time EOF will be true on a freshly returned Recordset is if the recordset is empty.
I believe if I remember correctly you would need to set the Recordset to use adUseClient for the CursorLocation property in order for it to show an accurate number. It is likely returning a -1 right now, regardless of the actual count.

-T

 

[Billybonga]

Cheers Tarwn - and everyone who helped.

I finally managed to get it working - certainly the If not RS.EOF worked better than the recordcount.

here's the code I've ended up with for anyone interested:

<%


set objconn = server.createobject("adodb.connection")
objconn.open "database"
set objrec = server.createobject("adodb.recordset")


   ' Retrieve the Windows Domain Username
username = request.servervariables("LOGON_USER")

    'Search for records of Username

set RS = objconn.execute("select * from tbl_people where Name = '"&username&"' ")

        if RS.EOF = True then
     'If Username does not already exist this - 
     response.write "Username Is Not In Database"
         
        else
        
        response.write "Username Is In Database"

    End If
%>

I still have to fully test this but initial tests have proved to work correctly.

Thanks a million everyone for your help

Billybong