469,626 Members | 1,068 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

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 1583

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
By using this site, you agree to our Privacy Policy and Terms of Use.