Click here to Skip to main content
12,956,234 members (59,978 online)
Rate this:
 
Please Sign up or sign in to vote.
i have used, two tables, i m inserting in two tables at a time, and tables are rellated with foreign key:

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:
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
Updated 28-Feb-13 17:16pm
v3
Comments
Nandakishorerao 1-Mar-13 1:53am
   
this is not the pattern to do insertion for multiple tables.Use Transactions
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
  Permalink  
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
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.
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();
}
  Permalink  
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Try this:
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
  Permalink  
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.. :)

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
OriginalGriff 6,869
CHill60 3,550
Maciej Los 3,243
ppolymorphe 2,070
Jochen Arndt 1,973


Advertise | Privacy | Mobile
Web01 | 2.8.170525.1 | Last Updated 1 Mar 2013
Copyright © CodeProject, 1999-2017
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