none
SqlDataReader question - how to trap exception RRS feed

  • Question

  • I have a SqlDataReader and I want to trap any exceptions with Try Catch Finally if the reader = cmd.ExecuteReader() statement fails. If this statement throws an exception do I need to close the reader? If so, where. Wherever I place the reader.close() statement I get an error that the reader has not been assigned a value yet.

    Also if I never reader.read() the reader do I still have to close it? I am simply inspecting reader.HasRows.

    Thursday, April 7, 2011 5:35 PM

Answers

  • You must always close the SqlDataReader. Simple way to handle this is use the "using" block:
    Using rdr As SqlDataReader = cmd.ExecuteReader()
    
     if rdr.HasRows Then
    
      isMatch = True
    
     end
    
    End Using ' The End Using will close the reader, even if there is an exception, which is what you want
    
    

    Another option is use the cmd.ExecuteScalar, then you don't need to create a SqlDataReader at all.  cmd.ExecuteScalar will return a single value from a single resultset query.  Use the count(*) SQL to get one row, like below:

    Dim strSQL As String = "SELECT count(*) FROM dbo.ClaimantAgencyProfile WHERE AP_Userid = '" & userid & "' COLLATE SQL_Latin1_General_CP1_CS_AS and AP_Pwd = '" & pwd & "' COLLATE SQL_Latin1_General_CP1_CS_AS" 
    
    
    
    if (cmd.ExecuteScalar > 0) then
    
      isMatch = true
    
    end
    
    

     

    Sunday, April 10, 2011 10:35 PM
    Moderator

All replies

  • Hi,

    Answering your question:

    Q. If this statement throws an exception do I need to close the reader?

    Ans: Yes. you should make use of TRY-CATCH (TRY-CATCH-FINALLY) block and specifically use SQLException class for trapping the right error.

    Q. Wherever I place the reader.close() statement I get an error that the reader has not been assigned a value yet

    Ans: you should place the reader.close() into ether CATCH (if you are not using finally) or FINALLY block. if you are getting error above statement then enclose it in if(reader.IsDBNull()) {not very sure on this}. Last but not least don't forget to use reader.dispose()

    Q. Also if I never reader.read() the reader do I still have to close it? I am simply inspecting reader.HasRows

    Ans: I feel this MSDN article or this thread can put more light

     


    Manish Patil http://patilmanishrao.wordpress.com Posting is provided AS IS with no warranties, and confers no rights.

    Manish Patil's Blog

    ↑ Grab this Headline Animator

    Friday, April 8, 2011 8:03 AM
  • Thanks for answering, unfortunately it did not help. Her is my code:

    Dim

    con As New SqlConnection(WebConfigurationManager.ConnectionStrings("ClaimantDataConnString").ConnectionString)

    con.Open()

     

    Dim strSQL As String = "SELECT * FROM dbo.ClaimantAgencyProfile WHERE AP_Userid = '" & userid & "' COLLATE SQL_Latin1_General_CP1_CS_AS and AP_Pwd = '" & pwd & "' COLLATE SQL_Latin1_General_CP1_CS_AS"

     

    Dim cmd As New SqlCommand(strSQL, con)

     

    Dim rdrAgyProfile As SqlDataReader

     

    Try

       rdrAgyProfile = cmd.ExecuteReader()

     

       If rdrAgyProfile.HasRows Then

          isMatch =

    True

       End

    If

       rdrAgyProfile.Close()

    Catch ex As SqlException

       rdrAgyProfile.Close()

       Dim strErrorMessage =

    "Error 1: There was a problem accessing the database."

       Dim strErrorTitle =

    "Database Error"

       ErrorMessage(strErrorMessage, strErrorTitle)

    Finally

       'rdrAgyProfile.Close()

       con.Close()

    End Try

    The reader.close() in the Catch block gets an error that the reader has not been assigned a value and hence may result in a Null referencev exception at runtime. Same error if I place it in the Finally block. What is the problem?

    Friday, April 8, 2011 12:27 PM
  • I am guessing that if the ExecuteReader() statement fails then the reader never gets created and hence there is nothing to close?
    Friday, April 8, 2011 2:55 PM
  • anybody?
    Friday, April 8, 2011 5:32 PM
  • You must always close the SqlDataReader. Simple way to handle this is use the "using" block:
    Using rdr As SqlDataReader = cmd.ExecuteReader()
    
     if rdr.HasRows Then
    
      isMatch = True
    
     end
    
    End Using ' The End Using will close the reader, even if there is an exception, which is what you want
    
    

    Another option is use the cmd.ExecuteScalar, then you don't need to create a SqlDataReader at all.  cmd.ExecuteScalar will return a single value from a single resultset query.  Use the count(*) SQL to get one row, like below:

    Dim strSQL As String = "SELECT count(*) FROM dbo.ClaimantAgencyProfile WHERE AP_Userid = '" & userid & "' COLLATE SQL_Latin1_General_CP1_CS_AS and AP_Pwd = '" & pwd & "' COLLATE SQL_Latin1_General_CP1_CS_AS" 
    
    
    
    if (cmd.ExecuteScalar > 0) then
    
      isMatch = true
    
    end
    
    

     

    Sunday, April 10, 2011 10:35 PM
    Moderator