Another SCOPE_IDENTITY() / SqlDataSource Issue

Hi Folks,

I've been Googling about for ages trying to get to the bottom of this, I've found plenty of stuff but nothing with the answer I'm looking for :(

What I'm trying to do is use SqlDataSource programatically (i.e. not the <asp:SqlDataSource ... /> route) to call a stored procedure which inserts values into a table, the Sproc ends with RETURN SCOPE_IDENTITY() in order to return the autoincrement identity of the row that was just created. My specific problem is getting the returned value assigned to any sort of variable at the C# end! At the moment it seems to be returning 1 for some reason, it used to return -1 so I guess this is a step in the right direction... Here's my code:

int importantReturnValue = 0;

SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = connString;
sds.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
sds.InsertCommand = "sp_insertValues";

sds.InsertParameters.Add("creator", creator);
sds.InsertParameters.Add("title", title);
// And so on.. Perhaps I need to add importantReturnValue to sds as some sort as parameter? - If so, how should I declare this at the Sproc end?

...

importantReturnValue = sds.Insert();

Label1.Text = importantReturnValue.ToString();

Thanks,

Ally

Hi,

the INSERT() method of the SqlDataSource returns the number of affected rows. A good way to retrieve the id is to use an output variable. See my reply to this post:http://forums.asp.net/thread/1603790.aspx

Regards
Marc André

|||

Hi Marc,

Many thanks for the prompt reply. I was attempting to use the SqlDataSource as opposed to the SqlCommand object you used in your example. I just couldn't find a way to assign the returned value from the Sproc to a variable, so I've changed it to now use the SqlCommand route instead. After a bit of playing about with the Sproc, all working now, many thanks for your help :) .

I seem to spend hours and hours trying to figure out how to get something SQL related to work, only to find out I'm using the wrong Sql c# object for my needs - is there some guide somewhere I'm missing?! Maybe I just need more practise at reading APIs...

All the best,

Ally