我这个方法怎么才能接收存储过程飞返回值呢

Question

 public static int ExecProcedurei(string ProcedureName, string[] VariableName, object[] Values)
        {
            int number = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                for (int i = 0; i < VariableName.Length; i++)
                {                    cmd.Parameters.Add("@" + VariableName[i], Values[i]);
                                    }                Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                number =(int) cmd.Parameters["@pkid"].Value;
                cmd.Parameters.Clear();
                Close();
            }
            catch (Exception ex)
            {
                wysc.systemlog(ex.Message, "~/err/sql");
                number = 0;
            }
            return number;
        }

Answer 1

只是返回插入数据库的数据ID
下面是存储过程
create proc inbbs
@userID int,
@uname nvarchar(100),
@classID int ,
@ifok int,
@click int,
@title nvarchar(200),
@con nvarchar(1000),
@pkid   int   output  
as
insert into bbs values(@userID,@uname,@classID,@ifok,@click,@title,@con)
--select    @pkid     =SCOPE_IDENTITY()
 select   @pkid     =SCOPE_IDENTITY()
return
go

Answer 2

  number =(int) cmd.Parameters["@pkid"].Value; 
取的类型错误

Answer 3

若用output 就不用select 了，再就是还要在代码中定义这个OUTPUT 变量

Answer 4

存储过程里return什么了？
select  @pkid    =SCOPE_IDENTITY() 
return @pkidSCOPE_IDENTITY() 是什么，跟添加数据有什么关系number =cmd.ExecuteScalar();  就可以得到SQL中返回的第一行第一列数值

Answer 5

SCOPE_IDENTITY() 
不是添加的ID值么

Answer 6

Parameter.Direction 属性...ParameterDirection.ReturnValue 参数表示诸如存储过程、内置函数或用户定义函数之类的操作的返回值。去看MSDN...

Answer 7

int number =int.parse(cmd.ExecuteScalar());  得到SQL中返回的第一行第一列数值 

Answer 8

汗...这个存储过程写的...SCOPE_IDENTITY() 这个是自定义函数？ 直接 set @pkid=@@identity
    return @pkid

Answer 9

设置Parameters.Direction.public static int ExecProcedurei(string ProcedureName, string[] VariableName, object[] Values) 
        { 
            int number = 0; 
            try 
            { 
                SqlCommand cmd = new SqlCommand(ProcedureName, conn); 
                for (int i = 0; i < VariableName.Length; i++) 
                {                     cmd.Parameters.Add("@" + VariableName[i], Values[i]); 
                                    }                 Open(); 
                cmd.CommandType = CommandType.StoredProcedure; 
                cmd.Parameters["@pkid"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery(); 
                number =(int) cmd.Parameters["@pkid"].Value; 
                cmd.Parameters.Clear(); 
                Close(); 
            } 
            catch (Exception ex) 
            { 
                wysc.systemlog(ex.Message, "~/err/sql"); 
                number = 0; 
            } 
            return number; 
        }

Answer 10

public static int ExecProcedurei(string ProcedureName, string[] VariableName, object[] Values) 
        { 
            int number = 0; 
            try 
            { 
                SqlCommand cmd = new SqlCommand(ProcedureName, conn); 
                for (int i = 0; i < VariableName.Length; i++) 
                {                     cmd.Parameters.Add("@" + VariableName[i], Values[i]); 
                                    }                 Open(); 
                cmd.CommandType = CommandType.StoredProcedure; 
                cmd.ExecuteNonQuery(); 
                number =(int) cmd.Parameters["@pkid"].Value; 
                cmd.Parameters.Clear(); 
                Close(); 
            } 
            catch (Exception ex) 
            { 
                wysc.systemlog(ex.Message, "~/err/sql"); 
                number = 0; 
            } 
            return number; 
        }
你的cmd还要添加一个输出类型参数，用来接收存储过程返回的参数@pkid

Answer 11

你这个参数cmd.Parameters["@pkid"]好像没有add，就直接引用了

Answer 12

cmd.Parameters.Add("@pkid"); 
cmd.Parameters["@pkid"].Direction = ParameterDirection.Output;
number =(int) cmd.Parameters["@pkid"].Value;

Answer 13

存储过程改成这样？create proc inbbs2
@userID int,
@uname nvarchar(100),
@classID int ,
@ifok int,
@click int,
@title nvarchar(200),
@con nvarchar(1000),
@pkid   int   output  
as
insert into bbs values(@userID,@uname,@classID,@ifok,@click,@title,@con)
set @pkid=@@identity 
 return @pkid 
go

Answer 14

      public static int ExecProcedurei(string ProcedureName, string[] VariableName, object[] Values)
        {
            int number = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                for (int i = 0; i < VariableName.Length; i++)
                {                    if (i == VariableName.Length - 1)
                    {
                        cmd.Parameters.Add("@" + VariableName[i], Values[i]).Direction = ParameterDirection.ReturnValue; ;
                        //cmd.Parameters["@" + VariableName[i]].Direction = ParameterDirection.ReturnValue;
                    }
                    else
                    {
                        cmd.Parameters.Add("@" + VariableName[i], Values[i]);
                    }                }                Open();
                //cmd.Parameters["@pkid"].Direction = ParameterDirection.ReturnValue;
                cmd.CommandType = CommandType.StoredProcedure;
               
                cmd.ExecuteNonQuery();
                number = (int)cmd.ExecuteScalar(); 
                cmd.Parameters.Clear();
                Close();
            }
            catch (Exception ex)
            {
                wysc.systemlog(ex.Message, "~/err/sql");
                number = 0;
            }
            return number;
        }
