Case sensitivity

[harjeen]

Does anyone know if SQL Server is case sensitive? I ask this because when I perform comparisons between string variables and fields in ADODB Recordsets case does not seem to matter. For example, if I compare hello and HELLO, they are considered the same string. However, my application needs to be case sensitive.

Does anyone know what could be happening, and how I can "turn on" this case sensitivity?

I think it may have something to do with the Option Compare statement, but by default it should be a binary compare.

TIA,

Nahid

[honeybee]

SQL Server is case sensitive. If you want clarification about the framing of queries through VB, just give me an example of how you are writing your queries. Many of us will be able to help you.

[Clunietp]

SQL Server is NOT case sensitive by default. It is an option during installation of SQL, but then your entire SQL Server would be case sensitive (ALL the databases).

You might have to add an extra step in your program to compare the SQL results with your string and make sure they match (VB is case sensitive)

[harjeen]

Clunietp,

Thanks for the help. The thing that is confusing me is that all my comparisons ARE done in VB, and yet even when case doesn't match but the words are the same, the comparison operation returns true. For example, if I do:

if (rcset_master!String = "hello") then
debug.print "True"
end if

and the String field of rcset_master contains "HELLO", "True" will show up in the immediate window.

Any ideas why that happens?

Thanks,

Nahid

[Clunietp]

I don't know, this code works correctly in evaluating the case sensitivity:

    Dim cn As New Connection
    Dim rs As Recordset
    
    'connect to Northwind DB
    cn.Open <ConnectionString>
    
    Set rs = cn.Execute("Select * from Customers")
    
    Debug.Print rs!CustomerID   'will be ALFKI
    
    MsgBox rs!CustomerID = "alfki"  'shows FALSE
    MsgBox rs!CustomerID = "ALFKI"  'shows TRUE

This uses ADO 2.5 and SQL 7 DB

[honeybee]

Your code looks OK. It should work in any 'case'. Just a moment, you are using debug.print, right? Could you try using the 'MsgBox' instead?