By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,920 Members | 1,905 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,920 IT Pros & Developers. It's quick & easy.

Returning pk value to DataTable

P: n/a
Hi all,

I've got a question about returning the value of a pk-column to a
DataTable
after inserting a row (via a data-adapter) using MySql5.

Here is the SQL and code concerned:

//================================================== =================
// The table

CREATE TABLE `client` (
`id_client` int(10) unsigned NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
PRIMARY KEY (`id_client`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
//================================================== =================
// Stored procedure

CREATE PROCEDURE pInsertClient (OUT id_client INT, IN name VARCHAR(40))
BEGIN

INSERT INTO client(name) VALUES (name);
SET id_client = LAST_INSERT_ID();

END$$

The code:
//================================================== =================
// Creating the data-adapter
....
try
{
// QueryHandler::getConnection() returns a static connection
dataAdapter = gcnew MySqlDataAdapter("SELECT * FROM client",
QueryHandler::getConnection());

// Insert
MySqlCommand^ insertCommand = gcnew MySqlCommand("pInsertClient");
insertCommand->Connection = QueryHandler::getConnection();

insertCommand->CommandType = CommandType::StoredProcedure;

MySqlParameter^ parameter1 = insertCommand->Parameters->Add("?name",
MySqlDbType::VarChar, 40, "name");
parameter1->Direction = ParameterDirection::Input;
MySqlParameter^ parameter2 =
insertCommand->Parameters->Add("?id_client", MySqlDbType::Int32, 32,
"id_client");
parameter2->Direction = ParameterDirection::Output;

dataAdapter->InsertCommand = insertCommand;

dataSet = gcnew DataSet();
dataTable = gcnew DataTable("client");
// dataTable declared elsewhere
dataSet->Tables->Add(dataTable);
dataAdapter->MissingSchemaAction = MissingSchemaAction::AddWithKey;
dataAdapter->Fill(dataTable);

DataColumn^ pkCol = dataTable->Columns["id_client"];
array<DataColumn^>^ columnArray = { pkCol };
dataTable->PrimaryKey = columnArray;
catch (MySqlException^ mE)
{
MessageBox::Show("An exception occured: \n" + mE->Message);
}
....

//================================================== =================

// Performing an insert to the client table

....
DataRow^ newRow = dataTable->NewRow();

newRow["id_client"] = static_cast<int^>(0); // dummy value
newRow["name"] = this->textBox->Text;
dataTable->Rows->Add(newRow);
dataAdapter->Update(dataTable);
....
//================================================== =================

This all works fine. But when I try to optimize the updating of the
DataTable
by changing the last line of code:

dataAdapter->Update(dataTable);

to:

DataSet^ changes = dataSet->GetChanges(DataRowState::Added);
dataAdapter->Update(changes->Tables["client"]);
The primairy key value that is generated in the source database will
not be
returned to the pk-column in the DataTable.

I wonder if it's really necessary to limit the update statement to the
isolated changed rows by calling GetChanges, because when it's applied
to the entire DataTable these rows also have to be determined.

So, can anybody help me out with the following questions:

1. Is it usefull to apply the Update-statement to a narrowed down
selection
of changed rows from the DataTable?

2. If so, how to accomplish this without having to use the RowUpdated
event?
Thanks a lot in advance!

Maart

Nov 13 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.