Click here to Skip to main content
12,069,536 members (65,658 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# stored-procedure
Hello friends, I am inserting data in two tables which I have linked with Foreign key through a stored procedure.
ALTER PROCEDURE [dbo].[str_collection]
@itemname nvarchar(50),
@item_img nvarchar(MAX),
@subitem_name nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
declare @item_rate decimal(18,2)
declare @item_descript nvarchar(100)
declare @id varchar(50)
SET @id= COUNT(Getdate())
declare @item_id nvarchar(50)
SET @item_id=@itemname+left(convert(varchar,datepart(YYYY,GETDATE())),2)+@id
INSERT INTO menu(itemname,item_id,item_img)values(@itemname,@item_id,@item_img)
INSERT INTO submenu(item_descript,item_rate,subitem_name)values(@item_descript,@item_rate,@subitem_name)
END
C#Code-------------
protected void btn_submit_Click(object sender, EventArgs e)
   {
       try
       {
           string path = @"~/images/";
           if (FileUpload1.HasFile)
           {
               string path1 = Server.MapPath(path);
               string fnm = FileUpload1.FileName;
               string p = path1 + fnm;
               FileUpload1.SaveAs(p);
               string img = @"~/images" + fnm;
               cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
               SqlCommand cm = new SqlCommand("menu", cnn);
               cm.CommandType = CommandType.StoredProcedure;
               cm.CommandText = "str_collection";
               cm.Parameters.Add("itemname", SqlDbType.NVarChar).Value = txt_itemname.Text;
               cm.Parameters.Add("item_img", SqlDbType.NVarChar).Value = img;
               SqlCommand cm1 = new SqlCommand("submenu", cnn);
               cm1.CommandType = CommandType.StoredProcedure;
               cm1.CommandText = "str_collection";
               cm1.Parameters.Add("item_descript", SqlDbType.NVarChar).Value = txt_descript.Text;
               cm1.Parameters.Add("item_rate", SqlDbType.Decimal).Value = txt_rate.Text;
               cm1.Parameters.Add("subitem_name", SqlDbType.NVarChar).Value = txt_subitem.Text;
               cnn.Open();
               SqlDataReader dr = cm.ExecuteReader();
               SqlDataReader dr1 = cm1.ExecuteReader();
               cnn.Close();
               Response.Write("script>alert('Your Collection is Done Successfully!!')/script>");
           }
           else
           {
               Response.Write("<script>alert('File Uploading Failed')</script>");
           }
       }
       catch (Exception ex)
       {
          lbl_msg.Text= ex.Message;
           //Response.Write("<script>alert('There is some problem')</script>");
       }
   }

I am getting and exception:

Procedure or function 'str_collection' expects parameter '@subitem_name', which was not supplied.

please help..
Posted 28-Feb-13 7:12am
Edited 28-Feb-13 7:17am
v2
Comments
richcb 28-Feb-13 12:17pm
   
It means you are not passing the "@subitem_name" variable to the stored procedure. Just make sure you pass it.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Change your c# code: the SP is expecting @subitem_name and you are providing subitem_name
cm1.Parameters.Add("@subitem_name", SqlDbType.NVarChar).Value = txt_subitem.Text;

You will need to change the names of all parameters to match!
  Permalink  
Comments
Ankit_Sharma1987 28-Feb-13 12:28pm
   
Thanks sir...
Ankit_Sharma1987 28-Feb-13 12:32pm
   
Sir again it is dispyaying, the,same error...

Procedure or function 'str_collection' expects parameter '@subitem_name', which was not supplied.
ThePhantomUpvoter 28-Feb-13 13:49pm
   
Try cm.Parameters.Add(blah blah blah) instead
OriginalGriff 28-Feb-13 14:01pm
   
You realise that you need to provide the parameters to *both* SqlCommands, don't you? You aren't at teh moment!

BTW: You might find
cmd.Parameters.AddWithValue("@subitem_name", txt_subitem.Text);
easier to read.
Ankit_Sharma1987 28-Feb-13 22:22pm
   
protected void btn_submit_Click(object sender, EventArgs e)
{
cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
try
{
string path = @"~/images/";
if (FileUpload1.HasFile)
{
string path1 = Server.MapPath(path);
string fnm = FileUpload1.FileName;
string p = path1 + fnm;
FileUpload1.SaveAs(p);
string img = @"~/images" + fnm;

SqlCommand cm = new SqlCommand("menu", cnn);
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "str_collection";
cm.Parameters.Add("@itemname", txt_itemname.Text);
cm.Parameters.Add("@item_img", img);
SqlCommand cm1 = new SqlCommand("submenu", cnn);
cm1.CommandType = CommandType.StoredProcedure;
cm1.CommandText = "str_collection";
cm1.Parameters.Add("@item_descript", txt_descript.Text);
cm1.Parameters.Add("@item_rate",txt_rate.Text);
cm1.Parameters.Add("@subitem_name", txt_subitem.Text);
cnn.Open();
SqlDataReader dr = cm.ExecuteReader();
SqlDataReader dr1 = cm1.ExecuteReader();
cnn.Close();
Response.Write("<script>alert('Your Collection is Done Successfully!!')</script>");
}
}
catch (Exception ex)
{
lbl_msg.Text = ex.Message;
//Response.Write("<script>alert('There is some problem')</script>");
}

And My Stored Proceedure is
ALTER PROCEDURE [dbo].[str_collection]
@itemname nvarchar(50),
@item_img nvarchar(MAX)
AS
BEGIN
SET NOCOUNT ON
declare @subitem_name nvarchar(50)
declare @item_rate decimal(18,2)
declare @item_descript nvarchar(100)
declare @id varchar(50)
SET @id= COUNT(Getdate())
declare @item_id nvarchar(50)
SET @item_id=@itemname+left(convert(varchar,datepart(YYYY,GETDATE())),2)+@id
INSERT INTO menu(itemname,item_id,item_img)values(@itemname,@item_id,@item_img)
INSERT INTO submenu(item_id,item_descript,item_rate,subitem_name)values(@item_id,@item_descript,@item_rate,@subitem_name)
END
Ankit_Sharma1987 28-Feb-13 22:23pm
   
Error:
There is already an open DataReader associated with this Command which must be closed first.
what does this error means...
OriginalGriff 1-Mar-13 4:09am
   
It means that there is already a reader open on that connection - which there is because you open two of them in quick succession. Since readers are asynch, you can't open two at the same time. And why the heck are you using Readers anyway, when your SP does INSERT operations? Use ExecuteNonQuery instead.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 28 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100