Execute Reader

[David W]

I am trying to get the unique id that is created when an insert is done, so
I thought I would use Execute reader public SqlCeDataReader
ExecuteReader(); instead of ExecuteNonQuery, so that I get returned back to
me the row that was just inserted. Both commands insert the record ok, but
the SqlCeDataReader returned is invalid when i try:

SqlCeDataReader dataReader = cmdDML.ExecuteReader(CommandBehavior.Default);
dataReader.Read();
dataReader.GetInt32(0);//Throws Exception

Can I use ExecuteReader like this or is there an easier way to get the
unique id?

Thanks

 

[Miha Markic]

Hi David,

I am trying to get the unique id that is created when an insert is done, so
I thought I would use Execute reader public SqlCeDataReader
ExecuteReader(); instead of ExecuteNonQuery, so that I get returned back to
me the row that was just inserted. Both commands insert the record ok, but
the SqlCeDataReader returned is invalid when i try:

SqlCeDataReader dataReader = cmdDML.ExecuteReader(CommandBehavior.Default);
dataReader.Read();
dataReader.GetInt32(0);//Throws Exception

Can I use ExecuteReader like this or is there an easier way to get the
unique id?

There is a no-checking error there - you should read a boolean from Read()
method that indicates if the data is avaliable.
Other than that, yes, you can.

What does the exception says? What is the cmdDML.CommandText?

 

[David W]

The cmdDML is:

string DML = "INSERT INTO Groups(ParentGroupID , Name) "
+ "VALUES ( '" + tagInt.ToString() + "','"
+ treeNode.Text + "')";

The create table statement is:

string DDL1 = "CREATE TABLE Groups(GroupID int not null identity(1,1),"
+ " ParentGroupID int,"
+ " Name nvarchar(50) null)";

and the exception is:

InvalidOperationException, no data exist for this row/column

 

[Miha Markic]

Hi David,

So, you are inserting a value?
Why do you use reader?
You should use ExecuteNonQuery().

 

[David W]

Yes, I am inserting value/row, and I thought I could use Execute Reader
because it returns a DataReader which I thought might tell me the unique id
that was inserted into the table.

Is there another way to get the unique id of an inserted row?

Thanks.

 

[Miha Markic]

Hi David,

I see. Try appending the ";SELECT SCOPE_IDENTITY()" at the end of insert
(assuming you are dealing with sql server).

 

[David W]

I am actually using SqlCE on Pocket PC 2003.

The Insert statement was changed to :

string DML = "INSERT INTO Groups(ParentGroupID , Name) "
+ "VALUES ( '" + tagInt.ToString() + "','"
+ treeNode.Text + "');SELECT SCOPE_IDENTITY()";

An SQLCE exception was thrown, with no useful message. I guess it doesn't
work on CE.

Any other ideas? Thanks for your help Miha

 

[Charles Sauthier]

Maybe try @@IDENTITY instead of SCOPE_IDENTITY()

 

[David W]

Unfortunately this did not work either, same error, thanks anyway.