> My apologies to you Thomas. it has been a long weekend and I
> could have been more complete in the question. I am sorting the data
> (ORDER BY) in SQL, but .NET does not see the data coming in from the
stream
> as ordered. It is mostly sorted but there are some items "out of
order".
> I decided to create a test program to see which collations worked and
> which did not:
keep in mind that other colocations have case sensitivity
embedded in them as well!
> Option Strict On
> Option Explicit On
>
> Imports System
> Imports System.data.sqlclient
> Imports System.Globalization
>
> Module Module1
>
> Private _Connection As SqlConnection
>
> Sub Main()
>
> Try
> _Connection = New SqlConnection _
>
> ("server=(local);uid=sa;pwd=pass4jcs;database=ISI;Max Pool
> Size=200")
^^^^^^^^
please change your password.
> _Connection.Open()
> TryCollation("SQL_Latin1_General_Cp1_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1_CI_AS")
> TryCollation("Latin1_General_CS_AS")
> TryCollation("Latin1_General_CI_AS")
> TryCollation("SQL_Latin1_General_Cp1250_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1250_CI_AS")
> TryCollation("SQL_Latin1_General_Cp1251_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1251_CI_AS")
> TryCollation("SQL_Latin1_General_Cp1253_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1253_CI_AS")
> TryCollation("SQL_MixDiction_Cp1253_CS_AS")
> TryCollation("SQL_AltDiction_Cp1253_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1253_CI_AI")
> TryCollation("SQL_Latin1_General_Cp1254_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1254_CI_AS")
> TryCollation("SQL_Latin1_General_Cp1255_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1255_CI_AS")
> TryCollation("SQL_Latin1_General_Cp1256_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1256_CI_AS")
> TryCollation("SQL_Latin1_General_Cp1257_CS_AS")
> TryCollation("SQL_Latin1_General_Cp1257_CI_AS")
> Catch myException As Exception
> Console.WriteLine(myException.ToString)
> Finally
> _Connection.Close()
> End Try
> Console.Read()
>
> End Sub
> Private Function TryCollation(ByVal theCollation As
> String) As Int32
>
> Dim myCompare As Int32
> Dim myErrorCount As Int32 = 0
> Dim myDataReader As SqlDataReader
>
> Try
> Dim mySQL As String = "SELECT
> CONVERT(varchar(255), CT.[Name]) "
> _
> & " COLLATE {0} AS AddRemoveName" _
> & " FROM [Application List] AL" _
> & " INNER JOIN [Count of Installed Applications] CT ON
> CT.AppKeyID = AL.AppKeyID" _
> & " WHERE(CT.[Name] Is Not NULL)" _
> & " ORDER BY AddRemoveName"
> Dim myCommand As New SqlCommand(String.Format(mySQL,
> theCollation), _Connection)
> myDataReader = myCommand.ExecuteReader()
> Dim myLastName As String = ""
> Dim myCurrentName As String = ""
> While myDataReader.Read
> myCurrentName = myDataReader.GetString(0).Trim
> myCompare = String.Compare(myLastName,
> myCurrentName, True,
> CultureInfo.InvariantCulture)
If you use a case insensitive compare, do you get the same
errors? (your code will also give 1 error at first, unless the read name
is "", you have to read 2 names up front.)
Also, the collations you specify do relate to cultures. So
always comparing them to the invariant culture is nonsense. Some
collations define codepages with characters not available in the
invariant culture (like islandic) See hte documentation of
CultureInfo.InvariantCulture.
> Select Case myCompare
> Case Is < 0, Is = 0
> ' this is goodness
this can be caused by case sensitivity, or by usage of a
different codepage than the 'invariant culture'. If you're using the
invariant culture ONLY in your .net code, you NEED to use the default
collation in sqlserver as well.
FB
--------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog : http://weblogs.asp.net/FBouma
Microsoft MVP (C#)
--------------------------------------------------------------------
===================================
This list is hosted by DevelopMentorŪ http://www.develop.com
Some .NET courses you may be interested in:
NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles
http://www.develop.com/courses/gaspdotnetls
View archives and manage your subscription(s) at http://discuss.develop.com
