Stored Proceedure Exception

Question

0.00/5 (No votes)

See more:

i have used, two tables, i m inserting in two tables at a time, and tables are rellated with foreign key:

C#

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>");
        }

The Stored Proceedure is:

SQL

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

The Exception: I m getting is:
There is already an open DataReader associated with this Command which must be closed first.

Please make me, resolve this...

Posted 28-Feb-13 16:30pm

Ankit_Sharma1987

Updated 28-Feb-13 17:16pm

AshishChaudha

v3

Add a Solution

Comments

Nandakishore G N 1-Mar-13 1:53am

this is not the pattern to do insertion for multiple tables.Use Transactions

3 solutions

Solution 1

Why you are using

SqlDataReader dr = cm.ExecuteReader();
SqlDataReader dr1 = cm1.ExecuteReader();

to insert the data,

try to use,
cm.ExecuteScalar() to insert the values.

hopes this can solve your problem.

Posted 28-Feb-13 18:31pm

Hrushikesh_phapale

Comments

Ankit_Sharma1987 1-Mar-13 0:41am

sir i thiknk, Executescalar is used, when we have to find out...specific column, or insert into, specific column

Solution 2

Yes the exception raised because without closing one datareader then you can not create another with same connection. You can update your code like

C#

using (var conn = new SqlConnection(connString))
{
    string sql1 = "INSERT INTO MyTable1(Name) VALUES ('Morning')";
    string sql2 = "INSERT INTO MyTable2(Name) VALUES ('Evening')";
    var cmd1 = new SqlCommand(sql1, conn);
    var cmd2 = new SqlCommand(sql2, conn);
    conn.Open();
    using(SqlDataReader reader1 = cmd1.ExecuteReader()){}
    using(SqlDataReader reader2 = cmd2.ExecuteReader()){}
}

But I suggest if you need to execute sp where only action query(insert/update/delete) and no return row set then execute ExecuteNonQuery method of SqlCommand object.

C#

using (var conn = new SqlConnection(connString))
{
    string sql1 = "INSERT INTO MyTable1(Name) VALUES ('Morning')";
    string sql2 = "INSERT INTO MyTable2(Name) VALUES ('Evening')";
    var cmd1 = new SqlCommand(sql1, conn);
    var cmd2 = new SqlCommand(sql2, conn);
    conn.Open();
    cmd1.ExecuteNonQuery();
    cmd2.ExecuteNonQuery();
}

Posted 28-Feb-13 18:43pm

S. M. Ahasan Habib

Comments

Ankit_Sharma1987 1-Mar-13 0:50am

Sir thanks, for...suggest...'
I have one Question...?
sir, i have to use, stored proceedure only..
If i have used it...then why should i, write code like this...
string sql1 = "INSERT INTO MyTable1(Name) VALUES ('Morning')";

Ankit_Sharma1987 1-Mar-13 0:52am

please explain...i would be...highly supportable...

S. M. Ahasan Habib 1-Mar-13 1:06am

Actually i show up an example. You can change it and execute it with stored procedure the code like
string spName = "usp_InsertMyTable";
var cmd1 = new SqlCommand(spName , conn);
cmd1.CommandType = CommandType.StoredProcedure;
//Add paraters to the cmd1 command
cmd1.ExecuteNonQuery().

Ankit_Sharma1987 1-Mar-13 1:09am

ok, sir, i m triyng...please..wait sir please..

S. M. Ahasan Habib 1-Mar-13 1:14am

Waiting for your feedback.

Ankit_Sharma1987 1-Mar-13 1:19am

Sir it is giving exception...
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__submenu__item_id__1BFD2C07". The conflict occurred in database "JIN", table "dbo.menu", column 'item_id'. The statement has been terminated.

Ankit_Sharma1987 1-Mar-13 1:22am

