InfoPath Dev

Please could you help me with the following query? Help! I am a junior developer that has recently been 'let loose' on the voyage of discovery and exploration in the exciting field of InfoPath and C# Web Services connecting to a Sql Express Database, and have hit my first challenge. I hope that you could help.

I wish to design a unique Employee Code comprised of the first letter of the Employee Name, followed by the first letter of the Surname and then three random assigned digits e.g. John Smith reads as JS001. How could I do this probably simple exercise? I am struggling to get around it. I need the Employee Code to be inserted into the Database using the web service. When the user queries the InfoPath Form he/ she will use this Employee Code. Also, when adding a new employee a Employee code will automatically be generated using the Surname and First Name Fields.

Just a quick idea:

You can use
System.Environment.UserName.ToString() to get the account name of the currently logged in user.
for John Smith it will be something like jsmith now all you need to do is split/cut this string so that you only have the first two letters left. To auto generate a number you can use the Math.Random method (you should be able to find examples about this in the msdn library).

You can also iterate through your Active Directory accounts and generate your "employee code" for all in once. Search for AD or Active Directory in this forum and you should be able to find some helpful stuff.

Hope this helps a bit

I have solve the code using the following solution. Hope that it helps others in search of similar:

AS
SET NOCOUNT OFF;

SELECT
@EmpCode = max(EmpCode)
FROM
Employees
WHERE
EmpCode like upper(substring(@Firstname,1,1)+substring(@Surname,1,1)) +'%'

IF @EmpCode is null
set @EmpCode = upper(substring(@Firstname,1,1)+substring(@Surname,1,1)) + '001';
ELSE
BEGIN
DECLARE @EmpCodeNo int;
SET @EmpCodeNo = Cast(substring(@EmpCode,3,3) as int) + 1;
SET @EmpCode = upper(substring(@Firstname,1,1)+substring(@Surname,1,1)) + replicate('0',3-len(@EmpCodeNo)) + cast(@EmpCodeNo as varchar);
END

INSERT INTO