Link to home
Start Free TrialLog in
Avatar of Britt Priddy Jr
Britt Priddy JrFlag for United States of America

asked on

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

Avatar of St3veMax
St3veMax
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of CoveyCraig
CoveyCraig

You should enclose your expiration date in # signs like this:

Response.cookies("CheckForm").expires = #DateAdd("m",1,CookieDate)#
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)#
Whoops lol

dim strDateTime
strDateTime=Now()
strFuture=dateadd("m",1,strdatetime)
Response.cookies("CheckForm").expires = #strFuture#
ASKER CERTIFIED SOLUTION
Avatar of CoveyCraig
CoveyCraig

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Britt Priddy Jr

ASKER

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!
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!!!

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!