使用C#执行存储过程时出现错误 [英] I have an error when excuting Stored Procedure using C#

问题描述

错误:
正式参数"@SupplierID"未声明为OUTPUT参数，但实际参数已传递至请求的输出中.


存储的Proc:

Error:
The formal parameter "@SupplierID" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.


Stored Proc:

GO
CREATE PROCEDURE ViewMaintenance
	@FormID nchar(5),
	@SupplierID nchar(5),
	@PlateNumber nchar(6),
	@ServiceForm xml
AS
	SET NOCOUNT ON;
	
	Select FormID, SupplierID, PlateNumber, ServiceForm
	From Maintenance
	Where FormID = @FormID AND SupplierID = @SupplierID
	AND PlateNumber = @PlateNumber AND ServiceForm IS NULL
GO

背后的代码:

Code Behind:

SqlConnection conn = new SqlConnection("Data Source=JOSE-PC;Initial Catalog=AKDB;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("ViewMaintenance",conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter formid_param = cmd.Parameters.Add("@FormID", SqlDbType.Int);
            SqlParameter supplierid_param = cmd.Parameters.Add("@SupplierID", SqlDbType.NChar,5);
            SqlParameter platenumber_param = cmd.Parameters.Add("@PlateNumber", SqlDbType.NChar,6);

            formid_param.Direction = ParameterDirection.Input;
            supplierid_param.Direction = ParameterDirection.Output;
            platenumber_param.Direction = ParameterDirection.Output;

            formid_param.Value = Convert.ToInt32(txtFormNo.Text);

            if(conn.State.Equals(ConnectionState.Closed))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                
            }
            txtFormNo.Text = formid_param.ToString();
            txtRequestedTo.Text = supplierid_param.ToString();
            txtVehicle.Text = platenumber_param.ToString();

            conn.Close();

希望解决这个问题:(

Hoping the solve this problem:(

推荐答案

GO CREATE PROCEDURE ViewMaintenance 
@FormID nchar(5), 
@SupplierID nchar(5), 
@PlateNumber nchar(6)
 AS SET NOCOUNT ON;
 Select FormID, SupplierID,
 PlateNumber, ServiceForm From Maintenance Where FormID = @FormID AND SupplierID = @SupplierID AND 
PlateNumber = @PlateNumber AND ServiceForm IS NULL GO

并在代码中

And in code

SqlConnection conn = new SqlConnection("Data Source=JOSE-PC;Initial Catalog=AKDB;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("ViewMaintenance",conn);
            cmd.CommandType = CommandType.StoredProcedure;
 
            SqlParameter formid_param = cmd.Parameters.Add("@FormID", SqlDbType.Int);
            SqlParameter supplierid_param = cmd.Parameters.Add("@SupplierID", SqlDbType.NChar,5);
            SqlParameter platenumber_param = cmd.Parameters.Add("@PlateNumber", SqlDbType.NChar,6);
 
            formid_param.Direction = ParameterDirection.Input;
            supplierid_param.Direction = ParameterDirection.Input;
            platenumber_param.Direction = ParameterDirection.Input;
 
            formid_param.Value = Convert.ToInt32(txtFormNo.Text);
 
            if(conn.State.Equals(ConnectionState.Closed))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                
            }
            txtFormNo.Text = formid_param.ToString();
            txtRequestedTo.Text = supplierid_param.ToString();
            txtVehicle.Text = platenumber_param.ToString();
 
            conn.Close();

请按如下所示更改代码...

please change the code as follows...

supplierid_param.Direction = ParameterDirection.Input;
platenumber_param.Direction = ParameterDirection.Input;

这可能会解决您的问题.

问候
塞巴斯蒂安

this might solve your issue.

Regards
Sebastian

请删除这些行，

Please remove the lines,

formid_param.Direction = ParameterDirection.Input;
supplierid_param.Direction = ParameterDirection.Output;
platenumber_param.Direction = ParameterDirection.Output;

由于ParameterDirection是可选的，默认值为Input.

如果要从SP获取输出，则在C#中使用ParameterDirection.Output并在SP中进行声明，例如

....

Since ParameterDirection is an optional one.The default is Input.

If you want the get output from SP then use ParameterDirection.Output in C# and declaration in SP like

....

@SupplierID nchar(5) output,
@PlateNumber nchar(6) output,

....

....

这篇关于使用C#执行存储过程时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！