SQL Server stored procedure insert query

Question

In my C# code I am using :

public void Add(int ID)
{
    foreach (AccessoireToSell item in OrderToAdd.Accessoires)
    {
        Adder(item.Ref, item.Qte, item.SellPrice, ID);
    }
}

private void Adder(int refid,int Qtetosell,string sellprice,int ID)
{
    SqlParameter[] param = new SqlParameter[4];

    param[0] = new SqlParameter("@AccessoireID", SqlDbType.Int);
    param[0].Value = refid;

    param[1] = new SqlParameter("@Qte", SqlDbType.Int);
    param[1].Value = Qtetosell;

    param[2] = new SqlParameter("@Price", SqlDbType.VarChar, 50);
    param[2].Value = sellprice;

    param[3] = new SqlParameter("@ORDERID", SqlDbType.Int);
    param[3].Value = ID;

    Function.Execute(param, "AccessoiresAddOrder");
}

The procedure AccessoiresAddOrder :

ALTER PROCEDURE [dbo].[AccessoiresAddOrder]
    @ORDERID int,
    @AccessoireID int,
    @Qte int,
    @Price Varchar(50)
AS
    INSERT INTO [dbo].[Accessoires_OrderDetails] ([orderID], [AccessoireID],[Qte], [Price])
    VALUES (@ORDERID, @AccessoireID, @Qte, @Price)

I don't understand why the records get inserted 2 times in a row. For example I insert a row from the Datagridview and I get the same row twice in my SQL Server table.

Please note that I checked the AccessoireToSell list counts during the execution as well it say for "Count = 2" in my table I find 4 records.

Execute method :

public void Execute(SqlParameter[] param, string ProcName)
{
    SqlCommand Cmd = new SqlCommand();
    Cmd.CommandText = ProcName;
    Cmd.CommandType = CommandType.StoredProcedure;

    if (param != null)
    {
        Cmd.Parameters.AddRange(param);
    }

    Cmd.Connection = Base.Connection;

    if (Base.Status() == true)
        Cmd.ExecuteNonQuery();
    else
        Base.Open();
        Cmd.ExecuteNonQuery();
}

That function is working well i tried to execute other insert procedure with it and it works all fine — Huster
– Huster, Commented Jan 7, 2017 at 20:17
if Base.Status() is true it will ExecuteNonQuery twice. Another case for using braces even on single line if statements. — Crowcoder
– Crowcoder, Commented Jan 7, 2017 at 20:28
Additionally, this method looks pretty leaky. You do not dispose your Command and it appears that do not close and dispose the connection in Base. — Crowcoder
– Crowcoder, Commented Jan 7, 2017 at 20:42

marc_s · Accepted Answer · 2017-01-07 21:18:35Z

4

Use this code:-

if (Base.Status() == true)
    Cmd.ExecuteNonQuery();
else
{
     Base.Open();
     Cmd.ExecuteNonQuery();
}

The difference is just add curly braces {....} to the else clause. The code without curly braces {} is executing the Cmd.ExecuteNonQuery() call twice.

edited Jan 7, 2017 at 21:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 7, 2017 at 20:29

ahmed abdelqader

3,58220 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server stored procedure insert query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related