Britt Priddy Jr
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.
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.
You should enclose your expiration date in # signs like this:
Response.cookies("CheckFor m").expire s = #DateAdd("m",1,CookieDate) #
Response.cookies("CheckFor
This is all you should need to use time to the second in your cookie:
dim strDateTime
strDateTime=Now()
strFuture=dateadd("m",1,st rdatetime)
Response.cookies("CheckFor m").expire s = #DateAdd("m",1,strDateTime )#
dim strDateTime
strDateTime=Now()
strFuture=dateadd("m",1,st
Response.cookies("CheckFor
Whoops lol
dim strDateTime
strDateTime=Now()
strFuture=dateadd("m",1,st rdatetime)
Response.cookies("CheckFor m").expire s = #strFuture#
dim strDateTime
strDateTime=Now()
strFuture=dateadd("m",1,st
Response.cookies("CheckFor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 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!
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!!
Thanks!!!
ASKER
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!
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.