How to get the returned value from Stored Proc. ?

[Jiggaz]

Hi,

I have a stored procedure (ms sql 2005) which is return add
the user in the table USERS. But the sp (stored proc) tests
if a user with the desired nickname already exists. If
exists, return -1 otherwise return 1 and execute the INSERT
query!

But, how to get the returned value from the stored
procedure (called : CreateAccount) in ASP.NET ?

My actual code :
__________
SqlCommand myCommand = new SqlCommand();
String strCnx =
ConfigurationSettings.ConnectionStrings["AppCnxStr"].ConnectionString.ToString();
myCommand.Connection = new SqlConnection(strCnx);
myCommand.Connection.Open();

SqlParameter myNickname = new SqlParameter("@Nickname",
SqlDbType.NVarChar, 30);
myNickname.Value = boxLogin.Text;
myCommand.Parameters.Add(myNickname);
.... // same for all parameters.

myCommand.ExecuteNonQuery();

// here i want to get the value to make an if : if (value =
1) COMPLETE REGISTRATION else GO BACK TO CHANGE NICKNAME.
________________________

Regards.

 

[Robert Howells]

The .ExecuteScalarm method of the SqlCommand object is designed to return a
single value... something like this

int retVal = Convert.ToInt32(myCommand.ExecuteScalar());



Hi,

I have a stored procedure (ms sql 2005) which is return add
the user in the table USERS. But the sp (stored proc) tests
if a user with the desired nickname already exists. If
exists, return -1 otherwise return 1 and execute the INSERT
query!

But, how to get the returned value from the stored
procedure (called : CreateAccount) in ASP.NET ?

My actual code :
__________
SqlCommand myCommand = new SqlCommand();
String strCnx =
ConfigurationSettings.ConnectionStrings["AppCnxStr"].ConnectionString.ToStri
ng();
myCommand.Connection = new SqlConnection(strCnx);
myCommand.Connection.Open();

SqlParameter myNickname = new SqlParameter("@Nickname",
SqlDbType.NVarChar, 30);
myNickname.Value = boxLogin.Text;
myCommand.Parameters.Add(myNickname);
.... // same for all parameters.

myCommand.ExecuteNonQuery();

// here i want to get the value to make an if : if (value =
1) COMPLETE REGISTRATION else GO BACK TO CHANGE NICKNAME.
________________________

Regards.

 

[Todd Casey]

You could also use the SqlParameter class and have the stored procedure
return the value (ParameterDirection.Output). Just another option.

Todd Casey
Velocity West