• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

ASP - Check if a form has already been submitted by the same user within a specified time frame using seconds

I have an application that a user submits that stores unique identifiable information about them as well as the full date/time.  How can I query the SQL Database (2000) to see if that user has submitted an application with the past 30 to 60 seconds?  I can't seem to grasp using DateDiff in the SQL Query.

Goal:  If I can find the unique identifier, like a social security number and say for example the form was submitted 04/30/2009 4:24:15 PM .  I want them to wait at least say 60 seconds before they can submit the form again - and stop processing of the form if a record is found as well as alerting them, they must wait at least xx amount of time before submitting the form again.

I feel like I am close - but stuck.  I know it will have to do with using the DateDiff and Now function but I am uncertain as to where or how to do it / use these functions; there is a high possibility that the unique number is in the database more than once - I just use that being it's unique and need to compare when the form was submitted to the current time.

Reasoning:  Prevent users from using their back buttons to resubmit the form -

Layout:  Form Page   - after clicking submit - it goes to a checkform.asp gathering all the data and cleaning it up - then inserting it into the database, but if an error is found - for example they did not fill out a certain field or used the wrong format - I have this checkform.asp response.write a message to them - letting them know about the error and respond.end to end the processing.  If all is good - it will insert into the database - and then pass on a couple of the fields variables to a confirmation page - thanking them for filling out the form.

Tried:
Setting Sessions :   Session("SubmittedForm") = 1 on the confirmation page
and on the main form I use:
IF Session("SubmittedForm") = 1 THEN
      Response.Write("You have already submitted this form")
      Session.Timeout = 1
      Respond.End
ELSE
      ...continue with code / form ...
END IF

I have tried cookies but I can't seem to use minutes or seconds in a cookie - if it is possible I can't figure it out.

No matter what method used below - people are still able to resubmit the form over and over, however when I try it - it tells me what I want to see.

Also note I am using the response.end when checking / validating the form data because if they did have an error and I response.Redirect them back to the form with a query string explaining the error - it would completely wipe out all of what they have already typed.

So in summary - the General function of the form is:
form.asp  (actual form)

checkform.asp (validates / cleans up data before inserting into the database)

