myAdapter.Fill(_DataSet) with error value can not be null

My code:

Private Sub FillDataSet()
        Dim myConnection As SqlConnection = New SqlConnection(ConnStr)
        Dim myCommand As New SqlDataAdapter("tsp_get_Accounts", myConnection)
        Try
            Dim myAdapter As New SqlDataAdapter()
            myConnection.Open()
            myAdapter.Fill(_DataSet)
            myConnection.Close()
        Catch ex As Exception
            RecordError.Record(ex)
        End Try
    End Sub

it goes wrong on: myAdapter.Fill(_DataSet) with error value can not be null

anyone know why

when i run the tsp in sql query analyzer i get back the entire table...

Answer this question

myAdapter.Fill(_DataSet) with error value can not be null

Jan van der Peet
I'm gonna suggest one more change: there's no reason to actually create the connection--the SqlDataAdapter will do that, if you pass it a connection string. Since the code doesn't use the connection anywhere, creating it is just one more place for things to go wrong. Same with the command. I'd rewrite it like this:

Dim _DataSet As New DataSet()

Private Sub FillDataSet()
  Dim myAdapter As New SqlDataAdapter("tsp_get_Accounts", ConnStr)

  Try
    myAdapter.Fill(_DataSet)
  Catch ex As Exception
    RecordError.Record(ex)
  End Try
End Sub

Obviously, this isn't always the solution (there are times when you DO want explicit command and connection objects, but that didn't appear to be the case in this example), but since we were all rewriting code, figured I'd add my own spin on it <g>.
mango_123
Well, we're sort of going in the opposite direction from a DAL here, in this thread.

<g>
AJCodeFixer
In addition to what Lan Ong posted, you'll also need to make some changes...try this instead:

Private Sub FillDataSet()
Dim myConnection As New SqlConnection(ConnStr)
Dim myCommand As New SqlCommand("tsp_get_Accounts", myConnection)
Dim myAdapter As New SqlDataAdapter(myCommand)

Try
myAdapter.Fill(_DataSet)
Catch ex As Exception
RecordError.Record(ex)
End Try
End Sub
RamyaPatki
How did you declare your _DataSet Try,
Dim _DataSet As New DataSet()
Thanks,
Lan
Bernd
I was going to do that when I posted that last piece of code, but I didn't want to confuse the person who originally posted. ;)

And I think in your example, Ken, it would definitely be OK to do it that way, even if you still needed to do something with say...the SqlCommand, like setting parameters, because you could access them through myAdapter.SelectCommand.Parameters

OK, I'm sure this is information overload, so I'm going to stop now! :p (I was about to point out how using a DAL would make things even easier :p )
myAdapter.Fill(_DataSet) with error value can not be null

Answer this question