| Author |
Messages |
|
rickwp
Posts:20
 |
| 01/09/2008 7:43 PM |
|
I'm trying to figure out how I can use a custom validator to prevent duplicate entries into the database based on a text field. I've been able to get the validator to work when adding a record, but upon updating a record it finds there is already a record that contains the same text as my LinkURL field, so the update throws a duplicate error. The sub routine that validates the entry is below. Basically, the sql select statement would need to have a WHERE statement that would compare the current record key which is ItemID to any match in the database and exclude it from the compare, so the where statement would look like "WHERE ItemID <> txtItemID". However, I can't figure out how to pass the txtItemID into the validator sub routine. It just gives errors that the variable doesn't exist. I tried using the @ItemID from the stored procedures, but got the error that it needed to be declared, and when I declared it in the sub it again threw an error. Does anyone know how to accomplish this? Where could I set up a variable that contains the ItemID/Primary Key field or where would I declare it to make it available to the validator.
Private Sub CustomValidator1_ServerValidate(ByVal _
source As System.Object, ByVal args As _
System.Web.UI.WebControls.ServerValidateEventArgs) _
Handles CustomValidator1.ServerValidate
Dim nameData As New System.Data.DataSet
Dim connString As String = "Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;"
Dim dbConnection As New SqlConnection(connString) ' = New SqlClient.SqlConnection(dsn)
Dim Query As String
Query = "Select [LinkURL] FROM ITGArticles"
Dim dbCommand As New SqlCommand(Query, dbConnection)
Dim sqlDataAdapter As New SqlDataAdapter(dbCommand)
sqlDataAdapter.Fill(nameData)
Dim dv As New DataView
dv = nameData.Tables(0).DefaultView
Dim datarow As DataRowView
Dim txtLinkURL As String
args.IsValid = True ' Assume True
' Loop through table and compare each record against user's entry
For Each datarow In dv
' Extract link from the current row
txtLinkURL = datarow.Item("LinkURL").ToString()
' Compare Link against user's entry
If txtLinkURL = args.Value Then
args.IsValid = False
Exit For
End If
Next
End Sub
Thanks,
Rick |
|
|
|
|
rickwp
Posts:20
|
| 01/10/2008 8:57 PM |
|
I wasn't able to get the current record ID number passed through to the variable, at least not the way I wanted. I was told that was impossible on another forum. So, instead I added the following at the beginning:
Dim objITGResources As New ITGResourcesInfo
objITGResources.ItemID = ITGResourcesId
If Null.IsNull(objITGResources.ItemID) Then
It seems this was able to pass through and at least let the function know if this was adding a new record or modifying an old one. Here, if there is a value, then it's an update and the validation database lookup is skipped and ends the function. If it's null, then it does the lookup for a duplicate entry in the database. It all makes no sense to me, but it works. New records get validated, updates don't. That will work as a work around the issue.
Rick |
|
|
|
|
rickwp
Posts:20
|
| 01/10/2008 9:06 PM |
|
Here is the final code put together in case someone else can make use of it at some point. It basically checks the field with the custom validator for an existing record in the database with a duplicate entry in the same field. This is updated to connect to the database via the connection string in the web.config file, the above code didn't do that.
In file Addxxx.ascx.vb:
Private Sub CustomValidator1_ServerValidate(ByVal source As System.Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles CustomValidator1.ServerValidate
Dim objITGResources As New ITGResourcesInfo
objITGResources.ItemID = ITGResourcesId
If Null.IsNull(objITGResources.ItemID) Then
Dim urllinkData As New System.Data.DataSet
Dim connString As String = Config.GetConnectionString
Dim dbConnection As New SqlConnection(connString) ' = New SqlClient.SqlConnection(dsn)
Dim Query As String
Query = "Select [LinkURL] FROM ITGArticles" 'replace with your field and table name appropriately
Dim dbCommand As New SqlCommand(Query, dbConnection)
Dim sqlDataAdapter As New SqlDataAdapter(dbCommand)
sqlDataAdapter.Fill(urllinkData)
Dim dv As New DataView
dv = urllinkData.Tables(0).DefaultView
Dim datarow As DataRowView
Dim txtLinkURL As String
args.IsValid = True ' Assume True
' Loop through table and compare each record against user's entry
For Each datarow In dv
' Extract link from the current row
txtLinkURL = datarow.Item("LinkURL").ToString()
' Compare Link against user's entry
If txtLinkURL = args.Value Then
args.IsValid = False
Exit For
End If
Next
End If
End Sub
And the line from Addxxx.ascx that uses the validator:
Enjoy,
Rick |
|
|
|
|
|