Reputation: 2215

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;
                    conn1.Open();

                    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;

                    cmd1.ExecuteNonQuery();

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

But why does it insert twice?

Upvotes: 2

Answers (6)

Ibibo

Reputation: 197

You are executing it twice.

cmd1.ExecuteNonQuery(); // 1st

string numScope = Convert.ToString(cmd1.ExecuteScalar()); // 2nd

Delete one of these.

Upvotes: 0

Tim Schmelter

Reputation: 460058

You are executing it twice

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

You just need ExecuteScalar to insert and select:

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

Upvotes: 14

Guffa

Reputation: 700212

That's because you are executing it twice.

First here:

cmd1.ExecuteNonQuery();

Then here:

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

Remove the first one.

Upvotes: 2

MDiesel

Reputation: 2667

The reason that your query executes twice is because you are calling both ExecuteNonQuery
and ExecuteScalar on your command object.

Upvotes: 2

MikeSmithDev

Reputation: 15797

because you are executing it twice:

   cmd1.ExecuteNonQuery(); //once

   string numScope = Convert.ToString(cmd1.ExecuteScalar()); //twice

Get rid of cmd1.ExecuteNonQuery(); and just use the ExecuteScalar().

Upvotes: 2

Aaron Bertrand

Reputation: 280262

Because you've executed it twice; once as ExecuteNonQuery() and once as ExecuteScalar().

Upvotes: 2

Why does my SQL &#39;INSERT&#39; statement execute twice?

Answers (6)

Related Questions

Why does my SQL 'INSERT' statement execute twice?