databinding and updating sample

[Grant]

I need some sample code showing how to manipulate data in my access database
using C#. This is what Im trying to do:

Dropdownlist with datagrid both bound to datasource. When the drop down list
value changes - the datagrid values change accordingly. I managed to get
this working by setting the currencymanager to the dropdownlist
selectedIndex (which is probably the wrong way to do it, bu the datagrid
seems to update its values correctly)

Im stuck with the Updatecommand, deleteCommand part of things. I set my
update comand to something like this "update Something set somethingElse = ?
WHERE this = ? AND that = ?" I get an error message with the update.

Please opint me in the direction of smoe sample code. My internet connection
runs like a 3 legged dog and I get too frustrated waiting for the inevitable
internet timeout.

Thanks heaps,
Grant

 

[Emma Middlebrook]

Hi,

What is your datasource? Is it a dataset or datatable that you have
filled with the contents of your database table?

If you use a dataset you can edit the data locally in your dataset and
then commit the changes to the database from the dataset.

 

[Grant]

Hi thanks for the reply,
I believe its both. Here is my code which I cannot get to update, it throws
the error: "No value given for one or more required parameters"

------------------Code start------------------------------
private void OpenDataset()
{
//Connection string
string connection = @"Provider=Microsoft.Jet.OLEDB.4.0; "+
@"Data Source=C:\stock.mdb";

// Setup DB-Connection
OleDbConnection conn = new OleDbConnection(connection);

//SQL Select string
//string query = "SELECT * FROM [Component-Codes]";

// Fill the Dataset with Compnent details, map Default Tablename
// "Table" to "Components".
adapter = new OleDbDataAdapter("SELECT * FROM [Component-Codes]",conn);
adapter.TableMappings.Add("Table","Components");
adapter.Fill(dset);

// Fill the Dataset with Compnent details, map Default Tablename
// "Table" to "Products".
adapter2 = new OleDbDataAdapter("SELECT * FROM [Product-Codes]",conn);
adapter2.TableMappings.Add("Table","Products");
adapter2.Fill(dset);

// Fill the Dataset with Compnent details, map Default Tablename
// "Table" to "components".
adapter3 = new OleDbDataAdapter("SELECT * FROM
[Product-Component-Junction]",conn);

//---------------Test area------------------------
adapter3.UpdateCommand = new OleDbCommand("UPDATE
[Product-Component-Junction] SET Quantity = ? " +
"WHERE [Component-Code] = ? AND [Product-Code] = ?" , conn);

//Add Parameters and set values.
//adapter3.UpdateCommand.Parameters.Add("@Country",OleDbType.VarChar,
15).Value = "UK";

//selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value =
"UK";
//selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value =
"London";


//adapter3.UpdateCommand = new OleDbCommand("UPDATE
[Product-Component-Junction] SET Quantity = ? " , conn);
//---------------Test area------------------------

adapter3.TableMappings.Add("Table","prod-comp-junction");
adapter3.Fill(dset);

// Establish the Relationship "ProdComp"
// between Products ---< prod-comp-junction
System.Data.DataRelation ProdComp;
System.Data.DataColumn col_Products;
System.Data.DataColumn col_JunctionTable_Prods;
col_Products = dset.Tables["Products"].Columns["Code"];
col_JunctionTable_Prods =
dset.Tables["prod-comp-junction"].Columns["Product-Code"];
ProdComp = new System.Data.DataRelation("ProdComp",col_Products,
col_JunctionTable_Prods);
dset.Relations.Add(ProdComp);

// Establish the Relationship "ProdComp"
// between Components ---< prod-comp-junction
System.Data.DataRelation Component_Product_Junction;
System.Data.DataColumn col_Components;
System.Data.DataColumn col_JunctionTable;
col_Components = dset.Tables["Components"].Columns["Code"];
col_JunctionTable =
dset.Tables["prod-comp-junction"].Columns["Component-Code"];
Component_Product_Junction = new
System.Data.DataRelation("Component_Product_Junction",col_Components,col_JunctionTable);
dset.Relations.Add(Component_Product_Junction);

dsView = dset.DefaultViewManager;

// Grid Databinding
dataGrid1.DataSource = dsView;
dataGrid1.DataMember = "Products.ProdComp";

//--------------------Combobox---------------
comboBox1.DataSource = dset.Tables["Products"];
comboBox1.DisplayMember = "Code";
comboBox1.ValueMember = "Products.Code";

//--------------------Combobox---------------

}

private void openDataSet()
{
string connection = @"Provider=Microsoft.Jet.OLEDB.4.0; "+
@"Data Source=C:stock.mdb";
string query = "SELECT * FROM [Component-Codes]";

OleDbConnection conn = new OleDbConnection(connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(query, conn);
adapter.Fill(dset);

}

private void BindControls()
{
comboBox1.DataSource = dset.Tables["Table"];
comboBox1.DisplayMember = "Code";

}


private void comboBox1_SelectedIndexChanged(object sender,
System.EventArgs e)
{

CurrencyManager cm =
(CurrencyManager)this.BindingContext[dsView,"Products"];
cm.Position = comboBox1.SelectedIndex;
}

private void comboBox1_BindingContextChanged(object sender, EventArgs e)
{
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dset,"Products"];
cm.Position = cm.Count + 1;

}

private void btnUpdate_Click(object sender, System.EventArgs e)
{
try
{
//adapter.Update(dset);
//adapter2.Update(dset);
adapter3.Update(dset);
}
catch(Exception exc)
{
catchError(exc);
}
}
-----------------------Code end-----------------------------------

 

[Emma Middlebrook]

Have you tried using the OleDbCommandBuilder to help you construct the
other statements? That might help you eliminate the possibility that
your SQL syntax might be wrong..?

The error seems to think that you have not supplied enough parameters
for the update..

Here's a sample from msdn on how it works:

OleDbConnection myConn = new OleDbConnection(myConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
myDataAdapter.SelectCommand = new OleDbCommand(mySelectQuery,
myConn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(myDataAdapter);

myConn.Open();

DataSet ds = new DataSet();
myDataAdapter.Fill(ds, myTableName);

//code to modify data in DataSet here

//Without the OleDbCommandBuilder this line would fail
myDataAdapter.Update(ds, myTableName);

myConn.Close();

return ds;