I want to get string value(designation) from Login table throught stored procedure
CREATE PROCEDURE logincheck
(
@intoption int=0,
@emp_name varchar(20)='',
@uid varchar(20)='',
@p varchar(20)='',
@add varchar(20)='',
@desig varchar(20)='',
@np varchar(20)=''
)
as
begin
declare @designation varchar(20)
if @intoption=0
begin
select @designation=Designation from Login where id=@uid and password=@p
if @designation is null
return null
else
return @designation
end
end
and calling
string r=checkuser(0, "", TextBox1.Text, TextBox2.Text, "", "", "");
private string checkuser(int option, string emp_name, string userid, string p, string addres, string desig, string np)
{
con.Open();
SqlCommand cmd = new SqlCommand("logincheck", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@intoption", SqlDbType.VarChar, 50).Value = option;
cmd.Parameters.Add("@emp_name", SqlDbType.VarChar, 50).Value = emp_name;
cmd.Parameters.Add("@uid", SqlDbType.VarChar, 50).Value = userid;
cmd.Parameters.Add("@p", SqlDbType.VarChar, 50).Value = p;
cmd.Parameters.Add("@add", SqlDbType.VarChar, 50).Value = addres;
cmd.Parameters.Add("@desig", SqlDbType.VarChar, 50).Value = desig;
cmd.Parameters.Add("@np", SqlDbType.VarChar, 50).Value = np;
SqlParameter p1 = new SqlParameter("ret", SqlDbType.Int);
p1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p1);
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
string firstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
}
rdr.Close();
}
string kk = (cmd.Parameters["ret"].Value).ToString();
return kk;
}
exception is
base {System.Data.Common.DbException} = {"Conversion failed when converting the varchar value 'Admin' to data type int."}