2. ASPRunnerPro General questions
  3. Prevent duplicate entries
This topic is locked

Prevent duplicate entries

 		9/4/2006 8:23:18 PM
ASPRunnerPro General questions
M
 mfred author

I have created a reservation system for a hunting club. We need to add 1 feature that I am unsure how to create. We need to prevent duplicate bookings. The fields relevant to this is Start Date, End Date, Suite and Bed. I need to add code that will prevent any booking of the bed within the suite withing the date range. Any help is appreciated.
J
 		Jane 9/5/2006

Hi,
you can do it using events.

Proceed to the Events tab, select Before record added event and add your code in it.

Here is a sample code:

Function BeforeAdd(dict)

strSQLExists = "select * from TableName where Suite=" & dict("Suite") &" and Bed = " & dict("Bed") & " and StartDate = '" & dict("Start Date") & "' and EndDate = '" & dict("EndDate") & "'"

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

Response.write "this record exist. Please enter correct data"

BeforeAdd = false
else

' if dont exist do something else

BeforeAdd = True

end if

rsExists.Close : set rsExists = Nothing
End Function



where tableName is your actual table name.
M
 		mfred author 9/5/2006

It did not do anything. I changed the table name and verified the field name. No error occured but duplicate entries still passed through. Any ideas? Can I sent the ASP file. If you can fix this, I can pay.
J
 		Jane 9/6/2006

Hi,
I need to see your table structure and your event code.

You can post it here or send to [email=support@xlinesoft.com]support@xlinesoft.com[/email]
A
 		ashumak 9/15/2006

If the table is Results, and the field I dont want to allow dupl;icates in is Num, how would I chnage the code?
Thanks
Alan
J
 		Jane 9/15/2006

Alan,
if Num field is numeric change your code in the following way:

Function BeforeAdd(dict)

strSQLExists = "select * from Results where Num=" & dict("Num")

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

Response.write "this record exist. Please enter correct data"

BeforeAdd = false
else

' if dont exist do something else

BeforeAdd = True

end if

rsExists.Close : set rsExists = Nothing
End Function
A
 		ashumak 9/15/2006

Sorry, it's a text field set as a us phone number.
Alan
J
 		Jane 9/15/2006

If field type is Text you only need to add single quotes:

Function BeforeAdd(dict)

strSQLExists = "select * from Results where Num='" & dict("Num") &"'"

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

Response.write "this record exist. Please enter correct data"

BeforeAdd = false
else

' if dont exist do something else

BeforeAdd = True

end if

rsExists.Close : set rsExists = Nothing
End Function
A
 		ashumak 9/19/2006

This is what I already have. We send emails out to notify when the lead is added...How do I put two different functions in place, and I guess the duplicate check would need to be first? And would it send an email anyways if the duplicate is found?
Alan
Function BeforeAdd(dict)
' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Add form represented as 'Field name'-'Field value' pair
'** Send email with new data ****

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
message ="Hello "& dict("MonexRep") &",you have a new lead sent by "& dict("CallCenterRep") &". Please check the website for details. The Merchant name is "& dict("Merchant") &",and the number is "& dict("Num") &"."
email=dict("EmailAddy")

email1=dict("MgrsEmail")

subject="New Monex Lead"
sendmail email, subject, message

sendmail email1, subject, message
BeforeAdd = True
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
End Function
Sergey Kornilov admin 9/19/2006

Alan,
put the code that checks for duplicate records in the beginning of that function.
If duplicate is found you can do the following:

BeforeAdd = False

Exit Function


Record won't be added and email won't be sent in this case.
A
 		ashumak 9/19/2006

Like this???
Function BeforeAdd(dict)
strSQLExists = "select * from Results where Num='" & dict("Num") &"'"

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

Response.write "this record already exists. Please enter correct data"

BeforeAdd = false
else

' if dont exist do something else

BeforeAdd = True

end if

rsExists.Close : set rsExists = Nothing
BeforeAdd = False

Exit Function
' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Add form represented as 'Field name'-'Field value' pair
'** Send email with new data ****

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
message ="Hello "& dict("MonexRep") &",you have a new lead sent by "& dict("CallCenterRep") &". Please check the website for details. The Merchant name is "& dict("Merchant") &",and the number is "& dict("Num") &"."
email=dict("EmailAddy")

email1=dict("MgrsEmail")

subject="New Monex Lead"
sendmail email, subject, message

sendmail email1, subject, message
BeforeAdd = True
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
End Function
A
 		ashumak 9/25/2006

Nope. Tried it. Twice it sent me back to login, third time it took the lead.
Suggestions?
Alan
B
 		berkeleyjw 9/28/2006

Alan,
Please post your entire BeforeAdd event code (including the real table/field names, etc.) that is not working. Also let me know what DB platform you are using. I've done some similar work in ASPRunner and would be willing to look the code over for you.

  • Jim
A
 		ashumak 9/28/2006

See above...that's exactly what I tried.
Alan
B
 		berkeleyjw 9/28/2006

Alan,
I'm sorry. I thought you were supposed to be dup-checking against several different fields, but the first poster on this thread was not you.
Try the following code. I always use an intermediate boolean value bOK to process conditional logic and only then I assign that value to BeforeAdd.
Function BeforeAdd(dict)
bOK = true
strSQLExists = "select * from Results where Num='" & dict("Num") &"'"

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

bOK = false

Response.write "this record already exists. Please enter correct data"

end if
rsExists.Close : set rsExists = Nothing
if bOK = true then

'** Send email with new data ****

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
message ="Hello "& dict("MonexRep") &",you have a new lead sent by "& dict("CallCenterRep") &". Please check the website for details. The Merchant name is "& dict("Merchant") &",and the number is "& dict("Num") &"."
email=dict("EmailAddy")

email1=dict("MgrsEmail")

subject="New Monex Lead"
sendmail email, subject, message

sendmail email1, subject, message

end if
BeforeAdd = bOK
End Function