Looking for a JavaEE Architect or Potential Tech Co-Founder?
Please don't hesitate to contact me.

SQLCE get last insert id, convert SqlDecimal to Int32

Platform: Windows Mobile, SqlCE2.0

Problem:
You want to get the id of the last query you insert.

Solution:
Using the @@identity you have to execute your queries like this:


SqlCeConnection conn = null;
try {
conn = new SqlCeConnection(GetConnection());
conn.Open();

//execute the insert statement
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

//get the last insert id
cmd.CommandText = "SELECT @@IDENTITY"; //using identity
//take note ExecuteScalar return an object, and in sqlce @@identity returns SqlDecimal
SqlDecimal x = (SqlDecimal)cmd.ExecuteScalar();
//so my way of converting it to int is catch the value as SqlDecimal
//convert that to string and then convert the string to int
//this way works for me
id = Convert.ToInt16(x.ToString());
}


Note:
1.) @@IDENTITY returns SqlDecimal so you need to convert it to integer
SQLCE get last insert id, convert SqlDecimal to Int32 SQLCE get last insert id, convert SqlDecimal to Int32 Reviewed by czetsuya on Tuesday, September 22, 2009 Rating: 5

1 comment:

Anonymous said...

Thank you! In my case it was sufficient to pipe the return value from cmd.ExecuteScalar() directly into Convert.ToInt32()

Powered by Blogger.