redirect if record already exists in database

[Jim in Arizona]

I'm wanting to do a simple controlled voting page. I too our webserver off
anonymous and everyone who accesses the website is a domain authenticated
user. I've already done some control structure pages based on
Request.ServerV ariables("AUTH_ USER"), which works great. That's also how I
would do this page, in my basic thinking.

My idea is to have an access database with two tables. One table will have
the vote written to it and the other table will have the AUTH_USER written
to it when the employee casts their vote. I'm wondering how I would do a
test against that table to see if their name has already been written to the
table. So, if an employee votes already, when they go to vote again, their
user logon would be tested against all entries in the table and if it
exists, the vote would not be written and they would be redirected to
another page that politely tells them they've already voted.

I'm hoping there is an easy answer for this. :)

Thanks,
Jim

[Jeff Cochran]

On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
<ti*******@hotm ail.com> wrote:

I'm wanting to do a simple controlled voting page. I too our webserver off
anonymous and everyone who accesses the website is a domain authenticated
user. I've already done some control structure pages based on
Request.Server Variables("AUTH _USER"), which works great. That's also how I
would do this page, in my basic thinking.

My idea is to have an access database with two tables. One table will have
the vote written to it and the other table will have the AUTH_USER written
to it when the employee casts their vote. I'm wondering how I would do a
test against that table to see if their name has already been written to the
table. So, if an employee votes already, when they go to vote again, their
user logon would be tested against all entries in the table and if it
exists, the vote would not be written and they would be redirected to
another page that politely tells them they've already voted.

I'm hoping there is an easy answer for this. :)

Do a SELECT from the authorization table WHERE the user column is
equal to AUTH_USER, then branch on whether it's null. Or just us an
IF EXISTS in your query if your database supports that and do the
entire thing in a single query. Books Online has examples if you use
SQL Server.

Jeff

[Jim in Arizona]

I'm using an access 2K database.

I'm trying to do what you suggested but I'm running into some trouble. I'm
getting this error:

a.. Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'SELECT * from voting'
/castvote.asp, line 17

Here's my code:

------------------------- castvote.asp-----------------------------

Dim empname, Conn, SQL1

empname = Request.ServerV ariables("AUTH_ USER")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
server.mappath( "eoty.mdb")

SQL1 = "SELECT * from votingemp where(voter = '" & empname & "')"

Conn.Execute SQL1,,129

'Response.Write (SQL1)

Response.Write( SQL1.Fields("vo ter"))

-------------------------------- end asp -----------------------------

When I comment out the Response.Write( SQL1.Fields("vo ter")) and uncomment
the Response.Write( SQL1), I get this SQL Statement:

SELECT * from votingemp where(voter = 'DOMAIN\jim')

I tested that statement in access and it worked fine.

Line 17 is Response.Write( SQL1.Fields("vo ter"))
My plan, if this was working, was to place the result of the
Response.Write( SQL1.Fields("vo ter")) into a variable like so:

testvariable = Response.Write( SQL1.Fields("vo ter"))

Then do some branching off of that like so:

If testvariable = empname Then
Response.Write( "You already voted")
Else
VoteDB Insert string Here
End If

Am I heading in the right direction?

Thanks,
Jim


"Jeff Cochran" <je*********@zi na.com> wrote in message
news:41******** ********@msnews .microsoft.com. ..

On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
<ti*******@hotm ail.com> wrote:

I'm wanting to do a simple controlled voting page. I too our webserver off
anonymous and everyone who accesses the website is a domain authenticated
user. I've already done some control structure pages based on
Request.Serve rVariables("AUT H_USER"), which works great. That's also how I
would do this page, in my basic thinking.

My idea is to have an access database with two tables. One table will have
the vote written to it and the other table will have the AUTH_USER written
to it when the employee casts their vote. I'm wondering how I would do a
test against that table to see if their name has already been written to
the
table. So, if an employee votes already, when they go to vote again, their
user logon would be tested against all entries in the table and if it
exists, the vote would not be written and they would be redirected to
another page that politely tells them they've already voted.

I'm hoping there is an easy answer for this. :)

Do a SELECT from the authorization table WHERE the user column is
equal to AUTH_USER, then branch on whether it's null. Or just us an
IF EXISTS in your query if your database supports that and do the
entire thing in a single query. Books Online has examples if you use
SQL Server.

Jeff

[Chris Hohmann]

"Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:OU******** ******@TK2MSFTN GP11.phx.gbl...

"Jeff Cochran" <je*********@zi na.com> wrote in message
news:41******** ********@msnews .microsoft.com. ..

On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
<ti*******@hotm ail.com> wrote:

I'm wanting to do a simple controlled voting page. I too our webserver offanonymous and everyone who accesses the website is a domain authenticateduser. I've already done some control structure pages based on
Request.Serve rVariables("AUT H_USER"), which works great. That's also how Iwould do this page, in my basic thinking.

