Click here to Skip to main content
15,894,546 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want to get string value(designation) from Login table throught stored procedure

SQL
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  --for login
 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

C#
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."}
Posted
Updated 24-Jul-20 20:30pm
v2
Comments
Praveen Meghwal 4-May-12 5:38am    
I think you can only return integer values through the stored procedure's return type.
Try out put parameter for this purpose.

base {System.Data.Common.DbException} = {"Conversion failed when converting the varchar value 'Admin' to data type int."}

this exception is arising because you have different DataType in first parameter of Stored Procedure then that present in the first parameter in your C# code.

@intoption int=0
cmd.Parameters.Add("@intoption", SqlDbType.VarChar, 50).Value = option;

Try fixing this first
 
Share this answer
 
v4
See following article and make @designation as output parameter
Calling Stored procedures in ADO.NET[^]
 
Share this answer
 
firstly you return string value in store procedure its wrong. it is always return int value if you want return string value using select statement. for Ex select @designation As Designation
 
Share this answer
 
Comments
CHill60 27-Jul-20 6:18am    
"if you want return string value using select statement" - that, or better would be to use an output parameter.

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