locked
unable to call sql server stored procedure from c# RRS feed

  • Question

  • User-1614995517 posted

    Hi all, I am having problem in calling MS SQL Server procedure from c# following  is my code ...

    C#

     public static COIObject<User> AuthenticateUser(string userName, string password)
            {
                COIObject<User> objCOIObject = null;
                try
                {
                    SqlConnection objSqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PIDCon"].ConnectionString);
                    SqlCommand objSqlCommand = new SqlCommand("sp_User_Select", objSqlConnection);
    
                    objSqlCommand.Parameters.AddWithValue("@p_UserName", userName);
                    objSqlCommand.Parameters.AddWithValue("@p_Password", password);
                    objSqlConnection.Open();
                    SqlDataReader objSqlDataReader = objSqlCommand.ExecuteReader();
                    User objUser = null;
                    if (objSqlDataReader.HasRows)
                    {
                        while (objSqlDataReader.Read())
                        {
                            objUser = new User();
    
                            objUser.UserId = Convert.ToInt32( objSqlDataReader.GetValue(objSqlDataReader.GetOrdinal("UserId")), CultureInfo.CurrentCulture);
                            objUser.UserName = Convert.ToString(objSqlDataReader.GetValue(objSqlDataReader.GetOrdinal("UserName")), CultureInfo.CurrentCulture);
                            objUser.Password = Convert.ToString(objSqlDataReader.GetValue(objSqlDataReader.GetOrdinal("Password")), CultureInfo.CurrentCulture);
                        }
                    }
    
    
    
                    objCOIObject = new COIObject<User>();
                    objCOIObject.Entity = objUser;
    
                    objCOIObject.Success = true;
                }
                catch (Exception)
                {
                    throw;
                }
                return objCOIObject;
            }


    SQL Stored Procedure

    ALTER PROCEDURE [dbo].[sp_User_Select]
    	@p_UserName varchar(50),
    	@p_Password varchar(50)
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	SELECT * from [User] u where u.UserName = @p_UserName and u.Password = @p_Password;
    END

    I am getting following exception:

    System.Data.SqlClient.SqlException: 'Procedure or function 'sp_User_Select' expects parameter '@p_UserName', which was not supplied.'

    please help...

    Wednesday, August 21, 2019 5:10 AM

Answers

  • User288213138 posted

    Hi ali_raza159,

    if you want to call StoredProcedure , you must select CommandType to StoredProcedure.

    The code:

    using System.Data;
    
    objSqlCommand.CommandType = CommandType.StoredProcedure;

    Best regards,
    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 21, 2019 7:04 AM

All replies

  • User-2054057000 posted

    System.Data.SqlClient.SqlException: 'Procedure or function 'sp_User_Select' expects parameter '@p_UserName', which was not supplied.'

    Check that the function is getting username from the parameter. You can try this code and check:

    objSqlCommand.Parameters.AddWithValue("@p_UserName", "abc");
    objSqlCommand.Parameters.AddWithValue("@p_Password", "xyz");

    Wednesday, August 21, 2019 5:53 AM
  • User288213138 posted

    Hi ali_raza159,

    if you want to call StoredProcedure , you must select CommandType to StoredProcedure.

    The code:

    using System.Data;
    
    objSqlCommand.CommandType = CommandType.StoredProcedure;

    Best regards,
    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 21, 2019 7:04 AM