Binding ddlList with Title case data.

Posted by Ashokn under Sql Server on 1/31/2011 | Points: 10 | Views : 1132 | Status : [Member] | Replies : 2
Hi,

I have All the emp names in sqlServer in CAPS. I need to bind below ddList with the All the Names(except one name "RAJKUMAR") in Title-case.

Create PROCEDURE [dbo].[SP_ReservoirNamesInDDL]
AS
BEGIN
SET NOCOUNT ON;
declare @ResName varchar(20)

SELECT @ExceptThis = EmpName FROM Employee
if(@ExceptThis != 'RAJKUMAR')
SELECT substring(EmpName,1,1)+Lower(substring(EmpName,2,10)), EmpID as EmpName FROM Employee
else
SELECT EmpName,EmpID FROM Employee
END


<asp:DropDownList ID="ddlEmpID" runat="server" DataSourceID="SqlEmployeeDtls" OnDataBound="ddlEmpID_DataBound"
CausesValidation="True" OnSelectedIndexChanged="ddlEmpID_SelectedIndexChanged"
DataTextField="EmpName" DataValueField="EmpID">
</asp:DropDownList>

please suggest the solution (without changing anything in DB table).

thanks in advance...




Responses

Posted by: PandianS on: 1/31/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Try this...!
IF OBJECT_ID('[DBO].[USP_ReservoirNamesInDDL]','P') IS NOT NULL

DROP PROC [DBO].[USP_ReservoirNamesInDDL]
GO
CREATE PROCEDURE [DBO].[USP_ReservoirNamesInDDL]
AS
BEGIN
SET NOCOUNT ON
SELECT Empid, LEFT(EmpName,1) + LOWER(SUBSTRING(EmpName,2,LEN(EmpName))) [EmpName] FROM Employee(NOLOCK) WHERE EmpName<>'RAJKUMAR'
UNION
SELECT Empid, EmpName FROM Employee(NOLOCK) WHERE EmpName='RAJKUMAR'
ORDER BY EmpName
END
GO
Result
Empid	EmpName

2 Anand
5 Ramkumar
1 Jone
6 Joseph
3 RAJKUMAR
4 Ramu
Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ashokn on: 2/1/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Pandian,
Thanks lot it exactly solved my problem.Keep helping .
May I know in which case we use [NOLOCK]?

Thanks again.

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response