Count(*) using Enterprise Library

Question

Hi all,

Does anyone have any idea how to use count(*) in a dynamic query with the Enterprise Library Data Access Application Block?

I am using:

Public Shared Function selectCount(ByVal code As String) As Integer
    Dim query As String = "SELECT COUNT(*) " & _
                          "FROM " & _
                            "Data " & _
                          "WHERE " & _
                            "Code = '" & code & "'"
    Dim db As Database = DatabaseFactory.CreateDatabase()
    Return db.ExecuteScalar(System.Data.CommandType.Text, query)
End Function

This error gets thrown:

The ITransactionLocal interface is not supported by the 'Microsoft.Jet.OLEDB.4.0' provider. Local transactions are unavailable with the current provider.

Answer 1

Have you tried creating an SQLCommand and then use Cmd.ExecuteScalar?

Also, you're using a variable in your query! This definitely requires a parameterized SQLCommand just to avoid SQL Code injection! Doesn't matter if you can guarantee it's safe or not. Best practice is to always use parameters instead. (Why? Because another developer who peeks at your code will learn by example, and you don't want them to learn bad practices!)

Have you tried adding "OLE DB Services=-4" to your connection string already? I've heard that this might solve this transaction error. :-) (Well, I admit, I've edited this answer after reading your comment.) Anyway, this option is related to transaction enlistment of the database. You're overriding the setting by adding this value. (Yeah, I shamelessly googled for it.) It seems that your OLE DB services have been modified or perhaps they're just different because you're on a different system.

Answer 2

I looked up the error message and found that by adding "OLE DB Services=-4" to my connection string cleared up the problem while using the code above. So it seems my code was not technically wrong, just some problem with the connection string (although it was working fine before!)