confirmation.asp (Tells them thank you and send an email notification that the form has been submitted, as well as a confirmation to the user - and sets the Session("SubmittedForm") = 1 and Session.Timeout = 1

Pasting some code I have tried in my header using cookies but doesn't seem to work.
CookieDate = Minute(Now)
 
** NOTE: the "/form.asp" actually has a true path like "/myforms/form.asp" - just making it simple.
 My Current SQL Query:
 
SSNum  = Request("SocialSecurityNumber")
 
*UPDATED - Pasted wrong SQLCMD!
 
SqlCmd2 = "SELECT FullDate, SocialSecurityNumber, RowID FROM FormEmployeeApplication WHERE FullDate < '" & minute(Ch1) & "' AND SocialSecurityNumber = '" & SSNum & "'"
 
 
IF Request.ServerVariables("SCRIPT_NAME") = "/form.asp" THEN 
 
IF Request.Cookies("CheckForm").HasKeys THEN
	Response.Write("<b>Your form has already been submitted. </b><br><br><br>")
	Response.Write("	.... ")
	Response.End
ELSE	
 
IF Request.ServerVariables("SCRIPT_NAME") = "/confirmation.asp" THEN
      Response.cookies("CheckForm")("Submitted") = "Yes"
      Response.cookies("CheckForm").expires = DateAdd("m",1,CookieDate)
     Session.Timeout = 1
END IF
	
    END IF
END IF
CheckCookie = Request.Cookies("CheckForm").HasKeys
 
 
In the Form.asp :
 
IF CheckCookie = False THEN
	Response.Write("Form Session Set")
ELSE
	Response.Write("Form has been submitted.  You must close your web browser.")
	Response.Write("<br><br>Or you may refresh this page after 60 seconds. ")
	Response.End
END IF
 
IF Session("AppSubmitted") = 1  THEN   '  *commented out - OR CheckCookie = True THEN
	Response.Write("<b>Your form has already been submitted. </b><br><br><br>")
	Session.Timeout = 1
	Response.End
ELSE
 
.... Continue with displaying the form to be filled out.

Open in new window

0
Britt Priddy Jr
Asked:
Britt Priddy Jr
  • 6
  • 3
2 Solutions
 
St3veMaxCommented:
Within the data you're inserting; do you have a unique value and a datetime time field?

If so, you could write something like:

SELECT X FROM Inserted WHERE X IN (Select X from YourTable WHERE DATEDIFF(SS, YourDate, GetDate()) < 30

then break out into your "you've already inserted" else insert statement.
0
 
CoveyCraigCommented:
You should enclose your expiration date in # signs like this:

Response.cookies("CheckForm").expires = #DateAdd("m",1,CookieDate)#
0
 
CoveyCraigCommented:
This is all you should need to use time to the second in your cookie:

dim strDateTime
strDateTime=Now()
strFuture=dateadd("m",1,strdatetime)
Response.cookies("CheckForm").expires = #DateAdd("m",1,strDateTime)#
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
CoveyCraigCommented:
Whoops lol

dim strDateTime
strDateTime=Now()
strFuture=dateadd("m",1,strdatetime)
Response.cookies("CheckForm").expires = #strFuture#
0
 
CoveyCraigCommented:
Here, I tested this, it works:


strSubmitted=Request.Cookies("CheckForm")("Submitted")
If strSubmitted<>"" then
	Response.Write	"Submitted on:  " & strSubmitted
else
	dim strDateTime
	strDateTime=Now()
	strFuture=dateadd("m",1,strdatetime)
	Response.Cookies("CheckForm").expires = strFuture
	Response.Cookies("CheckForm")("Submitted") = strDateTime
end if

Open in new window

0
 
Britt Priddy JrAuthor Commented:
I appreciate all the info on the cookies, however people are getting past this - so it would seem the best / solid solution is doing it server side as St3veMax has displayed.

To answer your question - I do have a unique # and the full date/time i.e.  RS("FullDate") = Now when inserting.

I have never heard of parts of this query - or if you're just using examples

SELECT X FROM Inserted WHERE X IN (Select X from YourTable WHERE DATEDIFF(SS, YourDate, GetDate()) < 30

So how would that reflect ?  Like -

SELECT FullDate, SSN, AppID FROM  ???xx???  WHERE ... actually that query lost me  :p

I thought DateDiff - first value must be a constant - like "m" or "yyyy" in quotes or single
What are you referring to on the DATEDIFF(SS .... ) ?  SS = ?  

YourDate - would be the "FullDate" stored in the database and I get the rest, but would I not have to break out with the WHERE '" & DATEDIFF(SS, YourDate, GetDate()) < 30 & "'

Maybe if you can confirm the SS - that is what is throwing me.

Thanks!
0
 
CoveyCraigCommented:
Here, using a database version....
strSql="SELECT FULLDATE FROM TABLENAME WHERE SSN='" & SSN & "'"
set rsDate=my_Conn.Execute(strSql)
if not rsDate.EOF then
	strFullDate=rsDate("FullDate")
	rsDate.Close
end if
set rsDate=Nothing
If DateAdd("m",1,strFullDate)>Now() then
	Response.Write	"Submitted on:  " & strFullDate
else
	dim strDateTime
	strDateTime=Now()
	strSql="UPDATE TABLENAME SET FULLDATE='" & strDateTime & "' WHERE SSN = '" & SSN & "'"
	my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
 
end if

Open in new window

0
 
CoveyCraigCommented:
Left off connection information....  Change "YourDB" in line 2 to the name of your own database.


set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("YourDB.mdb")
strSql="SELECT FULLDATE FROM TABLENAME WHERE SSN='" & SSN & "'"
set rsDate=my_Conn.Execute(strSql)
if not rsDate.EOF then
	strFullDate=rsDate("FullDate")
	rsDate.Close
end if
set rsDate=Nothing
If DateAdd("m",1,strFullDate)>Now() then
	Response.Write	"Submitted on:  " & strFullDate
else
	dim strDateTime
	strDateTime=Now()
	strSql="UPDATE TABLENAME SET FULLDATE='" & strDateTime & "' WHERE SSN = '" & SSN & "'"
	my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
 
end if 

Open in new window

0
 
Britt Priddy JrAuthor Commented:
I apologize for the delay I will give these suggestions a shot today and respond.  Work has been kicking me all over (Local Government) - new Chief of Police, etc...  So allot of changes.  Just wanted to bump it so you guys knew I didn't forget about your posts.

Thanks!!!

0
 
Britt Priddy JrAuthor Commented:
Thank you CoveyCraig for all your examples.  I am using both methods that shows as accepted - both work great!  This provides a backup using "server side" vs. sessions / disabled cookies, etc...  Much appreciated!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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