Procedure or Function expects parameter, which was not supplied : Resolved

|

I created a stored procedure as follows:

Create procedure spInsertTable1 

@Name varchar(50), 

@Phone Varchar(50) 

As 

Begin 

 Insert into Table1(Name,Phone) values (@Name,@Phone) 

End

Now I wanted to insert the values from front end using this stored procedure. To achieving this thing I wrote the below mentioned code.

protected void btnSubmit_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(“Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=localhost”);

            SqlCommand com = new SqlCommand();

            com.Connection = con;

            com.CommandType = CommandType.StoredProcedure;

            com.CommandText = “spInsertTable1”;

            com.Parameters.Add(“@Name”, SqlDbType.VarChar, 50, this.txtName.Text.Trim());

            com.Parameters.Add(“@Phone”, SqlDbType.VarChar, 50, this.txtPhone.Text.Trim());

            con.Open();

            com.ExecuteNonQuery();

            con.Close();

        }

But when I execute this code it gives me runtime eror

“Procedure or Function ‘spInsertTable1’ expects parameter ‘@Name’, which was not supplied.”

I cross check the code all was fine; I goggled the web but even though couldn’t find any solution. What most people suggested was already inside my code. So after spending some time to solve this strange situation, I came to know about the workaround to resolve this.  So below is the proper way to execute this.

Code behind Code

protected void btnSubmit_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(“Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=localhost”);

            con.Open();

            SqlCommand com = new SqlCommand(“spInsertTable1”,con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add(“@Name”, SqlDbType.VarChar).Value = this.txtName.Text.Trim();

            com.Parameters.Add(“@Phone”, SqlDbType.VarChar).Value = this.txtPhone.Text.Trim();

            com.ExecuteNonQuery();

            con.Close();

        }

Code execution details

  1. *Initialize SqlConnection object using connection string (con is SqlConnection class object.).
  2. *Open the connection.
  3. *Initialize SqlCommand object using stored procedure name and SqlConnection object (com is SqlCommand class object, spInsertTable1 is stored procedure name and con is SqlConnection class object.).
  4. *Set CommandType property of SqlCommand object (Here we are setting this property for use of stored procedure.).
  5. *Adding parameters to SqlCommand object in the sequence as defined in stored procedure (@Name and @Phone are parameters defined in the stored procedure.).
  6. *Call ExecuteNonQuery() method of SqlCommand object to permorm DML command.
  7. *Close the existing connection.

*Though the earlier approach was correct but even than it was not working. But the later approach was working fine.