I just want to know the difference of these two in vb.net? Using Data adapter and Using the oledb command?

Question

Private Sub Contestant_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim command As String
        Dim dsSET As New DataSet
        Dim connect As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb")


        command = "SELECT * from Contestant "

        Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(command, connect)

        comSTR = "SELECT * from Contestant "

        dsSET.Clear()
        da.Fill(dsSET, "contest")





        dgvContestant.DataSource = dsSET
        dgvContestant.DataMember = "contest"


    End Sub

I don't understand the above code but it still fetches data from the database and load it to datagridview.

Below is another code but throwing this error: 'Command text was not set for the command object.'

  Private Sub Contestant_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim dsSET As New DataSet
        Dim connect As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb")
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand


        With cmd
            .Connection = connect
            .CommandText = "SELECT * from Contestant "
             Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(command, connect)
            .Connection.Open()
            .ExecuteNonQuery()


            da.Fill(dsSET, "contest")
            dgvContestant.DataSource = "contest"
            .Connection.Close()
        End With




    End Sub

Answer 1

In the second code snippet, you're setting the CommandText of cmd but not setting the CommandText of command. It's command that that you then pass into the data adapter. Why do you have two command objects in the first place? If cmd is the command object whose CommandText you set then surely that should be the command object you pass into the data adapter.

A connection object creates a connection between your application and the database. SQL can be executed over that connection and data passed back and forth.

A command object contains SQL code and, optionally, the parameters for that SQL. A command is always associated with a connection over which it is executed. If the command contains a SELECT statement then you can call ExecuteScalar to retrieve a single value or ExecuteReader to retrieve zero, one or more records containing one or more columns. If the command does not contain a SELECT statement, you can call ExecuteNonQuery.

A data adapter is basically a group of up to four command objects to perform CRUD operations. When you call Fill, the SelectCommand is executed to retrieve data into a DataTable. When you call Update, the InsertCommand, UpdateCommand and DeleteCommand are executed as required to save changes from a DataTable to a database.

When you create a data adapter, you can either provide a ready-made command object for the SelectCommand or let the adapter create one itself. If you do the latter, you can pass the SQL code and an existing connection or you can pass the SQL code and a connection string, in which case the adapter will create the connection object too. A data adapter will not create its own InsertCommand, UpdateCommand and DeleteCommand so you have to create those yourself or, in certain circumstances, you can use a command builder to do it for you.

You might benefit from a look at my ADO.NET examples here.

Answer 2

A command is just a representation of a SQL statement and an associated connection. It can be executed in several ways, by returning a reader with .ExecuteReader, for Insert, Update and Delete statements with .ExecuteNonQuery and to retrieve a single value with .ExecuteScalar.

It can also be used by a DataAdapter.

A DataAdapter can not only .Fill a DataTable or DataSet but also .Update

Comments and explanations in line.

Private Sub Contestant_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'Declares a variable as String
    Dim command As String
    'Creates a DataSet object. Note the New keyword
    Dim dsSET As New DataSet
    'Creates a Connection object and sets the .ConnectionString property by passing it to the Constructor of the object
    Dim connect As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb")
    'Assigns a value to the previously declared String
    command = "SELECT * from Contestant "
    'Creates a DataAdapter object and provides a SQL Select statement that the adapter can use to create its SelectCommand property
    'and sets the .Connection property by passing a Connection object.
    'Note: the connection is NOT an open connection
    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(command, connect)
    'Undeclared and unnecessary variable
    'comSTR = "SELECT * from Contestant "
    'Unnecessary code - You just created, it is already empty
    'dsSET.Clear()
    'Calls the DatAdapter .Fill method passing the DataSet to fill and the name of the DataTable being filled.
    'The .Fill method opens and closes the connection if it finds it closed. If the connection is already open
    'the .Fill method leaves it open.
    da.Fill(dsSET, "contest")
    'The DataSet is set as DataSourd
    dgvContestant.DataSource = dsSET
    'Since a DataSet can contain more than one table; the .DataMember of the DataSet 
    'is set to the name of the DataTable to display.
    dgvContestant.DataMember = "contest"
End Sub

Private Sub Contestant_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim dsSET As New DataSet
    Dim connect As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb")
    Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand
    With cmd
        .Connection = connect
        .CommandText = "SELECT * from Contestant "
        'Here command is not declared
        'Visual Studion assumes you mean Interaction.Command() which is NOT at all want you want
        Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(command, connect)
        .Connection.Open()
        'A DataAdapter does not have a .ExecuteNonQuery method
        '.ExecuteNonQuery belongs to .Command and is used for SQL statements that
        'begin with Insert, Update or Delete.
        .ExecuteNonQuery()
        da.Fill(dsSET, "contest")
        'The .DataSoure of a DataGridView cannot be set to a String
        dgvContestant.DataSource = "contest"
        .Connection.Close()
    End With
End Sub

'I don't think you need a DataAdapter or a DataSet

Private Sub FillDataGridView()
    Dim dt As New DataTable
    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\athan\Documents\PAGEANT.accdb"),
            cmd As New OleDbCommand("SELECT * from Contestant ", cn)
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    'Update the User Interface after the connection is closed.
    dgvContestant.DataSource = dt
End Sub