The DataAdapter.SelectCommand property needs to be initialized

[Guest]

I am new to ADO.NET, but am experienced in ADO and VB 6.0.

I am writing some procedures for use with an Access database. The particular
procedure I am writing now is designed to create a new SQL command and update
the DataAdapter, then return the filled DataSet. However, I am getting the
error "The DataAdapter.SelectCommand property needs to be initialized." when
the command "odbSFB.GetUpdateCommand()" is run.

I have found that to fix this I need to put "odbSFB = New
OleDb.OleDbCommandBuilder(odaSFB)" before the line. But, I am not sure the
consequence of this. As I understand it this will create another instance of
the CommandBuilder, is this correct? I would think that all I need to do is
update the CommandBuilder with the new SQL command, but cant find how to do
this/

I have attached the code for this procedure below.

Any help, suggestions or questions appreciated.

Thank you.

Tippy.


Code:

Friend Function DBCreateDataSet(ByVal sSQLString As String, ByVal
sRecordSetName As String) As DataSet

' create new dataset and return

' set OleDBCommand to execute SQL commands
cmdSFB = New OleDb.OleDbCommand(sSQLString, odcSFB)

' set new data adapter with command
odaSFB = New OleDb.OleDbDataAdapter(cmdSFB)

' set command builder to automatically generate the SQL commands
' needed to update the database later
If odbSFB Is Nothing Then
odbSFB = New OleDb.OleDbCommandBuilder(odaSFB)
Else
' this is the line needed to avoid the error.
odbSFB = New OleDb.OleDbCommandBuilder(odaSFB)
End If

' you can call GetUpdateCommand explicitly to generate the Update
' command based on the current CommandText property value.
odbSFB.GetUpdateCommand()

' create new dataset
DBCreateDataSet = New DataSet

' fill dataset
odaSFB.Fill(DBCreateDataSet, sRecordSetName)

End Function

 

[Jason L James]

Tippy,

if the command builder object is in scope in your function then you
might be able to use the


myCommandBuilder.RefreshSchema()

method. If it is out of scope then you will need to instantiate a new
instance of the CommandBuilder class as you have done in your
code below.

Once you have done that you can go ahead and update your
data adapter.

I believe that the

myCommandBuilder.GetUpdateCommand()

just returns the command, and does not do anything
to it. Once you have created your command builder then
you can perform your update.

myDataAdapter.update(myDataSet, myTable)

Hope that helps,

Jason.

 

[chetansehgal]

Same selectcommand property problrm!! please help

Hi all,
I am using datagridview control to view and update my data with access database. but i get this " dataadapter.selectcommand property must be initialized!!..heres my code..please help me out with this!

public partial class Form1 : Form

{

OleDbDataAdapter dp = new OleDbDataAdapter();

OleDbConnection con = new OleDbConnection();

BindingSource bsource = new BindingSource();

DataSet set = new DataSet();

OleDbCommandBuilder command = new OleDbCommandBuilder();

string sel;



public Form1()

{

InitializeComponent();

}

public void Loaddata()

{

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\web1\\desktop\\newdb.mdb";

sel = "SELECT * FROM newtable";

dp = new OleDbDataAdapter("SELECT * FROM newtable",con);



OleDbCommandBuilder command = new OleDbCommandBuilder(dp);



dp.SelectCommand = new OleDbCommand();

dp.SelectCommand.CommandText = sel;

dp.SelectCommand.Connection = con;



dp.Fill(set, "newtable");

bsource.DataSource = set.Tables["newtable"];

dataGridView1.DataSource = bsource;

}

public void button2_Click(object sender, EventArgs e)

{





dp.UpdateCommand = command.GetUpdateCommand(); // THIS GIVES ERROR

dp.UpdateCommand.Connection = con;

dp.Update(set,"newtable");



}

private void button1_Click(object sender, EventArgs e)

{

Loaddata();

}



Thank you very much!!