如何从存储过程中获取字符串值 [英] how to get string value from stored procedure

问题描述

我想通过存储过程从登录表中获取字符串值(名称)

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  --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

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;
           
        }

例外是
base {System.Data.Common.DbException} = {将varchar值"Admin"转换为数据类型int时转换失败."}

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

推荐答案

base {System.Data.Common.DbException} = {将varchar值"Admin"转换为数据类型int时转换失败."}

之所以会出现此异常，是因为存储过程的第一个参数中的数据类型不同于C#代码中第一个参数中的数据类型.

@intoption int = 0
cmd.Parameters.Add("@ intoption"，SqlDbType.VarChar，50).Value =选项;

尝试先解决此问题

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

请参阅以下文章，并将@designation设置为输出参数
在ADO.NET中调用存储过程 [

See following article and make @designation as output parameter
Calling Stored procedures in ADO.NET[^]

这篇关于如何从存储过程中获取字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！