空字符串和ado.net [英] empty strings and ado.net

问题描述

我有一个vb应用程序，该应用程序从访问数据库中读取sql语句，然后在执行某些字符串替换后执行sql命令.此应用程序已升级为可与ADO.Net一起使用.

我的问题是在其中一个sql语句中有一个检查"where var <> "X"".在旧版本的应用程序中，它将返回var包含空字符串的行.在ADO.Net版本中，它不返回带有空字符串的行.

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

>

然后，代码将[SESSIONPARAMETER]和[REFERENCELOCATION]替换为适当的值.该代码执行以下代码以执行生成的sql语句:

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

如前所述，在非ADO版本中，当执行sql语句时，我得到的行返回，其中OOVSTATUS是空字符串(<>"X"的检查有效)，但是在此ADO.Net版本中，不返回带有OOVSTATUS的空字符串的行.我还通过一些测试代码验证了数据为空，而不是null.

两种版本的连接字符串为:
"Provider = Microsoft.Jet.OLEDB.4.0;数据源= {0};用户ID = admin;密码="，DBPhysicalFile()

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()

推荐答案

字符串表达式在MS SQL中可以为EMPTY或NULL.

您可以使用 LEN() [

The string expressions in MS SQL can be EMPTY or NULL.

You can use LEN()[^] function to check the length of returned data.
Use a little trick:

SELECT [CHRTV].[OOVSTATUS], LEN([CHRTV].[OOVSTATUS]) AS [LengthOfOOVStat]
FROM ...
GROUP BY [CHRTV].[OOVSTATUS]
ORDER BY LEN([CHRTV].[OOVSTATUS])

或

or

SELECT [CHRTV].[OOVSTATUS]
FROM ...
WHERE LTRIM(RTRIM([CHRTV].[OOVSTATUS]))!=''

检查[OOVSTATUS]是否为空.

to check the [OOVSTATUS] is not empty.

这篇关于空字符串和ado.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！