creating an audit

[acorn]

Hi.

i need to write code that will:
1. check my dataset to see if anything has changed.
2. if there are changes, write the old value, new value to a table in oracle.

I only need to know about rows that have changed. I know somewhere I can check something called rowstate but I don't know how do that.

So far I have the following code:

Private Sub WTBBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WTBBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.WTBBindingSource.EndEdit()
        'audit. 
        Dim dschanges As DataSet
        dschanges = Me.BQDS.GetChanges()

I can see that dschanges haschanges is true...
can you point me in the right direction?

thanks.

 

[cjard]

ps, did you really make columns called InterviewDate that you now have to "quote" so oracle can find them case sensitively? Bummer.

You need to modify that code a little:

SELECT REPLACE('
  IF :old.{cn} IS NULL or :new.{cn} IS NULL or :old.{cn} != :new.{cn} THEN
    INSERT INTO audit(...blah...) VALUES (...blah..., :old.{cn}, :new.{cn});
  END IF;
'
, '{cn}', '"'||column_name||'"' )
from
  user_tab_cols
where table_name = 'WHATEVER TABLE NAME' and nullable = 'Y'

And.. uh.. try not to use case sensitive column names in future.. Too much of a headache