The Disappearance of Parameters?

Last week after uploading an updated version of my ASP web app to the server, it showed an error at the place where data is inserted into the database using a stored procedure.

The error message is “Procedure or Function ‘A’ expects parameter ‘@B’, which was not supplied”.

I double checked my code. Yes, all the required parameters present in the web service code. Yes, the stored procedure is written correctly. Yes, the code does supply the parameter. So, why is there an error?

I searched on Google and found that there were many people facing this problem. Yet, their solutions are all different from each other. So, I decided to share those I found. Besides, in the end, I will also talk about how I solved it eventually.

ASP Web Service
ASP Web Service. Image Credits: Alik Levin’s

Case #1: Stored Procedure Is Written Wrongly

CS Code

publiv void insertData()
{
 SqlConnection con = new SqlConnection(str_con);
 con.Open();
 SqlCommand cmd = new SqlCommand();
 cmd.CommandText = "InsertInfo";
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Connection = con;

 string name = Convert.ToString(txtName.Text);

 cmd.Parameters.Add("@s_name", SqlDbType.VarChar).Value = name;

 cmd.ExecuteNonQuery();
 con.Close();
}

Stored Procedure

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[bkg_Insert_Members_FromCompanyID] 
@s_name varchar(50)
AS
-- Variable Declaration
DECLARE @iReturn INT
INSERT INTO Members (member_name) Values (@s_name)
SET @iReturn = @@error
RETURN @iReturn

Although both CS code and stored procedure are written correctly, due  to the fact that there is one line “exec <stored procedure name>” at the end of the stored procedure, the error happens. To solve the problem, just need to remove the unnecessary line.

Reference: Procedure or Function ‘InsertInfo’ expects parameter ‘@s_name’, which was not supplied.

Case #2: Null Value Is Passed

If a null value needs to be inserted into database table using stored procedure, DBNull.Value should be used as demonstrated below.

cmd.Parameters.Add("@s_name", SqlDbType.VarChar).Value = DBNull.Value;

The error will be raised if null value is passed instead of DBNull.Value.

Reference: Procedure expects parameter which was not supplied

Case #3: Wrong CommandType

When the program is calling a stored procedure, the CommandType should be set to StoredProcedure as shown below.

cmd.CommandType = CommandType.StoredProcedure;

Due to the fact that the default value of CommandType is Text, so if  this line is missing, it will cause the same error as well.

References:

  1. Procedure expects parameter which was not supplied
  2. Procedure or function expects parameter which was not supplied

Case #4: Name Mismatched

The same error will be thrown if the name of the data field used in the CS code is different from the parameter name of the stored procedure or the name of the field in the table. I don’t know what to say besides \bat the programmer.

Reference:

  1. Procedure or function expects parameter ‘@parm’, which was not supplied
  2. Procedure or function ‘login’ expects parameter ‘@username’, which was not supplied.

Case #5: Wrong Order

Yes, there is a value assigned to the parameter of the stored procedure in the CS code. However, the SQLCommand object is later replaced. Oh my… I have no comment about this.

Reference: Procedure or Function expects parameter, which was not supplied.

Ah-ha-ha-ha, problem solved!
Ah-ha-ha-ha, problem solved!. Image Credits: Little Busters! EX

So, how about my one? It turns out that it is because my web service project is not updated after doing the compilation. After rebuilding the web service project, everything is working again.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s