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.