时间戳

首页 > 解决方案 > SQL DataReader 给出“字符串或二进制数据将被截断”

.net - SQL DataReader 给出“字符串或二进制数据将被截断”

问题描述

我正在尝试读取一个在正常情况下将返回零行的表。如果返回任何行，则会显示错误消息。我已经在 SQL Server 上测试了 SQL 语句，它可以工作。但是，我在 DataReader 中遇到错误。我的代码如下：

Using sqlConn As SqlConnection = New SqlConnection(cSettings.PrimaryConnectionString)
     Dim sSQLc As String = "SELECT * FROM UnitHistory " _
                            + "WHERE Unit=" + iUnitID.ToString + " " _
                            + "AND StartDate<='" + returnSQLDateString(dAdmissionDateTime) + "' " _
                            + "AND (EndDate IS NULL OR " _
                            + "EndDate>'" + returnSQLDateString(dAdmissionDateTime) + "') " _
                            + "AND IsAmended IS NULL AND IsReversed IS NULL"
     Using sqlCMD As SqlCommand = New SqlCommand(sSQLc, sqlConn)
         Using sqlR As SqlDataReader = sqlCMD.ExecuteReader ' <--- (The error is raised at this line)
             If sqlR.HasRows Then
                 ' if rows were returned then there was someone in the unit
                 ' we're trying to admit to.
                 MsgBox("There is an admission already in this unit for this admission date")
                 Exit Sub
             End If
         End Using
     End Using
End Using

生成的示例 SQL 语句是：

SELECT * 
FROM UnitHistory 
WHERE Unit=1 
AND StartDate<='2019-07-14 20:55' 
AND (EndDate IS NULL OR EndDate>'2019-07-14 20:55') 
AND IsAmended IS NULL 
AND IsReversed IS NULL

有问题的列（如在 SQL 语句中）是 int/DateTime/DateTime/Bit/Bit

我不明白为什么这会导致引发错误。

标签： .netvb.net

解决方案

我去了 SQL 管理工作室并在我的一张表上尝试了类似的查询，它很快就指出了问题。它需要额外的括号。

    Using sqlConn As SqlConnection = New SqlConnection(cSettings.PrimaryConnectionString)
     Dim sSQLc As String = "SELECT * FROM UnitHistory " _
                            + "WHERE (Unit=" + iUnitID.ToString + ") " _
                            + "AND (StartDate<='" + returnSQLDateString(dAdmissionDateTime) + "') " _
                            + "AND ((EndDate IS NULL) OR " _
                            + "(EndDate>'" + returnSQLDateString(dAdmissionDateTime) + "')) " _
                            + "AND (IsAmended IS NULL) AND (IsReversed IS NULL)"
     Using sqlCMD As SqlCommand = New SqlCommand(sSQLc, sqlConn)
         Using sqlR As SqlDataReader = sqlCMD.ExecuteReader ' <--- (The error is raised at this line)
             If sqlR.HasRows Then
                 ' if rows were returned then there was someone in the unit
                 ' we're trying to admit to.
                 MsgBox("There is an admission already in this unit for this admission date")
                 Exit Sub
             End If
         End Using
     End Using
End Using

推荐阅读