Retrieving output parameter from stored proc

I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.

The problem is that

cmd.Parameters["@.UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.

Here is my code:

using (SqlConnection cn =new SqlConnection(this.ConnectionString)){ SqlCommand cmd =new SqlCommand("mys_ExistsPersonWithUserName", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@.UserName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@.UserExists", SqlDbType.Int); cmd.Parameters["@.UserExists"].Direction = ParameterDirection.Output; cn.Open();int x = (int)cmd.Parameters["@.UserExists"].Value; cn.Close();return (x>1);}

And the corresponding stored procedure:

ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName (@.UserNamevarchar(16),@.UserExistsint OUTPUT)ASSET NOCOUNT ONSELECT @.UserExists =count(*)FROM mys_ProfilesWHERE UserName = @.UserNameRETURN

Hey,

Not sure what the problem is, but as an alternative, you can use a return method and define it as return value, or you can just select the result, and in your code, use ExecuteScalar().

sql

Retrieve ID of Last Insert (Scope_Identity)

(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks.

Session["snCoDeptRowID"] = SqlDataSource1.Select();

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT Scope_Identity"

</asp:SqlDataSource>

try

SelectCommand="SELECT Scope_Identity()"

|||

Thank you for the suggestion. Now I am having trouble with the following line of code which assigns the Scope_Identity() to a session variable. The error msg is: does not recognise the word "command". How can I get the value of the @.CoDeptRowID (Scope_Identity) into my session variable? Thanks

Session["snCoDeptRowID"] = Convert.ToInt32(e.command.parameters("@.CoDeptRowID").value);

|||

Check thate argument has a command object

only if its there in event argument you will be able to use it. And Check the Direction of the @.CoDeptRowId is set to Output in Command as well

|||

Hi,

There are many ways to get the SCOPE_IDENTITY() from stored procedure.

You can use SELECT SCOPE_IDENTITY() or RETURN SCOPE_IDENTITY() or SET @.PARAM = SCOPE_IDENTITY(). All is fine, but the way to get it is different.

SELECT will return the SCOPE_IDENTITY() as the first col and first row of a result set. RETURN will return is as a return parameter. While SET will set the value to a parameter and you have to specify it as an OUTPUT parameter when declaring it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!