Execute Reader

  • Thread starter Thread starter David W
  • Start date Start date
D

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
 
Hi David,

David W said:
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?
Click to expand...

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?
 
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
 
Hi David,

So, you are inserting a value?
Why do you use reader?
You should use ExecuteNonQuery().
 
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.
 
Hi David,

I see. Try appending the ";SELECT SCOPE_IDENTITY()" at the end of insert
(assuming you are dealing with sql server).
 
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
 
Back
Top