My idea is to have an access database with two tables. One table will havethe vote written to it and the other table will have the AUTH_USER writtento it when the employee casts their vote. I'm wondering how I would do a
test against that table to see if their name has already been written to
the
table. So, if an employee votes already, when they go to vote again, theiruser logon would be tested against all entries in the table and if it
exists, the vote would not be written and they would be redirected to
another page that politely tells them they've already voted.

I'm hoping there is an easy answer for this. :)

Do a SELECT from the authorization table WHERE the user column is
equal to AUTH_USER, then branch on whether it's null. Or just us an
IF EXISTS in your query if your database supports that and do the
entire thing in a single query. Books Online has examples if you use
SQL Server.

Jeff

I'm using an access 2K database.

I'm trying to do what you suggested but I'm running into some trouble. I'm
getting this error:

a.. Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'SELECT * from voting'
/castvote.asp, line 17

Here's my code:

------------------------- castvote.asp-----------------------------

Dim empname, Conn, SQL1

empname = Request.ServerV ariables("AUTH_ USER")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
server.mappath( "eoty.mdb")

SQL1 = "SELECT * from votingemp where(voter = '" & empname & "')"

Conn.Execute SQL1,,129

'Response.Write (SQL1)

Response.Write( SQL1.Fields("vo ter"))

-------------------------------- end asp -----------------------------

When I comment out the Response.Write( SQL1.Fields("vo ter")) and uncomment
the Response.Write( SQL1), I get this SQL Statement:

SELECT * from votingemp where(voter = 'DOMAIN\jim')

I tested that statement in access and it worked fine.

Line 17 is Response.Write( SQL1.Fields("vo ter"))
My plan, if this was working, was to place the result of the
Response.Write( SQL1.Fields("vo ter")) into a variable like so:

testvariable = Response.Write( SQL1.Fields("vo ter"))

Then do some branching off of that like so:

If testvariable = empname Then
Response.Write( "You already voted")
Else
VoteDB Insert string Here
End If

Am I heading in the right direction?

Thanks,
Jim

Here are some observations:
1. SQL1 is a string, not a recordset object. You need to declare a recordset
object and assign it to the return value of the Conn.Execute call.

2. Please consider explicitly listing the columns in your SQL statement
instead of using "SELECT *". Here's an article that explains the benefits:
http://aspfaq.com/show.asp?id=2096

3. The parenthesis are unnecessary in the WHERE clause of your SQL
statement.

4. The third parameter in the Conn.Execute call is incorrect in this
context. The third parameter of the Execute method is the options parameter
which is a bitmask of command type and execution option values. 129
indicates a command type of text (1) which is correct plus an execution
option of "no records" (128) which is incorrect. So the value of the option
parameter should simply be 1. This is all outlined in the Connection.Exec ute
method documentation:
http://www.msdn.microsoft.com/librar...cnnexecute.asp

5. When dynamically constructing SQL statements, you should take steps to
validate the input. This includes but is not limited to escaping
apostrophes. In the alternative, you may want to consider avoiding dynamic
sql entirely by using a parameterized query.

6. Before attempting to access the recordset object, you should verify that
data was returned by inspecting the Recordset.EOF property.

7. The Response.Write method is a statement, not a function so you don't
need to use parenthesis when making the call.

8. Please consider closing/deallocating objects after your done with them.
Here's an article that explains why:
http://aspfaq.com/show.asp?id=2435

9. The quoted text of this reply has been reordered to preserve the flow of
the thread. When posting replies please consider placing them below the
quoted text or inline.

Here's a revision of your code with a number of the above observations
applied:

<%
Dim empname, Conn, rs, SQL1

empname = Request.ServerV ariables("AUTH_ USER")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
server.mappath( "eoty.mdb")

SQL1 = "SELECT voter from votingemp where voter = '" &
Replace(empname ,"'","''") & "'"
Set rs = Conn.Execute(SQ L1,,1)

'Response.Write (SQL1)
If rs.EOF Then
Response.Write "Record not found"
Else
Response.Write rs.Fields("vote r")
End If

rs.Close : Set rs = Nothing
Conn.Close : Set Conn=Nothing
%>

HTH
-Chris Hohmann

[Jim in Arizona]

"Chris Hohmann" <no****@thankyo u.com> wrote in message
news:ek******** ******@TK2MSFTN GP11.phx.gbl...

"Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:OU******** ******@TK2MSFTN GP11.phx.gbl...

"Jeff Cochran" <je*********@zi na.com> wrote in message
news:41******** ********@msnews .microsoft.com. ..

