empty strings and ado.net

See more:

VB

SQL

ADO.NET

I have a vb application that reads sql statements from an access database, then execute the sql commands after performing some string substitutions. This application has been upgraded to work with ADO.Net.

My problem is that in one of the sql statements there is a check 'where var <> "X"'. In the old version of the application, it would return the rows where var contains an empty string. In the ADO.Net version, it does not return the rows with empty string.

The sql statements have not changed, just the engine/tool to get to the data and process the statements.

Why does ADO.Net act differently and how can I fix this? Is there a setting somewhere that tells it to process empty strings?

Here is the query that I extract from the database.

INSERT INTO [CHRTV-SUB1] ( DSRECNUMFIX, SumOfACV, SumOfAHELO, SumOfARTY, SumOfAVLB, SumOfBT, SumOfCAIR, SumOfCSHELO, SumOfLAL, SumOfPTAIR, SumOfRCCTAIR, SumOfUTHELO, EQTABBR, EQTRECNUM, V_CATORD, [SESSION] )
SELECT CHRTV.DSRECNUMFIX, Sum(CHRTV.ACV) AS SumOfACV, Sum(CHRTV.AHELO) AS SumOfAHELO, Sum(CHRTV.ARTY) AS SumOfARTY, Sum(CHRTV.AVLB) AS SumOfAVLB, Sum(CHRTV.BT) AS SumOfBT, Sum(CHRTV.CAIR) AS SumOfCAIR, Sum(CHRTV.CSHELO) AS SumOfCSHELO, Sum(CHRTV.LAL) AS SumOfLAL, Sum(CHRTV.PTAIR) AS SumOfPTAIR, Sum(CHRTV.RCCTAIR) AS SumOfRCCTAIR, Sum(CHRTV.UTHELO) AS SumOfUTHELO, CHRTV.EQTABBR, CHRTV.EQTRECNUM, EQPMT.[5_CATORD], CHRTV.SESSION
FROM (CHRTV INNER JOIN (SELECT [EQTRECNUM], [SESSION], [5_CATORD]
FROM [EQUIPMNT], (SELECT * FROM [EQTCAT_ORD] IN [REFERENCELOCATION] WHERE [TREATY] = 'CFE') [REFEQTORD]
WHERE ([EQUIPMNT].[EQTCAT] =[REFEQTORD].[CAT])) [EQPMT] 
ON (CHRTV.EQTRECNUM = EQPMT.EQTRECNUM) AND (CHRTV.SESSION = EQPMT.SESSION)) 
WHERE (([CHRTV].[SESSION]=[SESSIONPARAMETER]) AND ([CHRTV].[OOVSTATUS]<>'X'))GROUP BY CHRTV.DSRECNUMFIX, CHRTV.EQTABBR, CHRTV.EQTRECNUM, EQPMT.[5_CATORD], CHRTV.SESSION

Then the code replaces the [SESSIONPARAMETER] and [REFERENCELOCATION] with the appropriate values. The code performs the following code to execute the resulting sql statement:

Public Function ExecStatement(ByVal sql As String) As Boolean
    Dim cmd As New OleDbCommand
    Dim rtn As Boolean = False
    Dim closeOnExit As Boolean = Not _connOpen
    Try
      If Not _connOpen Then OpenConnection()
      cmd.Connection = _cnn
      cmd.CommandText = sql
      cmd.ExecuteNonQuery()
      rtn = True
    Catch ex As Exception
      Throw New Exception(String.Format("Failed to execute statement. {0}SQL: {1}{0}Message: {2}", vbCrLf, sql, ex.Message))
    Finally
      If closeOnExit Then CloseConnection()
    End Try
    Return rtn
  End Function

As I mentioned earlier, in the non-ADO version, when the sql statement is executed I get rows returned where the OOVSTATUS is an empty string (the check of <>"X" works) but in this ADO.Net version the rows that have an empty string for OOVSTATUS are not returned. I have also verified through some test code that the data is empty, not null.

The connection string for both versions is:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID=admin; Password=", DBPhysicalFile()