Originally Posted by Tarcash
OK, this problem is something that took me a long time to figure out, and that's with Microsoft's support line. Luckily, this became classified as "a known issue" so I wasn't charged for anything. There is no complete description as to why it occurs, but it seems to occur when there is an issue passing items to your stored procedure, or an error occurs in your stored procedure. There is an easy fix, but it may not be what you wanted to use.
Use an OUTPUT parameter instead of a return value. I know, I hate doing it too, but it removes the issue. Like I said, the return parameters for sqlDataSources are bugged, so use an output parameter.
Sorry for bringing up a new post, only I found this post when I was trying to figure out this issue and hope to help someone who might also come across it.
Sorry for the long delay, thought it would be useful to document this since I had the same issue and couldn't find any answers on the internet.
To retrieve a return value from SQL server, you need to access it from the Inserted event.
protected void DSProjects_Inserted(object sender, SqlDataSourceStatusEventArgs e)
string strID = (e.Command.Parameters["@RETURN_VALUE"].Value).ToString();
Response.Redirect("ProjectDetails.aspx?id=" + strID);
A couple of things to mention that was overlooked:
1. Return value is not the selected value in SQL. In your Stored procedure, you need to use "RETURN @ID", rather than Select @ID
2. ASP parameter should look like this (Ensure the Size is not default 0):
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" Size="100" />
Alternatively, you can use the Output SQL Parameter:
<asp:Parameter Direction="Output" Name="ID" Type="Int32" Size="100" />
and set up SQL using outputs. In the Scope:
@ID VARCHAR(20) OUTPUT
in the code section:
SET @ID = SCOPE_IDENTITY()