0
public void Insert(Order order)
{

    SqlConnection con = DACHelper.GetConnection();
    SqlCommand cmd = new SqlCommand(INSERT, con);
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.Parameters.AddWithValue("@Category_Id", order.Category.Id);
    cmd.Parameters.AddWithValue("@Item_Id", order.Item.Id);
    con.Open();
    using (con)
    {
        SqlTransaction tran = con.BeginTransaction();
        cmd.Transaction = tran;
        try
        {
            order.Id= Convert.ToInt32(cmd.ExecuteScalar());
            new OrderMailsDAC().Insert(order.Id, order.Mail , tran);
            tran.Commit();
        }
        catch (Exception)
        {
            tran.Rollback();
            throw;
        }
    }
}

then I inserted the order.Id in the following Insert function of OrderMails Class.

public void Insert(int orderId,OrderConfirmationMail mails,SqlTransaction tran)
{
    SqlConnection con = tran.Connection;
    SqlCommand cmd = new SqlCommand(INSERT, con);
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.Transaction = tran;
    cmd.Parameters.AddWithValue("@Mail", mails.Mail);
    cmd.Parameters.AddWithValue("@Order_Id", orderId);
    cmd.ExecuteNonQuery();
}

The problem here is that the order.Id value when inserted into another Insert of OredrMail then it stores zero at that place.. The Column is autoincremented , ad.Id which we have stored in Order table ...

Xiong Chiamiov
  • 13,076
  • 9
  • 63
  • 101
Sajjad
  • 3
  • 4
  • 2
    Can you show the real text of the INSERT query? – Steve Jul 23 '16 at 13:54
  • Possible duplicate of [ExecuteScalar vs ExecuteNonQuery when returning an identity value](http://stackoverflow.com/questions/14246744/executescalar-vs-executenonquery-when-returning-an-identity-value) – Jan Köhler Jul 23 '16 at 13:57
  • 2
    BTW - best to change `throw ex` to just `throw`. `throw` rethrows the exception as-is, including the stacktrace. If you `throw ex` the stacktrace starts there and everything previous is lost. – Scott Hannen Jul 24 '16 at 12:40

1 Answers1

2

ExecuteScalar returns the first column of the first row returned. Typically we'd just have the procedure select a single value if we're expecting to receive a single value.

in your procedure declare a variable like

declare @id

then immediately after your insert,

set @id = scope_identity()

then when the procedure is done executing

select @id

You can actually condense all of that to

insert ....whatever
select scope_identity()

I just have a tendency to be more explicit in case any other operations in between replace the value of scope_identity().

Scott Hannen
  • 27,588
  • 3
  • 45
  • 62
  • then i Inserted the order.Id in the Following Insert of OrderMails class method INsert. public void Insert(int orderId,OrderConfirmationMail mails,SqlTransaction tran) { SqlConnection con = tran.Connection; SqlCommand cmd = new SqlCommand(INSERT, con); cmd.CommandType = System.Data.CommandType.Text; cmd.Transaction = tran; cmd.Parameters.AddWithValue("@Mail", mails.Mail); cmd.Parameters.AddWithValue("@Order_Id", orderId); cmd.ExecuteNonQuery(); } – Sajjad Jul 25 '16 at 04:30