Newbie Questions

Re: Problematic SQL Data Retrieval

lyntonS — Thu, 18 Jan 2007 06:38:21 GMT

Thanks for the help. I was able to solve the problem using the following SQL Express Insert Query:

...
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 ...

I included it so that if anyone else new to the topic could learn from it.

Chow!

Re: Problematic SQL Data Retrieval

Matt Faus — Thu, 11 Jan 2007 18:28:19 GMT

Hi Lynton and welcome to the developing community - it's a wonderous place!

The first problem is pretty interesting and I spent a little bit of time trying to figure the exact statement out and I got close but not 100%. The only problem is a type casting issue. You should be able to cast the number to a string, and then you'll need to add logic to add the zero-padding if necessary. The statement I made is pasted below. You should put this statement in the formula section of the 'Computer Column' property for the column in SQL Server Management Studio.

The second problem is a repeat of your other post to which I have already reply.

	upper(substring(T.FirstName, 1, 1))
+ upper(substring(T.SurName,1,1))
+ ((select count(*) from Names where upper(substring(FirstName,1,1)) = upper(substring(T.FirstName,1,1)) and upper(substring(SurName,1,1)) = upper(substring(T.SurName,1,1)))+1)

Problematic SQL Data Retrieval

lyntonS — Thu, 11 Jan 2007 08:01:49 GMT

I have been asked to design a staffing solution for a local company using InfoPath and a Web Service which connects to a SQL Express Database. There are two things I am struggling with as I am currently new to the developing community.

1.) I need to generate a unique id. based on the first letter of the SQL field 'FirstName' and the first letter of 'Surname', followed by three incrementing numbers. e.g John Smith would be JS001, with the next alphabetically assigned person becoming JS002. Sounds simply enough by I am struggling immensely with it. HELP!

2.) I am able to submit the form record successfully to the SQL database. The problem which arises is as follows. InfoPath Drop-Down List values which are successful posted to SQL are not being retrieved when I submit the query based on the 'empCode' of the record. Only the default 'Select...' appears? All the other data is being retrieved and displayed as normal.