G
|
gdmacdo 10/5/2012 |
This is an example of what I use to check if a user's email and employee number have already been used and presents a pop up alerting them. Hello, I would like to ask a question , (I'm a complete novice) I am trying to prevent duplications from being entered to my database I need to check 3 fields NAME, DOB, and location I have used the below line from another topic and it works perfect for the first two fields (name and DOB), set rsExists = dal_table.Query("Name='" & values("Name") & "' and DOB='" & values("DOB") & "'","") I just need to add to this to also check location field for a Null value. i.e. (if the location field is NULL and the name and dob are the same "show error message".) Any help greatly appreciated |
M
|
markynewport author 10/5/2012 |
Thanks for fast response, This is an example of what I use to check if a user's email and employee number have already been used and presents a pop up alerting them. dim rsEmail,rsEmployee,rsSchoolCode set dal_table=dal.Table("dbo.AD_Security") set rsEmail = dal_table.Query("Email='" & values("Email") & "'","") set rsEmployee = dal_table.Query("Employee_Number='" & values("Employee_Number") & "'","") if not rsEmail.eof then Flush_output Response.Write "<script>alert('Yikes! - The email address already exists. Please check your spelling/entry.')</script>" elseif not rsEmployee.eof then Flush_output Response.Write "<script>alert('Yikes! - The Employee ID Number already exists. Please check your entry.')</script>" elseif values("YourLocationFieldName")="" then Flush_output Response.Write "<script>alert('Yikes! - YourLocationFieldName does not have a valid entry.')</script>" beforeadd = false else BeforeAdd = true end if |
G
|
gdmacdo 10/5/2012 |
Would this work for you. Basically in the Before Record Added section in the events, if the location field is not blank, go ahead and add the record. If it is blank, check the fields and alert the user. |
M
|
markynewport author 10/5/2012 |
Thanks Greg, Would this work for you. Basically in the Before Record Added section in the events, if the location field is not blank, go ahead and add the record. If it is blank, check the fields and alert the user. dim rsEmail,rsEmployee,rsSchoolCode set dal_table=dal.Table("dbo.AD_Security") set rsEmail = dal_table.Query("Email='" & values("Email") & "'","") set rsEmployee = dal_table.Query("Employee_Number='" & values("Employee_Number") & "'","") if values("YourLocationFieldName") <> "" then BeforeAdd = true else if not rsEmail.eof then Flush_output Response.Write "<script>alert('Yikes! - The email address already exists. Please check your spelling/entry.')</script>" elseif not rsEmployee.eof then Flush_output Response.Write "<script>alert('Yikes! - The Employee ID Number already exists. Please check your entry.')</script>" beforeadd = false else BeforeAdd = true end if end if |
G
|
gdmacdo 10/5/2012 |
Maybe it is something like this then: Thanks Greg, Nearly there, i have changed it a bit so the name and dob can be checked together. dim rsname,rsdob,rsSchoolCode set dal_table=dal.Table("dbo.client_table") set rsname = dal_table.Query("name='" & values("name") & "' and dob='" & values("dob") & "'","") if values("location") <> "" then BeforeAdd = true else if not rsname.eof then Flush_output Response.Write "<script>alert('Yikes! - This record address already exists. Please check your spelling/entry.')</script>" beforeadd = false else BeforeAdd = true end if end if The validation for name and dob works fine it's just the location does not, I just replaced "YourLocationFieldName" with the field name "location" from my table "client_table" if values("location") <> "" then (this checks for duplicates even if the location value is not NULL) I changed if values("location") <> "NULL" then (this let's duplicates be added regardless of location field.) Thanks... |
M
|
markynewport author 10/8/2012 |
Hi Greg, Maybe it is something like this then: dim rsname,rsdob,rsSchoolCode set dal_table=dal.Table("dbo.client_table") set rsname = dal_table.Query("name='" & values("name") & "' and dob='" & values("dob") & "'","") if not rsname.eof and values("location") = "" then Flush_output Response.Write "<script>alert('Yikes! - This record address already exists. Please check your spelling/entry.')</script>" beforeadd = false else BeforeAdd = true end if |
G
|
gdmacdo 10/8/2012 |
I made a test table and put the same information in and it worked. It worked with the double quotes symbolizing the field being empty. I also put in the Add page (in the visual editor) a default value of (the actual word) "NULL" and the action caught it. |
M
|
markynewport author 10/10/2012 |
I made a test table and put the same information in and it worked. It worked with the double quotes symbolizing the field being empty. I also put in the Add page (in the visual editor) a default value of (the actual word) "NULL" and the action caught it. As a recap/scenario, I have one existing record: greg 11/01/1900 new york If I enter: greg 11/01/1900 NULL (the NULL is the default value that is displayed on the page) this will produce the error If I enter: emily 06/01/1900 NULL this will not produce the error because there is not an emily with that birthdate in the system and the record will be added If I enter: greg 11/01/1900 los angeles ***this will work I made sure to capitalize the word NULL in both the default value on the visual editor add page and in the before record added event: dim rsname,rsdob,rsSchoolCode set dal_table=dal.Table("Client_Table") set rsname = dal_table.Query("name='" & values("name") & "' and dob='" & values("dob") & "'","") if not rsname.eof and values("location") = "NULL" then Flush_output Response.Write "<script>alert('Yikes! - This record address already exists. Please check your spelling/entry.')</script>" beforeadd = false else BeforeAdd = true end if |
M
|
markynewport author 11/20/2012 |
Hi Greg, Thanks a million for all your help on this, I realisied the error I made here, I was trying to validate against a field in the table that was not on the add page hence the DAL could not find the field to validate it. |