HelpASisterOut HelpASisterOut -4 years ago 161
Vb.net Question

Invalid attempt to call Read when reader is closed. vb.net

I have this sub in my 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


I am opening the connection before executing the
SqlCommand
,

and closing the connection in the
finally
clause

Still, it is returning the following error everytime this sub is called:

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


Can Anyone help me figure out why?

Any help would be appreciated

Answer Source

Use the Using-statement instead and don't reuse the 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download