Exception on Sql Command Object = Nothing

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

Answer 1

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

Answer 2

User-158764254 posted

What would cause an exception to be thrown

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

Answer 3

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