Adapter Update help!!!!!!!!!

[sujatha]

I am fetching data into a dataset from one data source and trying to
update it to another data source using adapter.update method but this
doesnt work. Doesnt give me an error either!!

my code

MiningDataSet = new DataSet();
DataSet TempDataSet = new DataSet();
DataTable ClusterTable = MiningDataSet.Tables.Add ("Cluster");
ClusterTable.Columns.Add ("NODE_UNIQUE_NAME");
ClusterTable.Columns.Add ("NODE_CAPTION" );
DataColumn NodeDist = ClusterTable.Columns.Add
("NODE_DISTRIBUTION",System.Type.GetType("System.Int32") );
NodeDist.AutoIncrement = true;

DataTable ClusterDistTable =
MiningDataSet.Tables.Add("ClusterDist");
ClusterDistTable.Columns.Add("ATTRIBUTE_NAME");
ClusterDistTable.Columns.Add("ATTRIBUTE_VALUE");
ClusterDistTable.Columns.Add("SUPPORT");
ClusterDistTable.Columns.Add("PROBABILITY");
ClusterDistTable.Columns.Add("VARIANCE");
ClusterDistTable.Columns.Add("VALUETYPE");
DataColumn NodeDistChild =
ClusterDistTable.Columns.Add("NODE_DISTRIBUTION" );
NodeDistChild.DataType = System.Type.GetType("System.Int32");


MiningDataSet.Relations.Add
("NodeDistribution",NodeDist,ClusterDistTable.Columns["NODE_DISTRIBUTION"]);

SelectCommand.CommandType = CommandType.Text ;
SelectCommand.CommandText = "SELECT
NODE_UNIQUE_NAME,NODE_CAPTION,NODE_DISTRIBUTION FROM
[mystores].content";

Adapter.TableMappings.Add ("Table","Cluster");
Adapter.TableMappings.Add
("TableNODE_DISTRIBUTION","ClusterDist");

Adapter.Fill(MiningDataSet);

InsertCommand.CommandType = CommandType.Text ;
InsertCommand.CommandText = "INSERT INTO
Cluster(NODE_UNIQUE_NAME,NODE_CAPTION,NODE_DISTRIBUTION) VALUES
(?,?,?)";
InsertCommand.Parameters.Add
("NODE_UNIQUE_NAME",OleDbType.VarChar,50,ClusterTable.Columns["NODE_UNIQUE_NAME"].ToString()
);
InsertCommand.Parameters.Add
("NODE_CAPTION",OleDbType.VarChar,50,ClusterTable.Columns["NODE_CAPTION"].ToString());
InsertCommand.Parameters.Add
("NODE_DISTRIBUTION",OleDbType.VarChar
,50,ClusterTable.Columns["NODE_DISTRIBUTION"].ToString());
CloseDBConnection();
Conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;pwd=sa12;initial Catalog=IXAppDB;Data
Source=nexdell-05";
OpenDBConnection();
InsertCommand.Connection = Conn;

TempDataSet = MiningDataSet.Clone();
//InsertCommand.CommandText ="select * from organization";
//InsertCommand.ExecuteNonQuery ();
Adapter.Update(TempDataSet,"ClusterDist");

 

[David Sceppa]

It looks like you're trying to use the DataAdapter and
DataTable to synchronize your databases. ADO.NET doesn't
inherently support this functionality. However, with a little
understand of how DataAdapter.Update works, you should be able to
handle the scenario you described.

When you call DataAdapter.Update, it looks checks each row's
RowState and executes the corresponding update command
(InsertCommand/UpdateCommand/DeleteCommand) to submit that row's
pending change. The DataAdapter doesn't understand that the
SelectCommand and InsertCommand pointed at different databases,
or know that you wanted to submit all rows as inserts against the
InsertCommand's Connection. It simply sees that all of the rows
have a RowState of Unchanged and assumes there are no changes to
submit.

If prior to calling DataAdapter.Fill, you set
DataAdapter.AcceptChangesDuringFill to False, the rows you create
by calling DataAdapter.Fill will have a RowState of Added. The
subsequent call to DataAdapter.Update will force the DataAdapter
to execute the InsertCommand for each row.

Things get more complex if the "target" database already
contains rows that you retrieved from the "source" database. If
that's a scenario you need to handle, and you simply want to
overwrite such rows in the "target" database, you could set your
InsertCommand to a query like the following:

IF NOT EXISTS(SELECT ID FROM MyTable WHERE ID = @ID)
BEGIN
-- Insert Scenario
INSERT INTO MyTable (...) VALUES (...)
END
ELSE
BEGIN
-- Update Scenario
UPDATE MyTable SET ... WHERE ...
END

Of course, placing this logic in a stored procedure and
calling the stored procedure from the InsertCommand would be an
even better idea.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[sujatha]

yes!!!!!!11
it worked like a charm.
thanks a zillion