Error using data reader

[Stephen]

I'm having terrible trouble working out where I have gone wrong in my code
below. In the data layer I have two methods and i'm basically returning a
command and using it and then returning a reader. Unfortunately i'm getting
the following error on the line:
reader = cmdLogin.ExecuteReader();
Error Message: -
Procedure 'LoginValidation' expects parameter '@p_UserName', which was not
supplied.

Not sure where I have went wrong as I reckon I have supplied it. Can someone
have a look at this and give me some suggestions please.
Thanks for any help you can give.

public static SqlDataReader LoginValidateUser(string p_sUserName,
SqlConnection p_conn)
{
SqlCommand cmdLogin = null;
SqlDataReader reader = null;

try
{
cmdLogin = LoginValidateUserCommand(p_sUserName);
//Set the connection instance
cmdLogin.Connection = p_conn;
if (!(ConnectionState.Open == p_conn.State))
{
p_conn.Open();
}
// execute the reader

reader = cmdLogin.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{

cmdLogin.Dispose();
}
return reader;
}

private static SqlCommand LoginValidateUserCommand(string p_sUserName)
{
SqlCommand cmdLogin = null;
try
{
cmdLogin = new SqlCommand();
cmdLogin.CommandText = "LoginValidation";
cmdLogin.CommandType = CommandType.StoredProcedure;

//Create Parameter
SqlParameter @p_UserName = new SqlParameter
("username", System.Data.SqlDbType.Char);
@p_UserName.Direction = ParameterDirection.Input;
// set parameter value
@p_UserName.Value = p_sUserName;

}
catch (Exception e)
{
throw(e);
}
return cmdLogin;
}

[John Bailo]

You create the parameter, but you never add it to the parameters
collection of the command.
Stephen wrote:

I'm having terrible trouble working out where I have gone wrong in my code
below. In the data layer I have two methods and i'm basically returning a
command and using it and then returning a reader. Unfortunately i'm getting
the following error on the line:
reader = cmdLogin.ExecuteReader();
Error Message: -
Procedure 'LoginValidation' expects parameter '@p_UserName', which was not
supplied.

Not sure where I have went wrong as I reckon I have supplied it. Can someone
have a look at this and give me some suggestions please.
Thanks for any help you can give.

public static SqlDataReader LoginValidateUser(string p_sUserName,
SqlConnection p_conn)
{
SqlCommand cmdLogin = null;
SqlDataReader reader = null;

try
{
cmdLogin = LoginValidateUserCommand(p_sUserName);
//Set the connection instance
cmdLogin.Connection = p_conn;
if (!(ConnectionState.Open == p_conn.State))
{
p_conn.Open();
}
// execute the reader

reader = cmdLogin.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{

cmdLogin.Dispose();
}
return reader;
}

private static SqlCommand LoginValidateUserCommand(string p_sUserName)
{
SqlCommand cmdLogin = null;
try
{
cmdLogin = new SqlCommand();
cmdLogin.CommandText = "LoginValidation";
cmdLogin.CommandType = CommandType.StoredProcedure;

//Create Parameter
SqlParameter @p_UserName = new SqlParameter
("username", System.Data.SqlDbType.Char);
@p_UserName.Direction = ParameterDirection.Input;
// set parameter value
@p_UserName.Value = p_sUserName;

}
catch (Exception e)
{
throw(e);
}
return cmdLogin;
}