Technicalities of ExecuteNonQuery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working on creating audit trail functionality for a table in SQL Server that gets its data from my VB.Net program. Everything worked fine while I just had the Insert and Delete triggers. However, upon adding the Update trigger, I noticed that the following code will do something that SQL considers an update before performing the actual insert. So, I get an Update-from and an Update-to line in the table that's full of nulls before the actual Insert takes place

So, the question: Is ExecuteNonQuery causing this? If so, is there a better way to make it only an Insert from this code

Thank

' Create the INSERT command for Sql Server
strInsert = "INSERT INTO tblBaseline (Patient_Name, Test_Date, DOB, MRNum," &
" Height, Weight, TestDesc, Gender, DiskStor," &
" TST, Latency, Efficiency, LimbMove, RDI_TST," &
" RDI_Rem, RDI_Sup, REvtWDesat, REvtWoDesat," &
" LegMove, Spontaneous, AvgO2Wake, AvgO2NRem," &
" ApxMinO2, NumEvtWDesat) VALUES ('
For j = 1 To 2
strInsert = strInsert & strData(j) & "','
Nex
strInsert = strInsert & strData(24) & "')

' Submit INSERT command to the database
BiosSqlCommand.CommandText = strInser
BiosSqlCommand.ExecuteNonQuery()
 
I am wiling to bet ExecuteNonQuery has nothing to do with your problem. I
expect that the same thing would happen if you ran this insert query from
query analyzer. It sounds like you haven't tried that to see what the issue
is, though that seems like the most logical first step.

I would guess it is something in your trigger definition that is doing this,
and if query analyzer causes the same behavior, then that will be confirmed.

I've actually worked on writing an audit trail using triggers myself, and
have not had this issue.

Ken said:
I'm working on creating audit trail functionality for a table in SQL
Click to expand...
Server that gets its data from my VB.Net program. Everything worked fine
while I just had the Insert and Delete triggers. However, upon adding the
Update trigger, I noticed that the following code will do something that SQL
considers an update before performing the actual insert. So, I get an
Update-from and an Update-to line in the table that's full of nulls before
the actual Insert takes place.
So, the question: Is ExecuteNonQuery causing this? If so, is there a
Click to expand...
better way to make it only an Insert from this code?
 
Well, you were kind of right: I had run everything on my test database and the triggers were running fine. Moving it to the real database that is fed by the VB program made it break. Turns out there was another trigger on the real database that was interacting with the audit trail. Sorry to have bothered you.
 
Back
Top