Oledb Case When in Select Query

Case Sensitivity when querying SQL Server 2005 from .NET using OleDB

I suspect that this is a procedure cache issue. One benefit of stored procedures is that the plan is stored for you, which speeds things up. Unfortunately, it's possible to get a bad plan in the cache (even when using dynamic queries).

Just for fun, I checked my procedure cache, ran an adhoc query, checked again, then I ran the same query with different capitlization and I was surprised to see the procedure count higher.

Try this....

Connect to SQL Server Management Studio.

DBCC MemoryStatus

Select Columns... From TABLES.... Where....

dbcc MemoryStatus

Select Columns... From tables.... Where....

dbcc MemoryStatus

I think you'll find that the TotalProcs changes when the statement changes (even when the only change is case sensitive).

Updating your statistics may help. That is a rather slow running process, so you may want to run that during a slow period.

Query comparing values for case sensitive

You can use this option that will also increase security for SQL Injection

cmd.CommandText = "SELECT UserName, Password, ID FROM AdminLogin WHERE StrComp(UserName,'" + txtUserName.Text + "',0)=0 AND StrComp(Password,'" + txtPassword.Password + "',0)=0 ";

Excel ACE.OLEDB: COUNTIF equivalent in query

Count will just count things. You should do a SUM

SUM(CASE WHEN Description="Work" THEN 1 ELSE 0 END) 

If it is not work, it sums a 0, otherwise a 1.

Looking more at your tags, you mention Excel. You might need to change it to

SUM( IIF( Description="Work", 1, 0) ) 

Does MS Access support CASE WHEN clause if connect with ODBC?

You could use IIF statement like in the next example:

SELECT
   IIF(test_expression, value_if_true, value_if_false) AS FIELD_NAME
FROM
   TABLE_NAME

Using C# to SELECT against Excel, how do I make it case-INsensitive?

[COLLATE SQL_Latin1_General_CP1_CI_AS] only works in SQL Server. From what I can tell from your question, you're not using SQL Server; you're using an OLEDB data source to query an Excel file, in which case UCASE should work:

if (f.vs(firstName))
    select += "UCase([First Name]) like \"%" + firstName.ToUpper() + "%\" and ";
if (f.vs(lastName))
    select += "UCase([Last Name]) like \"%" + lastName.ToUpper() + "%\" and ";

In-line Table Value Function with Recursive CTE using ADO with OLE DB

It appears we were using an outdated version of ADO.

---

Related Topics