I got your object reference right here!

Colonel Angus · April 9th, 2004, 09:08 AM

I'm getting the classic "Object reference not set to an instance of an object" error.

Usually this means I forgot to include the keyword "New" in an object declaration. However, this time I'm not seeing it.

This code is from a class method that returns a dataset - it chokes on the connection setting.
Public Function GetInquiriesForEdit(ByVal UserCenterID As Integer) As DataSet
        Dim dsEdits As New DataSet
        Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("C onnectString"))
        Dim cmd As New SqlDataAdapter

        cmd.SelectCommand.Connection = conn
        If UserCenterID = 5 Then 'Central Office
            cmd.SelectCommand.CommandText = "sp_GetCentralInquiries"
        Else
            cmd.SelectCommand.CommandText = "sp_GetInquiries"
        End If

        cmd.SelectCommand.Parameters.Add(New SqlParameter("@UserCenterID", SqlDbType.Int))
        cmd.SelectCommand.Parameters("@UserCenterID").Valu e = UserCenterID
        cmd.SelectCommand.Parameters.Add(New SqlParameter("@SearchText", SqlDbType.VarChar))
        If m_SearchText <> "" Then
            cmd.SelectCommand.Parameters("@SearchText").Value = m_SearchText
        Else
            cmd.SelectCommand.Parameters("@SearchText").Value = " "
        End If
        cmd.Fill(dsEdits, "edits")

        Return dsEdits
    End Function

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Imar · April 9th, 2004, 09:12 AM

Does ConfigurationSettings.AppSettings("ConnectString") return a valid connection string??

Is this in a Code Behind page or in a Class Library?

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Cinderella's big score by Sonic Youth (Track 4 from the album: Dirty boots)

Colonel Angus · April 9th, 2004, 09:33 AM

The connection string is good, I use it in several other places.

This is in a class library.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Imar · April 9th, 2004, 09:38 AM

Did you import the System.Configuration namespace to have ConfigurationSettings accessible?

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Track 11 by Various Artists (Track 11 from the album: Dance - Various - Disc 2)

Colonel Angus · April 9th, 2004, 09:44 AM

I did not - however, I have not needed to do this before (maybe VS.NET takes care of it?)

I am starting to wonder about

Code:

cmd.SelectCommand.Connection = conn

- is that the proper way to assign the connection?

BTW, "Various Artists" is my favortie band :)

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Imar · April 9th, 2004, 09:55 AM

Yeah, I think that's it. The SelectCommand needs to be an object of type SqlCommand:

Code:

Dim cmd As New SqlDataAdapter

Dim MyCommand As New SqlCommand
cmd.SelectCommand = MyCommand

cmd.SelectCommand.Connection = conn

seems to work for me.

I am glad we're sharing the same musical taste... ;) I have some old stuff that comes from cassettes (for those of you younger than say, 20, cassettes are those old fashioned thingies we used back in the 80's to play music) that doesn't have the right ID3 tags.....

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: The Air Near My Fingers by The White Stripes (Track 12 from the album: Elephant)

Colonel Angus · April 9th, 2004, 10:04 AM

I got it. Now the code looks like this:

Code:

Public Function GetInquiriesForEdit(ByVal UserCenterID As Integer) As DataSet
        Dim dsEdits As New DataSet
        Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectString"))
        Dim cmd As New SqlDataAdapter("sp_GetInquiries", conn)

        cmd.SelectCommand.CommandType = CommandType.StoredProcedure
        If UserCenterID = 5 Then 'Central Office
            cmd.SelectCommand.CommandText = "sp_GetCentralInquiries"
        End If

        cmd.SelectCommand.Parameters.Add(New SqlParameter("@UserCenterID", SqlDbType.Int))
        cmd.SelectCommand.Parameters("@UserCenterID").Value = UserCenterID
        cmd.SelectCommand.Parameters.Add(New SqlParameter("@SearchText", SqlDbType.VarChar))
        If m_SearchText <> "" Then
            cmd.SelectCommand.Parameters("@SearchText").Value = m_SearchText
        Else
            cmd.SelectCommand.Parameters("@SearchText").Value = " "
        End If
        cmd.Fill(dsEdits, "edits")

        Return dsEdits
    End Function

I had to use the overload for the adapter, and set the command type enum to Stored procedure. A bit quirky; I have to use the overload, or it still fails.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Imar · April 9th, 2004, 10:25 AM

Right. Looks good. Apparently, when you pass in a sp_GetInquiries name, it creates the SelectCommand automatically for you, right??

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: And So Is Love by Kate Bush (Track 2 from the album: The Red Shoes)

Colonel Angus · April 9th, 2004, 10:52 AM

I think for stored procedures, yes. Otherwise, you have to do it manually.

Billy's Saloon by Gamalon from "Gamalon"

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.