SQL SqlDataAdapter with Parameters returning "reference not set"

Question

I'm trying to use a SELECT query with SqlDataAdapter to fill a dataset, the problem is occurring in this parameters.add line. The SqlConnection is OK.

Dim dt As New DataSet()

Using adapter As New SqlDataAdapter("SELECT * from TAB_Movimentos WHERE Banco = @Banco", New SqlConnection(MontaStringConexaoSQLServer()))
    adapter.UpdateCommand.Parameters.Add("@Banco", SqlDbType.Int)
    adapter.Fill(dt)
End Using

Returning:

Referência de objeto não definida para uma instância de um objeto." (BR)

Object reference not set to an instance of an object." (EN)

Caius Jard · Accepted Answer · 2020-04-28 17:21:04Z

1

Indeed; you've set a SELECT query but that doesn't mean that the DataAdapter's UpdateCommand is set to anything

        Dim dt As New DataTable()
        Using adapter As New SqlDataAdapter("SELECT * from TAB_Movimentos WHERE Banco = @Banco", New SqlConnection(MontaStringConexaoSQLServer()))
            adapter.SelectCommand.Parameters.Add("@Banco", SqlDbType.Int).Value = 1234
            adapter.Fill(dt)
        End Using

You don't need to use a DataSet (a collection of datatables) - a DataAdapter knows how to fill a DataTable. Using a dataset for this is like using an array of strings when all you really want is a single string

You also forgot to give the parameter a value. I gave it 1234

answered Apr 28, 2020 at 17:21

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Heitor Badotti Over a year ago

Nice! It worked... One more question, I got confuse about DataSet, I understood your explain but I tried to googled it without success...

Black Light Over a year ago

You can think of a DataTable as a thing which has zero or more rows. You could also think of a DataSet as a thing that has zero or more DataTables. A DataAdapter can "fill" and empty DataTable, but it can also fill an empty DataSet (in which case you get a DataSet with a single DataTable containing your rows). If you just have a single table, fill a DataTable.

Caius Jard Over a year ago

A DataSet is like an array of DataTables and is the conceptual equivalent of an entire database. A DataTable is like an array of DataRows and is the equivalent of a single DB table. If you want a DataTable, use a DataTable, not a DataSet. After all, if you want a single string you write Dim s as String = "Hello World", you don't write Dim s(100) as New String s(0) = "Hello World". No-one make an array of 100 strings if all they want is a string. You'll nearly never need to use a DataSet if you only user use a DataAdapter, so if nothing else, just remember "DataTable, not DataSet"

Collectives™ on Stack Overflow

SQL SqlDataAdapter with Parameters returning "reference not set"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related