Get Return Value from Stored procedure in asp net

Question

i have a stored procedure   ALTER PROC TESTLOGIN      UserName varchar 50        password varchar 50  As Begin     declare  return int       set  return     SELECT COUNT                         FROM CPUser                      WHERE UserName    UserName                      AND Password    password        return  return  End   and in c   SqlConnection con   db con  SqlCommand cmd   new SqlCommand  TESTLOGIN   con   cmd CommandType   CommandType StoredProcedure   SqlParameter parm   new SqlParameter   return   SqlDbType Int   parm Direction   ParameterDirection ReturnValue   cmd Parameters Add parm   cmd Parameters Add new SqlParameter   UserName   txtUserName Text ToString   Trim      cmd Parameters Add new SqlParameter   password   txtPassword Text ToString   Trim       cmd ExecuteNonQuery    con Close     int id   Convert ToInt32 parm Value     but it always return 0  Please help me to solve this problem

User · Answer

Procedure never returns a value You have to use a output parameter in store procedure   ALTER PROC TESTLOGIN  UserName   varchar 50    password   varchar 50   retvalue int output  as  Begin     declare  return     int      set  return     Select COUNT         FROM    CPUser       WHERE   UserName    UserName AND Password    password      set  retvalue  return   End   Then you have to add a sqlparameter from c  whose parameter direction is out   Hope this make sense

User · Answer

Do it this way  make necessary changes in code                 SqlConnection con   new SqlConnection GetConnectionString                 con Open                SqlCommand cmd   new SqlCommand  CheckUser   con               cmd CommandType   CommandType StoredProcedure              SqlParameter p1   new SqlParameter  username   username Text               SqlParameter p2   new SqlParameter  password   password Text               cmd Parameters Add p1               cmd Parameters Add p2               SqlDataReader rd   cmd ExecuteReader                if rd HasRows                                  do the things                           else                               lblinfo Text    abc

User · Answer

2 things    The query has to complete on sql server before the return value is sent  The results have to be captured and then finish executing before  the return value gets to the object    In English  finish the work and then retrieve the value   this will not work                   cmm ExecuteReader                    int i    int  cmm Parameters   RETURN VALUE   Value    This will work                           SqlDataReader reader   cmm ExecuteReader                            reader Close                             foreach  SqlParameter prm in cmd Parameters                                                       Debug WriteLine                                 Debug WriteLine  Name     prm ParameterName                              Debug WriteLine  Type     prm SqlDbType ToString                                Debug WriteLine  Size     prm Size ToString                                Debug WriteLine  Direction     prm Direction ToString                                Debug WriteLine  Value     prm Value                                if you are not sure check the value of the parameter before during and after the results have been processed by the reader

User · Answer

You need a parameter with Direction set to ParameterDirection ReturnValue in code but no need to add an extra parameter in SP  Try this    SqlParameter returnParameter   cmd Parameters Add  RetVal   SqlDbType Int     returnParameter Direction   ParameterDirection ReturnValue    cmd ExecuteNonQuery       int id    int  returnParameter Value

User · Answer

If you want to to know how to return a value from stored procedure to Visual Basic NET  Please read this tutorial  How to return a value from stored procedure  I used the following stored procedure to return the value   CREATE PROCEDURE usp get count  AS BEGIN  DECLARE  VALUE int    SET  VALUE  SELECT COUNT    FROM tblCar     RETURN  VALUE   END GO

User · Answer

you can try this Add the parameter as output direction and after executing the query get the output parameter value     SqlParameter parmOUT   new SqlParameter   return   SqlDbType Int     parmOUT Direction   ParameterDirection Output    cmd Parameters Add parmOUT     cmd ExecuteNonQuery      int returnVALUE    int cmd Parameters   return   Value

[c#] Get Return Value from Stored procedure in asp.net

Examples related to c#

Examples related to asp.net

Examples related to sql-server