Skullomania Skullomania - 1 year ago 79
SQL Question

Why does my SQL 'INSERT' statement execute twice?

I have the following Insert Command and I need to grab the Scope Identity for some order tracking later in the application

SqlCommand cmd1 = new SqlCommand("INSERT INTO [pharm_OrderID](UserID, RequestType, CreateDate) values (@UserID, @RequestType, @CreateDate); Select SCOPE_IDENTITY();", conn1);
cmd1.CommandType = CommandType.Text;

string strUserID = txtEmpID.Text;
cmd1.Parameters.Add("@UserID", SqlDbType.NVarChar, 50);
cmd1.Parameters["@UserID"].Value = strUserID;

string strRequestType = ddlReturnType.SelectedValue;
cmd1.Parameters.Add("@RequestType", SqlDbType.NVarChar, 50);
cmd1.Parameters["@RequestType"].Value = strRequestType;

string strCreateDate = lblOrderAttemptTime.Text;
cmd1.Parameters.Add("@CreateDate", SqlDbType.NVarChar, 50);
cmd1.Parameters["@CreateDate"].Value = strCreateDate;


string numScope = Convert.ToString(cmd1.ExecuteScalar());
lblOrderNum.Text = numScope;

But why does it insert twice?

Answer Source

You are executing it twice

  1. cmd1.ExecuteNonQuery();
  2. Convert.ToString(cmd1.ExecuteScalar());

You just need ExecuteScalar to insert and select:

decimal newID = (decimal) cmd1.ExecuteScalar();