locked
Exception on Sql Command Object = Nothing RRS feed

  • Question

  • User368742804 posted

    What would cause an exception to be thrown on 

    cmdGetImageList = Nothing
            Dim dsImageList As DataSet = New DataSet
            Dim daImageList As SqlDataAdapter = New SqlDataAdapter
            Dim cnn = New SqlConnection(GetConnectionStringFromConfig())
            Try
                If cnn.State <> ConnectionState.Open Then cnn.Open()
                ' Stock dropdown of existing images
                cmdGetImageList.Connection = cnn
                If Session("FirmID") Is Nothing Then Session("FirmID") = GetFirmIDFromCookie().ToString
                cmdGetImageList.Parameters("@FirmID").Value = CInt(Session("FirmID"))
                daImageList.SelectCommand = cmdGetImageList
                daImageList.Fill(dsImageList, "Images")
                With ddlImages
                    .DataSource = dsImageList
                    .DataMember = "Images"
                    .DataTextField = "ImageName"
                    .DataValueField = "ImageID"
                    .DataBind()
                End With
    
                If cnn.State = ConnectionState.Open Then cnn.Close()
                cmdGetImageList = Nothing 
    
            Catch ex As Exception
                Elmah.ErrorSignal.FromCurrentContext.Raise(ex)
            Finally
    
            End Try

    Thanks

    Friday, October 24, 2014 9:31 AM

Answers

  • User281315223 posted

    You might want to consider using a Using statement here to avoid any issues with objects being disposed at inopportune times :

    Try
            ' Build your connection '
            Using cnn As New SqlConnection(GetConnectionStringFromConfig())
                   ' Open your connection
                   cnn.Open()
    
                   ' Define your your command here '
                   Using cmdGetImageList As New SqlCommand("Query",cnn)
                         ' Ensure your Session exists
                         If Session("FirmID") Is Nothing Then Session("FirmID") = GetFirmIDFromCookie().ToString
    
                         ' Set you parameter '
                         cmdGetImageList.Parameters.AddWithValue("@FirmID",CInt(Session("FirmID"))
    
                         ' Build a dataset and an adapter to execute '
                         Dim dsImageList As DataSet = New DataSet
                         Dim daImageList As SqlDataAdapter = New SqlDataAdapter
    
                         daImageList.SelectCommand = cmdGetImageList
                         daImageList.Fill(dsImageList, "Images")
                         With ddlImages
                             .DataSource = dsImageList
                             .DataMember = "Images"
                             .DataTextField = "ImageName"
                             .DataValueField = "ImageID"
                             .DataBind()
                         End With
                  End Using
            End Using
    Catch ex As Exception
            Elmah.ErrorSignal.FromCurrentContext.Raise(ex)
    End Try

    It might help if we could get a bit more information on exactly what you are trying to accomplish, however a using statement will eliminate the need for explicitly closing and disposing of your various objects (connections, commands, etc.)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 26, 2014 9:30 AM

All replies

  • User2103319870 posted

    You can try using the Dispose method to dispose the SQLCommand Object instead of setting it to nothing

    Dim dsImageList As DataSet = New DataSet
    
            Dim daImageList As SqlDataAdapter = New SqlDataAdapter
            Dim cnn = New SqlConnection(GetConnectionStringFromConfig())
            'Declare SQLCommand Object here
            Dim cmdGetImageList As SqlCommand = New SqlCommand()
            Try
                If cnn.State <> ConnectionState.Open Then cnn.Open()
                ' Stock dropdown of existing images
                cmdGetImageList.Connection = cnn
                If Session("FirmID") Is Nothing Then Session("FirmID") = GetFirmIDFromCookie().ToString
                cmdGetImageList.Parameters("@FirmID").Value = CInt(Session("FirmID"))
                daImageList.SelectCommand = cmdGetImageList
                daImageList.Fill(dsImageList, "Images")
                With ddlImages
                    .DataSource = dsImageList
                    .DataMember = "Images"
                    .DataTextField = "ImageName"
                    .DataValueField = "ImageID"
                    .DataBind()
                End With
    
                If cnn.State = ConnectionState.Open Then cnn.Close()
                cmdGetImageList.Dispose()
    
            Catch ex As Exception
                Elmah.ErrorSignal.FromCurrentContext.Raise(ex)
            Finally
    
            End Try

    Friday, October 24, 2014 9:49 AM
  • User-158764254 posted

    What would cause an exception to be thrown

    Please share the details of the specific exception that was thrown.

    Sunday, October 26, 2014 9:12 AM
  • User281315223 posted

    You might want to consider using a Using statement here to avoid any issues with objects being disposed at inopportune times :

    Try
            ' Build your connection '
            Using cnn As New SqlConnection(GetConnectionStringFromConfig())
                   ' Open your connection
                   cnn.Open()
    
                   ' Define your your command here '
                   Using cmdGetImageList As New SqlCommand("Query",cnn)
                         ' Ensure your Session exists
                         If Session("FirmID") Is Nothing Then Session("FirmID") = GetFirmIDFromCookie().ToString
    
                         ' Set you parameter '
                         cmdGetImageList.Parameters.AddWithValue("@FirmID",CInt(Session("FirmID"))
    
                         ' Build a dataset and an adapter to execute '
                         Dim dsImageList As DataSet = New DataSet
                         Dim daImageList As SqlDataAdapter = New SqlDataAdapter
    
                         daImageList.SelectCommand = cmdGetImageList
                         daImageList.Fill(dsImageList, "Images")
                         With ddlImages
                             .DataSource = dsImageList
                             .DataMember = "Images"
                             .DataTextField = "ImageName"
                             .DataValueField = "ImageID"
                             .DataBind()
                         End With
                  End Using
            End Using
    Catch ex As Exception
            Elmah.ErrorSignal.FromCurrentContext.Raise(ex)
    End Try

    It might help if we could get a bit more information on exactly what you are trying to accomplish, however a using statement will eliminate the need for explicitly closing and disposing of your various objects (connections, commands, etc.)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 26, 2014 9:30 AM