Not Inserting values in Table

Question

I have written an Stored procedure for inserting values in the table. Please see the SP for your reference:-

ALTER PROCEDURE [dbo].[AddingpagesinGrid] (@page_title       NVARCHAR(100), 
                                 @page_description NVARCHAR(max), 
                                 @meta_title       NVARCHAR(255), 
                                 @meta_keywords    NVARCHAR(255), 
                                 @meta_description NVARCHAR(1000), 
                                 @Active           BIT) 

AS BEGIN SET nocount ON;

  BEGIN 
      INSERT INTO [tbl_pages] 
                  ([page_title], 
                   [page_description], 
                   [meta_title], 
                   [meta_keywords], 
                   [meta_description], 
                   [active]) 
      VALUES      ( @page_title, 
                    @page_description, 
                    @meta_title, 
                    @meta_keywords, 
                    @meta_description, 
                    @Active) 
  END 

  SELECT [page_title], 
         [page_description], 
         [meta_title], 
         [meta_keywords], 
         [meta_description], 
         [active] 
  FROM   tbl_pages 

END

go

Also see the code-behind:-

protected void btnAdd_Click(object sender, EventArgs e)
    {
        conn.Open();
        var cmd = new SqlCommand("AddingPagesInGrid", conn);
        cmd.Parameters.AddWithValue("@page_title", txtPageTitle.Text);
        cmd.Parameters.AddWithValue("@page_description", txtPagedesc.Text);
        cmd.Parameters.AddWithValue("@meta_title", txtmetatitle.Text);
        cmd.Parameters.AddWithValue("@meta_keywords", txtMetakeywords.Text);
        cmd.Parameters.AddWithValue("@meta_description", ddlActiveInactive.SelectedIndex);
        cmd.ExecuteNonQuery();
        conn.Close();
        ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('User details saved sucessfully');window.location ='csrpage.aspx';", true);
    }

it is not working and giving me error as

"Procedure or function 'AddingPagesInGrid' expects parameter '@page_title', which was not supplied."

Answer 1

I think you forget to set your CommandType property and your program thinks your "AddingPagesInGrid" as a Text which is the default value of a CommandType.

Just add;

cmd.CommandType = CommandType.StoredProcedure;

And use using statement to dispose your SqlConnection and SqlCommand.

using(SqlConnection conn = new SqlConnection(conString))
using(SqlCommand cmd = conn.CreateCommand())
{

}

And please don't use AddWithValue method. It may generate unexpected results sometimes. Use .Add method or it's overloads.

Read: Can we stop using AddWithValue() already?