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