Case Sensitivity Issue

[dorkboy]

I finally got my query to work the way I want in SQL Server 2005 by adding a COLLATE SQL_Latin1_General_CP1_CS_AS.

However, when I move my SQL Statement into my vb.net code, I am getting different results in my Combo Box. It is like the Combo Box does not recognize the Case Sensitivity.

Code:

Imports System.Data.SqlClient

Dim CityDataSet As New DataSet()
Dim da As SqlDataAdapter

Dim cn As New SqlConnection()

cn.ConnectionString = Global.xxx.My.MySettings.Default.ConnectionString1
cn.Open()

da = New SqlDataAdapter("SELECT DISTINCT CONVERT(varchar(50), City) COLLATE SQL_Latin1_General_CP1_CS_AS AS City FROM Address ORDER BY City", cn)

da.Fill(CityDataSet, "Address")

CityComboBox.DataSource = CityDataSet.Tables("Address")
CityComboBox.DisplayMember = "City"
CityComboBox.ValueMember = "City"

cn.Close()


Thanks in advance

 

[cjard]

It is like the Combo Box does not recognize the Case Sensitivity.

Please explain this sentence?

 

[dorkboy]

When I run this query in a SQL Server window:

SELECT DISTINCT CONVERT(varchar(50), City) COLLATE SQL_Latin1_General_CP1_CS_AS AS City FROM Address ORDER BY City

Among the rows returned will be

boston
Boston

However, when the code runs in the application, the Combo box will only show:

Boston

This is what I mean by the Combo Box is NOT recognizing the Case Sensitivity.

Thanks.

 

[cjard]

I can think of no reason why this would be. VB is case sensitive. Use the debugger to inspect the datatable that youre using as the datasource for the datatable.

If it really doesnt contain the values, you might want to check whether the database connection string contains something that is turning off the case sensitivity so your select distinct query is only returning Boston