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