Problem with select statement

[J L]

In the following code, I am trying to find a record in my Operators
table based on the field ID. I have a record with ID = John (cap J).
The code below returns this record even thoug I did the select on john
with small j. What am I doing wrong?

(Note: this function returns the variable dTable and boolean result if
a record was found, hence the myDT.copy etc.)

TIA,
John (wiht Cap J LOL)

strSQL = "SELECT * FROM Operators WHERE ID = 'john'"
Dim da As New DbDataAdapter = OledDbDataAdapter(strSQL,
"Microsoft.Jet.OLEDB.4.0;user ID=Admin;Data
Source=C:\TestData\Marymonte.mdb")

Dim myDS as New DataSet
Dim myDT as New DataTable

da.Fill(myDS)
myDT = myDS.Tables(0)
if myDT.Rows.Count > 0 then
dTable = myDT.copy
return True
end if

 

[Elton W]

Hi John

Nothing is wrong about your code. Access DB is not case
sensitive.

HTH

Elton Wang
(e-mail address removed)

 

[J L]

Hi Elton,
Wow, thanks. I thought I was going crazy. And all these years using
Access I did not realize that. Is it documented somewhere?

Thanks again,
John

 

[Paul Clement]

¤ Hi Elton,
¤ Wow, thanks. I thought I was going crazy. And all these years using
¤ Access I did not realize that. Is it documented somewhere?
¤

Yes, although I'm not sure whether it's in the Access documentation:

ACC2000: How to Return Case-Sensitive Matches in Queries
http://support.microsoft.com/default.aspx?scid=kb;en-us;209674


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[J L]

Thanks Paul. I used the StrComp to solve the problem. Is this also
compatible with SQL Server should my underlying database change?

John

 

[Paul Clement]

¤ Thanks Paul. I used the StrComp to solve the problem. Is this also
¤ compatible with SQL Server should my underlying database change?
¤

Transact SQL doesn't support this function so you would have to remove it from your SQL statements
or Access QueryDefs when migrating to another database type.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[J L]

Thanks Paul.
I have changed my code to use a normal Select and then compare the
results for case sensitivyt...

SELECT * FROM myTable WHERE ID = 'John'

then compare the result john to John and determine it was wrong.

I do not understand why, if the data stored is case sensitive, the
query would not be. From what I have read, this seems to be a
convention that has been around a while. But it makes no sense to
me...I query for what I want...if I wanted lower case, I would have
quereid for it...oh well,

John

 

[Paul Clement]

¤ Thanks Paul.
¤ I have changed my code to use a normal Select and then compare the
¤ results for case sensitivyt...
¤
¤ SELECT * FROM myTable WHERE ID = 'John'
¤
¤ then compare the result john to John and determine it was wrong.
¤
¤ I do not understand why, if the data stored is case sensitive, the
¤ query would not be. From what I have read, this seems to be a
¤ convention that has been around a while. But it makes no sense to
¤ me...I query for what I want...if I wanted lower case, I would have
¤ quereid for it...oh well,
¤

With SQL Server or Access? I think we've already established that Access does not support case
sensitive based criteria.


Paul
~~~~
Microsoft MVP (Visual Basic)