?At a loss? ExecuteNonQuery executing my Insert Statement 3 times in debug mode

M

Mike

I know this sounds strange but I am at a loss. I am calling a simple
funtion that opens a connection to a SQL Server 2000 database and executes
an Insert Statement.

private void AddMinimunWageStipen(string payrollid,double amount)
{

System.Data.SqlClient.SqlConnection cn = null;
System.Data.SqlClient.SqlCommand cm = null;

string sql = "INSERT INTO tblStipen (EmployeeID, StipenTypeID, Units,
Amount, AddHours, PayrollID) ";
sql+= "VALUES (" + _employeeid + ", 3, 1, " + amount + ", 0, " + payrollid
+ ")";

try
{
cn = new
System.Data.SqlClient.SqlConnection(PayrollSystem.Data.SQLSERVER);
cn.Open();
cm = new System.Data.SqlClient.SqlCommand(sql,cn);
int flag = cm.ExecuteNonQuery();
cn.Close();
}
catch(Exception ex)
{
throw ex;
}
finally
{
cm.Dispose();
cn.Dispose();
}

}

When I run this code in the debugger and step through this funtion, when I
execute the line "int flag = cm.ExecuteNonQuery();" the Insert Statement
gets exectued 3 times. So I have the same record in the database 3 times??
And I know the the code is only being exectuted once. The function isn't
being called 3 times with the same parameters being passed in. I am also
not doing any special threading.

If I run the program without stopping in this function the code behave like
it should and the record only gets inserted once.

Is there something I am missing? Has anyone else ran into this problem?

Thanks in advance,

Mike R
 
S

Scott M.

I have found similar behavior when stepping through code that deals with a
datareader's read method. What I found was that anytime the debugger was
asked to give me ANY information about the data (i.e. watch, autos and local
windows of the debugger) it would cause the datareader to invoke its read
method because it had to try to get some data for me to look at while
debugging.

It sounds like the ExecuteNonQuery method of the command object acts the
same way in the debugger. This behavior is troubling because it basically
means that you can't debug (with any degree of certainty) certain
statements.
 
W

William Ryan eMVP

Mike:

How do you know it's being executed 3 times? If this isn't in a loop and
there's not multiple threads running it, I would be the house that it's not
really executing three times.

As an aside, I'd really lose the dynamic sql and either use Parameters or
ideally make it a proc...

sql = "INSERT INTO tblStipen(EmployeeId, StipenTypeID, Units, Amount,
AddHours, PayrollID) Values
@EmployeeId, @StipType, @Units, @Amount, @Hours, @PayrollID)";

cmd.Parameters.Clear();
cmd.Parameters.Add("@EmployeeId", SqlDbType.Employee'sType ie Int,
Length).Value = _employeeid;
cmd.Parameters.Add("@StipType", SqlDbType.SmallInt).Value = 3;

etc.

I'm guessing when you say it's gettting executed 3 times it's based on the
return value being 3? First verify that it's not actually being inserted 3
times by querying the DB. Since it's a simple insert, I can't see how 3
records could be getting inserted, but you may have a trigger or something
that's causing this number to behave funny.

First let me know if it's the number 3 as a return value that's the issue or
if there are verifiably 3 records getting inserted.

HTH,

Bill
 
M

Mike

Well the way I can tell that the Insert statement is being executed 3 times
is because there are 3 records in the database.

1 is being returned by the ExecuteNonQuery command. No there aren't any
triggers running.


Mike
 
S

Sushil Chordia

Mike,
AFAIK, i have never seen this happen.
Is it possible that you have the expression "cm.ExecuteNonQuery();" in your
watch list? This might cause the expression to be called and there by
inserting rows.
 
P

Philip Rieck

I've seen similar with other sqlclient classes when using the debugger. My
theory is that the debugger queries the object for information and causes it
to execute multiple times.

Try closing all watch windows, the locals window, and the autos window. I'd
even close the "this" window. Then step through and see what happens.
I've done this and eliminated the problem -- but it kind of defeats the
purpose of running in the debugger.
 
S

Scott M.

This is what I suspect is the problem.


Sushil Chordia said:
Mike,
AFAIK, i have never seen this happen.
Is it possible that you have the expression "cm.ExecuteNonQuery();" in your
watch list? This might cause the expression to be called and there by
inserting rows.
Click to expand...
 
W

William Ryan eMVP

If 1 is being returned as the records affected, I'd be the house that the
insert isn't executing 3 times...I think the watch list issue has already
been brought up, but if all else fails, put a PK on the table temporarily
and see when you get a violation. you may also want to change where you
call this from and see if it happens repeatedly even if you call it from
another proc.
 
S

Scott M.

It's got to be executing 3 times because the OP says that when he checks the
db, there are 3 records inserted.
 
M

Mike

I can very clearly see 3 records in the database. And the table does have a
primary key and and it set as an Identity field. Each record is given a
unique ID.

Ive been just stepping over the methods where I am doing the inserts and it
hasn't happen since. The debugger must be executing the code several times
to get the value the ExecuteNonQuery is returning to display in the various
debug windows I have open. Seems like a giant bug that is incredibly
annoying.

Everything has been working great since I stopped stepping through the
method.
 
Top