Case sensitive query

[rjoubert]

VB.NET 2005 thick app querying Excel spreadsheets

I'm using the following code snippet to query an Excel spreadsheet for unique values in a given column. I want the query to be case sensitive (i.e. return 2 rows if values of "Test" and "test" are found). Any suggestions?

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source=" & s_strExcelFile & "; " & _
   "Extended Properties=Excel 8.0;"

strSQL = "SELECT [" & FieldName & "], " & _
   "Count([" & FieldName & "]) as DataCount " & _
   "FROM [TheData] " & _
   "GROUP BY [" & FieldName & "] " & _
   "ORDER BY Count([" & FieldName & "]) DESC"

objConnection = New System.Data.OleDb.OleDbConnection(strConnection)
objDataAdapter = New System.Data.OleDb.OleDbDataAdapter(strSQL, objConnection)

objDataSet = New System.Data.DataSet
objDataAdapter.Fill(objDataSet)

 

[chrissie1]

You aren't using parameters, why?

Christiaan Baes
Belgium

"My old site" - Me

 

[rjoubert]

Would using parameters help with my case sensitive search?

 

[chrissie1]

Nope, but using parameters should be second nature.

Anyway. The case sensitvity depends on excel and the oledb link not vb.net.

So excel is the.

Christiaan Baes
Belgium

"My old site" - Me

 

[Qik3Coder]

after ten seconds of googling here is an answer*

  SELECT *
    FROM tblTable
    WHERE (xField = 'SkyBlue' COLLATE latin1_general_cs_as)

*

http://forum.dragonsfate.com/index.php?showtopic=60&mode=threaded

-The answer to your problem may not be the answer to your question.

 

[rjoubert]

If I'm not using a where clause, how would I do it? I'm just trying to group all values.

 

[chrissie1]

I don't thin the collate thing would work on excel it's a sql-server thing.

Christiaan Baes
Belgium

"My old site" - Me

 

[rjoubert]

I believe you are correct Christiaan. I found the same thing during my Googling, but only saw examples of that COLLATE keyword with SQL Server examples.

 

[Qik3Coder]

Did you try it?

MS wrote excel,
MS wrote the OLEDB adapter,
MS wrote SQL Server and
MS wrote VS2k5.

Chances are somethings spill over.
You'd be suprized with "what works"


If that doesnt work you may want to try doing the "clean up" vb code side.


-The answer to your problem may not be the answer to your question.

 

[rjoubert]

Yes, I did. It gave me a syntax error.

 

[rjoubert]

I can see that this won't be solved using the .NET app, so I've linked to the spreadsheets thru Access and used a custom function that converts the values to ASCII (using the Asc function). That worked fine. Issue closed.