Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am calling store procedure from code using Microsft SQL Helper Utlity class and getting each time null value while store procedure is giving proper value when executing from SQL server.
below is my code.
SQL
Create Proc [dbo].[SP_GetUserNumber]
(
 @UserID AS VARCHAR(10)= '',
 @RetValue As VARCHAR(50) OUTPUT
)
As
BEGIN
    select @RetValue=MobNumber from tbl_User where userid=@UserID
END


C# code
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.AppSettings["SQLConn"]);
			
			SqlParameter[] SqlParam = new SqlParameter[2];
            SqlParam[0] = new System.Data.SqlClient.SqlParameter("@UserID", SqlDbType.VarChar);
            SqlParam[0].Value = strUserID;

            SqlParam[1] = new System.Data.SqlClient.SqlParameter("@RetValue", SqlDbType.VarChar);
            SqlParam[1].Direction = ParameterDirection.Output;

            

            int i = SqlHelper.ExecuteNonQuery(sqlcon, "SP_GetUserNumber", SqlParam);

            string[] arReturnParms = new string[1];
            arReturnParms[0] = SqlParam[1].Value.ToString();
Posted

 
Share this answer
 
Comments
vishal_h 29-Sep-15 5:59am    
Sir above solution will work but i am talking about SQL Helper class which is microsoft generic utility.
John C Rayan 29-Sep-15 6:33am    
Hi You could search for the solution within CODEPROJECT. For example see below

http://www.codeproject.com/Tips/555870/SQL-Helper-Class-Microsoft-NET-Utility
 
Share this answer
 
Comments
vishal_h 30-Sep-15 0:57am    
It was problem with my store procedure as in that SP output variable is used in select statement to set the value rather than using set statement.
John C Rayan 30-Sep-15 4:53am    
Nice work!
Check if there is duplicate userID value in the table. If it is returning more then one record then it will throw an exception resulting null.

You can also try with ExecuteScaler instead of ExecuteNonQuery.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900