我的存储过程有两个输入参数,1个输出参数,在数据库直接用是没有问题,而且也返回输出值,但是一写到程序里就出问题,存储过程能执行,但是输出参数没法返回,我的程序代码是这样的:
string XH;
this.my_Connect = new OleDbConnection(WebConfiguration.ConnectionString);
this.my_Connect.Open();
this.my_Command = new OleDbCommand();
this.my_Command.Connection = my_Connect;
this.my_Command.CommandType = CommandType.StoredProcedure;
this.my_Command.CommandText = "PROCNAME";this.my_Command.Parameters.Add("@BMXJ",OleDbType.Char);
this.my_Command.Parameters["@BMXJ"].Value=str_ccdwbm;this.my_Command.Parameters.Add("@TBRQ",System.Data.OleDb.OleDbType.VarChar);
this.my_Command.Parameters["@TBRQ"].Value=dt_tbrq;this.my_Command.Parameters.Add("@XH",OleDbType.VarChar,16);
this.my_Command.Parameters["@XH"].Direction = ParameterDirection.Output;this.my_Command.ExecuteNonQuery();XH = this.my_Command.Parameters["@XH"].Value.ToString();
this.my_Command.Dispose();
this.my_Connect.Close();return XH;用断点执行的时候发现XH没有值,请大家帮帮忙看看,谢谢~~
string XH;
this.my_Connect = new OleDbConnection(WebConfiguration.ConnectionString);
this.my_Connect.Open();
this.my_Command = new OleDbCommand();
this.my_Command.Connection = my_Connect;
this.my_Command.CommandType = CommandType.StoredProcedure;
this.my_Command.CommandText = "PROCNAME";this.my_Command.Parameters.Add("@BMXJ",OleDbType.Char);
this.my_Command.Parameters["@BMXJ"].Value=str_ccdwbm;this.my_Command.Parameters.Add("@TBRQ",System.Data.OleDb.OleDbType.VarChar);
this.my_Command.Parameters["@TBRQ"].Value=dt_tbrq;this.my_Command.Parameters.Add("@XH",OleDbType.VarChar,16);
this.my_Command.Parameters["@XH"].Direction = ParameterDirection.Output;this.my_Command.ExecuteNonQuery();XH = this.my_Command.Parameters["@XH"].Value.ToString();
this.my_Command.Dispose();
this.my_Connect.Close();return XH;用断点执行的时候发现XH没有值,请大家帮帮忙看看,谢谢~~
this.my_Command.Parameters["@BMXJ"].Value=str_ccdwbm;this.my_Command.Parameters.Add("@TBRQ",System.Data.OleDb.OleDbType.VarChar);
this.my_Command.Parameters["@TBRQ"].Value=dt_tbrq;这两句的this.my_Command.Parameters.Add("@TBRQ",OleDbType.VarChar, 这里加上Size)
CREATE PROCEDURE CZYWLXTZ_PROC_GETXH (@BMXJ CHAR(8),@TBRQ SMALLDATETIME,@XH VARCHAR(14) OUTPUT)
AS
DECLARE @MAXXH NUMERIC(4),@T_XH CHAR(4)
set transaction isolation level serializable
BEGIN TRAN
IF EXISTS(SELECT 1 FROM CZYWLSTZ_MAXXH WHERE TBRQ=@TBRQ AND BMXJ=@BMXJ)
BEGIN
SELECT @MAXXH=MAXXH FROM CZYWLSTZ_MAXXH WHERE TBRQ=@TBRQ AND BMXJ=@BMXJ
SELECT @MAXXH=@MAXXH+1
UPDATE CZYWLSTZ_MAXXH SET MAXXH=@MAXXH WHERE TBRQ=@TBRQ AND BMXJ=@BMXJ
END
ELSE
BEGIN
SELECT @MAXXH=1
INSERT INTO CZYWLSTZ_MAXXH VALUES (@BMXJ,@MAXXH,@TBRQ)
END
SELECT @T_XH=CONVERT(CHAR(4),@MAXXH)
WHILE LEN(LTRIM(RTRIM(@T_XH)))<4
BEGIN
SELECT @T_XH='0'+@T_XH
END
SELECT @XH=CONVERT(NUMERIC(14),SUBSTRING(@BMXJ,1,4)+
SUBSTRING(CONVERT(CHAR(8),@TBRQ,112),3,6)+@T_XH)
IF @@ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN 1
END
COMMIT TRAN
RETURN
我执行下面语句:declare @XH varchar(14)
exec PROCNAME '12345678','2005-6-10',@XH output
就有返回值的
this.my_Command.Parameters["@TBRQ"].Value=dt_tbrq;
这里的参数类型和在存储过程的参数类型不一致,该该试试
this.my_Command.Parameters["@BMXJ"].Value = "12345678";
this.my_Command.Parameters["@TBRQ"].Value = "2005-6-10";
再试试。
SqlCommand cmdTemp;
SqlParameter spTemp;
string strI;
try
{
OpenDataBase();
cmdTemp=new SqlCommand(strProcedureName,pubDataBase.conn);
cmdTemp.CommandType=CommandType.StoredProcedure;
cmdTemp.Parameters.Add (strParameterName,strDataValue);
spTemp=cmdTemp.Parameters.Add("@intI",SqlDbType.Int);
spTemp.Direction=ParameterDirection.Output;
cmdTemp.ExecuteNonQuery();
strI=spTemp.Value.ToString();//一样的返回结果
strI=cmdTemp.Parameters["@intI"].Value.ToString();//一样的返回结果
cmdTemp.Dispose();
CloseConn();
return strI;
}
catch(Exception E)
{
MessageBox.Show(E.Message);
return "1";
}
........
conn.Open();
SqlCommand cmd=new SqlCommand("",conn);
int intReturn = 0;
cmd.CommandText="spName";
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Clear();cmd.Parameters.Add ("@name",strName);SqlParameter Oper=cmd.Parameters.Add ("@out_value",SqlDbType.Int);
Oper.Direction =ParameterDirection.Output;cmd.ExecuteNonQuery();
intReturn = Convert.ToInt32(Oper.Value);
cmd = null;
conn.Close();
conn.Dispose();
return(intReturn);我的代码,是肯定可以执行的,因为我已经执行了。你试试把
yes4pierce(pierce) 我的程序中有output的阿