sir actually i have...attached table with foreign key...
i just want, data to inserted in menu, and sub menu both, in that equalent case...i m confused..what to do...
i should have to use scope_identity???
i m giving you, stored proceedure, which i have used....
ALTER PROCEDURE [dbo].[str_collection]
@itemname nvarchar(50),
@item_img nvarchar(MAX),
@subitem_name nvarchar(50),
@item_rate decimal(18,2),
@item_descript nvarchar(100)
AS
BEGIN
SET NOCOUNT ON
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)
SELECT @item_id=SCOPE_IDENTITY()
INSERT INTO submenu(item_id,item_descript,item_rate,subitem_name)values(@item_id,@item_descript,@item_rate,@subitem_name)
END

S. M. Ahasan Habib 1-Mar-13 1:26am

Actually it is data relation establishment problem(database issue, your application code is ok). You just execute sp from your sql server management studio with parameters which you passed from your web application and easily address/solve the problem. You can find the parameters value easily if you run sqlserver profiler and track the spcalling from web application.

S. M. Ahasan Habib 1-Mar-13 1:36am

Please visit the link
http://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

_Amy · Accepted Answer · 2013-02-28T19:20:00

Solution 3

Try this:

C#

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;    
            cnn.Open(); //Open the connection to execute first command
            SqlCommand cm = new SqlCommand("str_collection", cnn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.Add("@itemname", txt_itemname.Text);
            cm.Parameters.Add("@item_img", img);
            cm.Parameters.Add("@item_descript", txt_descript.Text);
            cm.Parameters.Add("@item_rate",txt_rate.Text);
            cm.Parameters.Add("@subitem_name", txt_subitem.Text);
            cm.ExecuteNoneQuery();
            cnn.Close();//Close the connection
            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>");
    }
}

--Amit

Posted 28-Feb-13 19:20pm

_Amy

Updated 28-Feb-13 19:52pm

v2

Comments

Ankit_Sharma1987 1-Mar-13 1:29am

ok, but it is inserting in onlyin menu table..
it is not inserting, in...submenu table, please chek my stored proceedure....
ALTER PROCEDURE [dbo].[str_collection]
@itemname nvarchar(50),
@item_img nvarchar(MAX),
@subitem_name nvarchar(50),
@item_rate decimal(18,2),
@item_descript nvarchar(100)
AS
BEGIN
SET NOCOUNT ON
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)
SELECT @item_id=SCOPE_IDENTITY()
INSERT INTO submenu(item_id,item_descript,item_rate,subitem_name)values(@item_id,@item_descript,@item_rate,@subitem_name)
END

_Amy 1-Mar-13 1:31am

Can you tell me the error description in this forcedure?

Ankit_Sharma1987 1-Mar-13 1:32am

yes, it is giving exception....
Procedure or function 'str_collection' expects parameter '@subitem_name', which was not supplied.

_Amy 1-Mar-13 1:34am

Yes, See your procedure is not having a parameter named @subitem_name which you are passing from front-end. Try to add and check debug that again.

Ankit_Sharma1987 1-Mar-13 1:38am

ALTER PROCEDURE [dbo].[str_collection]
@itemname nvarchar(50),
@item_img nvarchar(MAX),
@subitem_name nvarchar(50),

sir i have declared it, in proceedure....
and also mentioned it in...INSERT Query....i have declared it...then, whats the, problem???

Ankit_Sharma1987 1-Mar-13 1:39am

Sir please check my procedure..
I m Sennding it again..

USE [JIN]
GO
/****** Object: StoredProcedure [dbo].[str_collection] Script Date: 03/01/2013 11:03:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[str_collection]
@itemname nvarchar(50),
@item_img nvarchar(MAX),
@subitem_name nvarchar(50),
@item_rate decimal(18,2),
@item_descript nvarchar(100)
AS
BEGIN
SET NOCOUNT ON
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)
SELECT @item_id=SCOPE_IDENTITY()
INSERT INTO submenu(item_id,item_descript,item_rate,subitem_name)values(@item_id,@item_descript,@item_rate,@subitem_name)
END

_Amy 1-Mar-13 1:44am

Are you getting the same error?

_Amy 1-Mar-13 1:51am

I am updating the solution just check it.

Ankit_Sharma1987 1-Mar-13 2:02am

Ohhhh Thnkkk You, Thankyou...thankyou...so much sir...i m happy now...

_Amy 1-Mar-13 2:07am

Welcome.. :)