output parameters - once again...

andreim · Post by **andreim** » Thu 08 Sep 2005 21:06

Hi All,

Here is the attempted code - obviously, I want to get the last insert id in one trip and stick it into this._lastinputid class member and to the test_id field of my dataset:

...
this.mySqlInsertCommand1.CommandText = "INSERT INTO cms.test(test_id, testtext) VALUES (\\, :testtext);SELECT :param=@@identity";
...
this.mySqlInsertCommand1.Parameters.Add(new CoreLab.MySql.MySqlParameter("testtext", CoreLab.MySql.MySqlType.VarChar, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "testtext", System.Data.DataRowVersion.Current, null));
this.mySqlInsertCommand1.Parameters.Add(new CoreLab.MySql.MySqlParameter("param", CoreLab.MySql.MySqlType.Int, 11, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "test_id", System.Data.DataRowVersion.Current, this._lastinsertid));

According to the reference, the value of parameter this._lastinsertid 'is set on the completion of the command'. By itself, SELECT @@IDENTITY works perfectly, however I can' t pull the value into the :param and subsequently into the this._lastinsertid. Any ideas?

Thank you.

Serious · Post by **Serious** » Fri 09 Sep 2005 06:32

Due to limitations of MySQL protocol, MySqlParameter class does not support out parameters in this query.

andreim · Post by **andreim** » Fri 09 Sep 2005 19:31

I guess I can use command's InsertId property to retrieve the last AutoIncrement value.

Question(s): How can I synchronize a new AutoIncrement value in my DataSet with the one on the server? In earlier discussions it sounded like dataAdapter will do it for you. However, it's not happening in my case. Do I miss something? Right now I plan to grab a new Auto number using InsertId and manually stick it to a field in my DataSet. But to do it this way seems far from an elegant solution. What is a 'proper' way?

Thank you.

Serious · Post by **Serious** » Mon 12 Sep 2005 08:20

Here is an example to populate an autoincrement field value from database:

Code: Select all

dataAdapter = new MySqlDataAdapter("select * from dept", connection);
commandBuilder = new MySqlCommandBuilder(dataAdapter);
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dataAdapter.InsertCommand.CommandText += ";select deptno from dept;"; // deptno is autoincrement field