>Tim,
>
>FWIW I changed the stored procedure to this:
>
>

CREATE Procedure [dbo].[NewAgentNumber]
>
>-- @NewNumber varchar(10)
>-- OUTPUT
>AS
> SET NOCOUNT ON
> 
> DECLARE @tmpNumber nvarchar(10)
>
> -- find max of Agent Number counter from current table to be used to create 
> -- new id
> SET @tmpNumber = (SELECT MAX(SUBSTRING(AgentNumber, 4, 5) + 1)
>                        FROM Agents)
>
> -- if this is first Agent record then start counter with ....1 else 
> -- whatever the most recent counter
> IF @tmpNumber IS NULL
>    SET @tmpNumber = '00001'
> ELSE 
>    SET @tmpNumber = replicate('0',5-LEN(@tmpNumber)) + 
>        @tmpNumber
> -- store new invoice id to output param
> -- SET @NewNumber = @tmpNumber
> 
>SELECT @tmpNumber as NewNumber

>
>and I changed my code to this:
>
>

                string newNumber = this.ExecSprocScalar("NewAgentNumber").ToString();
>
>                dt.Rows[0]["AgentNumber"] = newNumber;

>
>and that works! Much easier than all the gobbledygook from before.
>
>Now, my next question is: I have set the AgentNumber field to have a UNIQUE constraint, how do I trap if this constraint has caused an error so that I can reset the agent number to blank and let the user save again?
>
>< UPDATE >
>I worked out how to do this by calling my CheckForDuplicates in the business rules and setting the agent number to the empty string.
>
>So I think I am happy with this.

Glad you got that figured out. I wonder if the SELECT in the stored procedure is the key to this.
Tim