CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> .NET

 Prevent duplicate entries

Print topic Send  topic

Author Message
kescott


Posts: 49
Posted: 10/27/2008, 6:37 AM

:-)

I have created an employee entry form that allows a user to enter an
employee's first and last name and address. However, when a user
enters a name that matches a name already in the database then there
should be an error message that alerts the user, for example,"That
name already exists" and the entry will not be allowed.

Any ideas, anyone?
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
Nicole

Posts: 586
Posted: 10/29/2008, 7:01 AM

Hello,
if you need to validate a single entry then just set Unique property of input field to Yes.
In case you'd like to validate a combination of two or more fields (for example first name plus last name) then execute custom count sql query in form's onValidate event. If query returns value greater than "0" then add a new error message to form's errors collection.
_________________
Regards,
Nicole
View profile  Send private message
kescott


Posts: 49
Posted: 10/29/2008, 9:29 AM

Thanks a lot for the information. I have been working on preventing duplicates by checking
the employees lastname, firstname, and date of birth. By counting more than (1) duplicate
each time an entry is made

I tested the following SQL statement in the visual query builder and out of 2000 records I
got 62 that were duplicates. The code is the following...

SELECT emp_lastname,emp_firstname,dob, count(*) FROM Employees GROUP BY  
emp_lastname, emp_firstname,dob HAVING count(*) > 1

I wrote the following code to validate, got an idea what I'm doing incorrectly.



  
if(employeemaintenance.Text = (new TextField("", Settings  
.identification_DEVELOPMENTDataAccessObject.ExecuteScalar  
("SELECT emp_lastname,emp_firstname,dob, count(*) FROM  
Employees GROUP BY emp_lastname, emp_firstname,dob  
HAVING count(*) > 1"))).GetFormattedValue(""));

_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
Vasiliy

Posts: 378
Posted: 10/29/2008, 1:57 PM

Why don't just:

If Convert.ToInt32(....ExecuteScalar("SELECT COUNT(*) AS DupExists FROM Employees WHERE lastname = <???> AND firstname = <???> AND dob = <???>")) > 0 Then
errors.Add("LastName","Nope, this user already exists")
End If

Where <???> is an appropriate value from web-form.

Or even better - modify "RecordDataProviderBase.vb", add SQL Server error parser to Insert, Update, Delete statements:
Ex:

Protected Function ExecuteDelete() As Object
PrepareDelete()
If CmdExecution And IsParametersPassed Then
Try
Return Delete.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception(CreateFriendlyErrorMessage(ex))
End Try
Else
Return 0
End If
End Function


' Process DB Errors ---------------------------------------
Private Function CreateFriendlyErrorMessage(ByVal ex As Exception) As String

If System.Web.HttpContext.Current.Session("DeveloperMode") <> 1 Then
If Regex.IsMatch(ex.Message, "INSERT statement conflicted with") Then
Return "This Record is already in the database."
ElseIf Regex.IsMatch(ex.Message, "UPDATE statement conflicted with") Then
Return "This Record is used in other tables and can not be updated."
ElseIf Regex.IsMatch(ex.Message, "DELETE statement conflicted with") Then
Return "This Record is used in other tables and can not be deleted."
ElseIf Regex.IsMatch(ex.Message, "Cannot insert duplicate key.") Then
Return "This Record is already in the database."
ElseIf Regex.IsMatch(ex.Message, "Cannot insert the value") Then
Return "Please, fill all required fields."
ElseIf Regex.IsMatch(ex.Message, "The transaction ended in the trigger. The batch has been aborted.") Then
Return ex.Message.Replace("The transaction ended in the trigger. The batch has been aborted.","")
End If
End If
Return ex.Message
End Function
' Process DB Errors -----------------------------------end-

_________________
Vasiliy
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.