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."

What is the content of `txtPageTitle.Text`? – CodeCaster Nov 24 '14 at 13:13 — CodeCaster, Nov 24 '14 at 13:13
content means the datatype ? – Rahul Sutar Nov 24 '14 at 13:18 — Rahul Sutar, Nov 24 '14 at 13:18
No, the content. The value it holds. – CodeCaster Nov 24 '14 at 13:18 — CodeCaster, Nov 24 '14 at 13:18
The values is in text format – Rahul Sutar Nov 24 '14 at 13:20 — Rahul Sutar, Nov 24 '14 at 13:20
use CommandType = CommandType.StoredProcedure; – A.K. Nov 24 '14 at 13:25 — A.K., Nov 24 '14 at 13:25

Soner Gönül · Accepted Answer · 2014-11-24T13:20:56.637

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?

edited Nov 24 '14 at 13:20

answered Nov 24 '14 at 13:14

Soner Gönül

97,193
102
206
364

I am adding after the line, 'var cmd = new SqlCommand("AddingPagesInGrid", conn);' – Rahul Sutar Nov 24 '14 at 13:16
now I am getting error as **Procedure or function 'AddingPagesInGrid' expects parameter '@Active', which was not supplied.** – Rahul Sutar Nov 24 '14 at 13:18
Can you describe it please – Rahul Sutar Nov 24 '14 at 13:20
@RahulSutar Your store procedure have a parameter called `@Active` but your `SqlCommand` doesn't. – Soner Gönül Nov 24 '14 at 13:21
I did it, but it is with 'AddWithValue' Can you tell me about .Add() ? how to implement that – Rahul Sutar Nov 24 '14 at 13:25
1

@RahulSutar You can always read the documentation first. For example; `cmd.Parameters.Add("@page_title", SqlDbType.NVarChar).Value = txtPageTitle.Text;` – Soner Gönül Nov 24 '14 at 13:27
@RahulSutar = A simple search will provide you the Syntax! – Rahul Singh Nov 24 '14 at 13:27
@SonerGönül: Thanks will implement that way, a bit of question. My Active/Inactive is coming from dropdownlist and in Table it is the bit DataType. I want to set 1 for Active and 0 for Inactive. How to achieve that ? – Rahul Sutar Nov 24 '14 at 13:33
@SonerGönül: The Active value is going 0 into the table, I want 1 to be added on Active and 0 on Inactive. My code for that is 'cmd1.Parameters.Add("@Active", SqlDbType.Bit).Value = Convert.ToInt32(ddlActiveInactive.SelectedValue);' – Rahul Sutar Nov 24 '14 at 14:58

Not Inserting values in Table

1 Answers1