当读取器关闭时，调用Read的尝试无效.vb.net

Question

我在WinForm中有这个子窗体:

Public Function CheckSentToday(ByVal date1 As DateTime) As Boolean
    Dim cmSql As New SqlCommand("Check_Today", cnSql)
    Try
        cmSql.CommandType = CommandType.StoredProcedure
        cmSql.Parameters.AddWithValue("@date1", String.Format("{0:yyyy-MM-dd}", date1))
        If Not cnSql Is Nothing AndAlso cnSql.State = ConnectionState.Closed Then cnSql.Open()
        If cmSql.ExecuteScalar IsNot Nothing Then
            Return True
        Else
            Return False
        End If
    Catch ex As Exception
        WriteToText("CheckSentToday", ex.ToString)
        CheckSentToday = False
    Finally
        If Not cnSql Is Nothing AndAlso cnSql.State = ConnectionState.Open Then cnSql.Close()
    End Try
End Function

我在执行SqlCommand之前打开连接，

和关闭finally子句中的连接

但是，每次调用这个sub时，它都会返回以下错误:

Description:System.InvalidOperationException: Invalid attempt to call Read when reader is closed.
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()

有谁能帮我找出原因吗?

如有任何帮助，不胜感激

Answer 1

使用Using -语句代替，不要重用SqlConnection:

Public Function CheckSentToday(ByVal date1 As DateTime) As Boolean
    Using cnSql = New SqlConnection("connection-string")
        Using cmSql As New SqlCommand("Check_Today", cnSql)
            cmSql.CommandType = CommandType.StoredProcedure
            cmSql.Parameters.AddWithValue("@date1", String.Format("{0:yyyy-MM-dd}", date1))
            Try
                cnSql.Open()
                If cmSql.ExecuteScalar IsNot Nothing Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                WriteToText("CheckSentToday: ", ex.ToString)
                CheckSentToday = False
            End Try
        End Using
    End Using
End Function