Question Convert DropDownList items to lowercase

[slash75]

I have a DropDownList that is being populated from SQL server. Some of the records are in uppercase. I want to convert all of them to lowercase in the dropdown. Is this possible?

            Dim oConn As SqlConnection
            Dim oComm As SqlCommand
            Dim oReader As SqlDataReader
            Dim sSQL As String
            Dim sConn As String

            sSQL = "SELECT fname + ' ' + lname AS EmpName, email, active "
            sSQL += "FROM Employess WHERE active=1 ORDER BY fname ASC"
            sConn = "server=111.11.1.11;database=mydb;Initial Catalog=mydb;User ID=user;Password=pwd"

            oConn = New SqlConnection(sConn)
            oConn.Open()

            oComm = New SqlCommand(sSQL, oConn)
            oReader = oComm.ExecuteReader()          
            
            ddlCoordinator.DataSource = oReader
            ddlCoordinator.DataBind()

            ddlCoordinator.Items.Insert(0, "Select")

 

[Menthos]

Sure is possible. Just modify your query to return lower case results:

            sSQL = "SELECT [B]LOWER(fname + ' ' + lname)[/B] AS EmpName, email, active "
            sSQL += "FROM Employess WHERE active=1 ORDER BY fname ASC"

Hope that helps
Menthos

 

[slash75]

Ok - i see. Thanks for your help.

That converts everything to lower case, but I just realized that the first letter of the first and last names need to be upper case. Ex. Bill Smith

 

[Menthos]

Hehe, then that's what you should have asked for initially

sSQL = "SELECT SELECT UPPER(LEFT(fname,1))+LOWER(RIGHT(fname,LEN(fname)-1)) + ' ' + UPPER(LEFT(lname,1))+LOWER(RIGHT(lname,LEN(lname)-1)); AS EmpName, email, active "
            sSQL += "FROM Employess WHERE active=1 ORDER BY fname ASC"

... basically use upper and lower to give your case. You could of course make a nice little PROPER function to tidy things up.

 

[slash75]

Sorry - I didn't realize it until I actually saw the result.
It is working perfectly now. Thanks for the great example.

 

[MattP]

You could of course make a nice little PROPER function to tidy things up.

Here's a SQL function to return property case so all of the ugly is on the server side rather than in your sSQL variable. There's issues with this function from the once over I gave it.

Less Than Dot - Blog - SQL Server Proper Case Function

Here's a nice tutorial on creating a CLR function in VB.NET and deploying to SQL Server.

SQL Safety: Format names in proper case

Here's a better example of a propery case function than the one provided in the previous link.

Proper Case Or Title Case - VBForums

 

[slash75]

Thanks I will try to move this into a function using the examples.