471,863 Members | 1,196 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,863 software developers and data experts.

Returning pk value to DataTable

Hi all,

I've got a question about returning the value of a pk-column to a
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`)
//================================================== =================
// Stored procedure

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

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


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

// 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,
parameter2->Direction = ParameterDirection::Output;

dataAdapter->InsertCommand = insertCommand;

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

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;
//================================================== =================

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



DataSet^ changes = dataSet->GetChanges(DataRowState::Added);
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
of changed rows from the DataTable?

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


Nov 13 '06 #1
0 1671

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by lekshmi | last post: by
2 posts views Thread by lekshmi | last post: by
3 posts views Thread by lekshmi | last post: by
6 posts views Thread by Young J. Putt | last post: by
15 posts views Thread by Joseph Geretz | last post: by
5 posts views Thread by Frank Hauptlorenz | last post: by
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.