这样改，说没给@pkID提供参数

Answer 15

哪有那么麻烦呀  把执行语句换成获取数据集的语句  就可以了        objConn=new OleDbConnection();
        objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=XXX;User ID=sa;pwd=sa;Initial Catalog=HEGENG";
        objConn.Open();        objAdapter = new OleDbDataAdapter("存储过程名 参数", objConn);
        objBuider = new OleDbCommandBuilder(objAdapter);
        objDs = new DataSet();
        objAdapter.Fill(objDs);objDs这里就有返回值了
objDs.table[0]........

Answer 16

  /// <summary>
        /// 增、删、改操作数据库的方法（存储过程）
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="VariableName">参数集合</param>
        /// <param name="Values">值集合</param>
        /// <returns>返回插入ID号</returns>
        public static int ExecProcedurei(string ProcedureName, string[] VariableName, object[] Values)
        {
            int number = 0;
            string inc = ""; //参数
            try
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                for (int i = 0; i < VariableName.Length; i++)
                {
                    if (i == VariableName.Length - 1)
                    {
                        //cmd.Parameters("@" + VariableName[i], Values[i]).Direction = ParameterDirection.ReturnValue; ;
                        cmd.Parameters.Add("@" + VariableName[i], Values[i]);
                        cmd.Parameters["@" + VariableName[i]].Direction = ParameterDirection.Output;
                        inc = "@" + VariableName[i].ToString();                    }
                    else
                    {
                        cmd.Parameters.Add("@" + VariableName[i], Values[i]);
                    }
                }                Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                object o = cmd.Parameters[inc].Value;
                //number = (int)cmd.Parameters[inc].Value;
                //number = (int)cmd.Parameters["@pkid"].Value; 
                number = (int)o;
                cmd.Parameters.Clear();
                Close();
            }
            catch (Exception ex)
            {
                wysc.systemlog(ex.Message, "~/err/sql");
                number = 0;
            }
            return number;
        }
改好了，多谢大家！！
create proc inbbs2
@userID int,
@uname nvarchar(100),
@classID int ,
@ifok int,
@click int,
@title nvarchar(200),
@con nvarchar(1000),
@pkid   int   output  
as
insert into bbs values(@userID,@uname,@classID,@ifok,@click,@title,@con)
set @pkid=@@identity 
    return @pkid
go改好了，多谢大家！！改好了，多谢大家！！