redirect if record already exists in database

Discussion in 'ASP General' started by Jim in Arizona, Jan 5, 2005.

  1. 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.ServerVariables("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
     
    Jim in Arizona, Jan 5, 2005
    #1
    1. Advertising

  2. Jim in Arizona

    Jeff Cochran Guest

    On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
    <> 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.ServerVariables("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
     
    Jeff Cochran, Jan 6, 2005
    #2
    1. Advertising

  3. 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.ServerVariables("AUTH_USER")

    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Provider=Microsoft.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("voter"))

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

    When I comment out the Response.Write(SQL1.Fields("voter")) 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("voter"))


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

    testvariable = Response.Write(SQL1.Fields("voter"))

    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" <> wrote in message
    news:...
    > On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
    > <> 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.ServerVariables("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, Jan 6, 2005
    #3
  4. "Jim in Arizona" <> wrote in message
    news:...
    > "Jeff Cochran" <> wrote in message
    > news:...
    > > On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
    > > <> 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.ServerVariables("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

    >
    > 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.ServerVariables("AUTH_USER")
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "Provider=Microsoft.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("voter"))
    >
    > -------------------------------- end asp -----------------------------
    >
    > When I comment out the Response.Write(SQL1.Fields("voter")) 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("voter"))
    >
    >
    > My plan, if this was working, was to place the result of the
    > Response.Write(SQL1.Fields("voter")) into a variable like so:
    >
    > testvariable = Response.Write(SQL1.Fields("voter"))
    >
    > 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.Execute
    method documentation:
    http://www.msdn.microsoft.com/library/en-us/ado270/htm/mdmthcnnexecute.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.ServerVariables("AUTH_USER")

    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    server.mappath("eoty.mdb")

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

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

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

    HTH
    -Chris Hohmann
     
    Chris Hohmann, Jan 6, 2005
    #4
  5. "Chris Hohmann" <> wrote in message
    news:...
    > "Jim in Arizona" <> wrote in message
    > news:...
    >> "Jeff Cochran" <> wrote in message
    >> news:...
    >> > On Wed, 5 Jan 2005 13:59:28 -0700, "Jim in Arizona"
    >> > <> 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.ServerVariables("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

    >>
    >> 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.ServerVariables("AUTH_USER")
    >>
    >> Set Conn = Server.CreateObject("ADODB.Connection")
    >> Conn.Open "Provider=Microsoft.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("voter"))
    >>
    >> -------------------------------- end asp -----------------------------
    >>
    >> When I comment out the Response.Write(SQL1.Fields("voter")) 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("voter"))
    >>
    >>
    >> My plan, if this was working, was to place the result of the
    >> Response.Write(SQL1.Fields("voter")) into a variable like so:
    >>
    >> testvariable = Response.Write(SQL1.Fields("voter"))
    >>
    >> 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.Execute
    > method documentation:
    > http://www.msdn.microsoft.com/library/en-us/ado270/htm/mdmthcnnexecute.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.ServerVariables("AUTH_USER")
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > server.mappath("eoty.mdb")
    >
    > SQL1 = "SELECT voter from votingemp where voter = '" &
    > Replace(empname,"'","''") & "'"
    > Set rs = Conn.Execute(SQL1,,1)
    >
    > 'Response.Write(SQL1)
    > If rs.EOF Then
    > Response.Write "Record not found"
    > Else
    > Response.Write rs.Fields("voter")
    > 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
     
    Jim in Arizona, Jan 6, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chad Crowder
    Replies:
    7
    Views:
    21,561
    hemant.yadav
    Oct 24, 2009
  2. Alvin Bruney [MVP]

    Re: Class already exists ... help please!!

    Alvin Bruney [MVP], Apr 6, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    2,512
    Alvin Bruney [MVP]
    Apr 6, 2004
  3. George
    Replies:
    1
    Views:
    6,209
    Jamie
    Feb 25, 2005
  4. Alan Silver
    Replies:
    2
    Views:
    408
    Alan Silver
    Sep 29, 2005
  5. Thorsten Meininger
    Replies:
    1
    Views:
    463
    Sudsy
    Oct 13, 2004
Loading...

Share This Page