Stuart,

Following up on Kevin's response, I use the business object rules to catch this type of error. The following code is a sample of one instance on how this is done. This code is how I validate that a name of a trader (in a trader table) is not duplicated for a given client (represented by clientID). First, in the business rule, i call a validate method like this:

/// <summary>
        /// Validates the alternate key's uniqueness
        /// </summary>
        public string ValidateUniqueTraderName(string firstName, string lastName, int traderID, int clientID)
        {
            string Msg = null;
            Trader oTrader = new Trader();
            TraderEntity TE = oTrader.GetTraderByClientIDTraderIDAndName(clientID, traderID, firstName, lastName);
            if (TE.HasValues)
            {
                Msg = "This Trader name has already been setup";
                AddErrorProviderBrokenRule("traderLastName", Msg);
            }
            return Msg;
        }

The trader business object method that is called here is as follows:

public TraderEntity GetTraderByClientIDTraderIDAndName(int clientID, int traderID, string firstName, string lastName)
        {
            return this.GetEntity("TraderSelectByClientIDAndName", this.CreateParameter("@ClientID", clientID),
                this.CreateParameter("@TraderID", traderID),
                this.CreateParameter("@FirstName", firstName),
                this.CreateParameter("@LastName", lastName));
        }

And the stored procedure that is called is as follows:

ALTER PROCEDURE [dbo].[TraderSelectByClientIDAndName]
(
	@ClientID int, @TraderID int, @FirstName char(25), @LastName char(25)
)
AS
SET NOCOUNT ON;
	SELECT * 
	FROM [dbo].[Trader]
	WHERE 
		([ClientID] = @ClientID and 
		TraderID <> @TraderID and
		TraderFirstName like rtrim(@FirstName) + '%' and 
		TraderLastName like rtrim(@LastName) + '%')
	order by traderLastName, traderFirstName
GO

The reason I pass and use the traderID column is to allow for the proper checking of the record when saving a record that already has been saved. Otherwise without it this rule would fail on any subsequent save of a record. The traderID column is the PK of the table that is an IDENTITY type integer column. That is why this is checked as not equal (<>) in the WHERE clause.

You can duplicate this for any table passing as many columns as you need to check for duplication. In my case, I am checking for duplication on three columns (clientID, lastName and firstName).

HTH

Kevin,

As a side note, this I would think this type of functionality can be added into the BLG by checking for alternate keys (AK) that have been defined for a table and then building the validate method and stored procedure accordingly. (Maybe you already have this in the works.) :)


>Hi
>
>I have a table that does not allow duplicate records, if I try and enter a duplicate record how do I catch the error and error message?
>TIA
>Stuart