no

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 ha...

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

Related

.net 4124411262297498005

Post a Comment

1 comment

Anonymous said...

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

item