> On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
> <ti*******@hotm ail.com> wrote:
>
>>I'm wanting to do a simple controlled voting page. I too our webserver off >>anonymous and everyone who accesses the website is a domain authenticated >>user. I've already done some control structure pages based on
>>Request.Serve rVariables("AUT H_USER"), which works great. That's also
>>how I >>would do this page, in my basic thinking.
>>
>>My idea is to have an access database with two tables. One table will have >>the vote written to it and the other table will have the AUTH_USER written >>to it when the employee casts their vote. I'm wondering how I would do
>>a
>>test against that table to see if their name has already been written
>>to
>>the
>>table. So, if an employee votes already, when they go to vote again, their >>user logon would be tested against all entries in the table and if it
>>exists, the vote would not be written and they would be redirected to
>>another page that politely tells them they've already voted.
>>
>>I'm hoping there is an easy answer for this. :)
>
> Do a SELECT from the authorization table WHERE the user column is
> equal to AUTH_USER, then branch on whether it's null. Or just us an
> IF EXISTS in your query if your database supports that and do the
> entire thing in a single query. Books Online has examples if you use
> SQL Server.
>
> Jeff

I'm using an access 2K database.

I'm trying to do what you suggested but I'm running into some trouble.
I'm
getting this error:

a.. Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'SELECT * from voting'
/castvote.asp, line 17

Here's my code:

------------------------- castvote.asp-----------------------------

Dim empname, Conn, SQL1

empname = Request.ServerV ariables("AUTH_ USER")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
server.mappath( "eoty.mdb")

SQL1 = "SELECT * from votingemp where(voter = '" & empname & "')"

Conn.Execute SQL1,,129

'Response.Write (SQL1)

Response.Write( SQL1.Fields("vo ter"))

-------------------------------- end asp -----------------------------

When I comment out the Response.Write( SQL1.Fields("vo ter")) and uncomment
the Response.Write( SQL1), I get this SQL Statement:

SELECT * from votingemp where(voter = 'DOMAIN\jim')

I tested that statement in access and it worked fine.

Line 17 is Response.Write( SQL1.Fields("vo ter"))
My plan, if this was working, was to place the result of the
Response.Write( SQL1.Fields("vo ter")) into a variable like so:

testvariable = Response.Write( SQL1.Fields("vo ter"))

Then do some branching off of that like so:

If testvariable = empname Then
Response.Write( "You already voted")
Else
VoteDB Insert string Here
End If

Am I heading in the right direction?

Thanks,
Jim

Here are some observations:
1. SQL1 is a string, not a recordset object. You need to declare a
recordset
object and assign it to the return value of the Conn.Execute call.

2. Please consider explicitly listing the columns in your SQL statement
instead of using "SELECT *". Here's an article that explains the benefits:
http://aspfaq.com/show.asp?id=2096

3. The parenthesis are unnecessary in the WHERE clause of your SQL
statement.

4. The third parameter in the Conn.Execute call is incorrect in this
context. The third parameter of the Execute method is the options
parameter
which is a bitmask of command type and execution option values. 129
indicates a command type of text (1) which is correct plus an execution
option of "no records" (128) which is incorrect. So the value of the
option
parameter should simply be 1. This is all outlined in the
Connection.Exec ute
method documentation:
http://www.msdn.microsoft.com/librar...cnnexecute.asp

5. When dynamically constructing SQL statements, you should take steps to
validate the input. This includes but is not limited to escaping
apostrophes. In the alternative, you may want to consider avoiding dynamic
sql entirely by using a parameterized query.

6. Before attempting to access the recordset object, you should verify
that
data was returned by inspecting the Recordset.EOF property.

7. The Response.Write method is a statement, not a function so you don't
need to use parenthesis when making the call.

8. Please consider closing/deallocating objects after your done with them.
Here's an article that explains why:
http://aspfaq.com/show.asp?id=2435

9. The quoted text of this reply has been reordered to preserve the flow
of
the thread. When posting replies please consider placing them below the
quoted text or inline.

Here's a revision of your code with a number of the above observations
applied:

<%
Dim empname, Conn, rs, SQL1

empname = Request.ServerV ariables("AUTH_ USER")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
server.mappath( "eoty.mdb")

SQL1 = "SELECT voter from votingemp where voter = '" &
Replace(empname ,"'","''") & "'"
Set rs = Conn.Execute(SQ L1,,1)

'Response.Write (SQL1)
If rs.EOF Then
Response.Write "Record not found"
Else
Response.Write rs.Fields("vote r")
End If

rs.Close : Set rs = Nothing
Conn.Close : Set Conn=Nothing
%>

HTH
-Chris Hohmann

Thanks Chris. That put me in the right direction. I think I can get
accomplished what I've set out to do.

It seems that I'm still trying to get it strait in my head on when and when
not to use parenthesis. I'll get it strait someday.

Thanks for your guidance. I'll put it to good use.

Jim