I have an error when excuting Stored Procedure using C#

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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

Stored Proc:

SQL

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:

C#

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

Posted 12-Jun-12 23:31pm

EvanJo

Updated 12-Jun-12 23:55pm

Sandeep Mewara

v2

Add a Solution

Comments

RDBurmon 13-Jun-12 9:24am

Thanks Everyone who replied to this thread , So EvanJo, I think you have got enough responses and you should be able to mark it as your answer and close the thread. Please do so.

5 solutions

Solution 1

please change the code as follows...

C#

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

this might solve your issue.

Regards
Sebastian

Posted 12-Jun-12 23:38pm

Sebastian T Xavier

Solution 2

Please remove the lines,

C#

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

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

....

SQL

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

....

Posted 13-Jun-12 0:00am

Manivannan Shan

Updated 13-Jun-12 3:22am

v2

Comments

EvanJo 13-Jun-12 6:09am

I already update my SP as you said, but I have another error

It says like this : Procedure or function 'ViewMaintenance' expects parameter '@ServiceForm', which was not supplied.

Tony Tom.k 13-Jun-12 7:39am

Remove that @ServiceForm your sps declaration part Your not Passing any value then why you are declared it as input parameter

Tony Tom.k 13-Jun-12 7:39am

GOCREATE 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

Solution 3

Hi,
As your stored procedure says that

SQL

@SupplierID

and

SQL

@PlateNumber

is not output parameter and you still want to use it as output parameter.. Definitely it will give an error.. It can only take the data from front-end but it will not return any data from back-end to front-end.

You need to view this article:
Stored Procedure Data Gateway[^]
Sql Server - How to write a Stored procedure in Sql server[^]

All the best.
--AK

Posted 13-Jun-12 0:17am

AmitKumar89

Solution 4

Chage SP like this

SQL

CREATE PROCEDURE ViewMaintenance
	@FormID nchar(5),
	@SupplierID nchar(5) output,
	@PlateNumber nchar(6) output,
	@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

Posted 13-Jun-12 1:17am

Michael_Jacsi

Updated 13-Jun-12 3:25am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tony Tom.k · Accepted Answer · 2012-06-13T01:42:00

SQL

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

C#

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();