Hi!

I'm not sure if this question has been asked before, my search in the forums did not return anything useful.

I have a couple of modules that provide basic insert, update and delete functionality and they work 100%.  In the interim, an additional requirement for auditing was added for the site and after doing some research I decided to use triggers in the SQL database.

This was fairly easy to implement for the inserts and updates seeing as I'm able to easily pass the userid to the stored procedures in the ObjectDataSource_Inserting/Updating methods using the following line of code:

e.InputParameters("UserId") = UserId.ToString

However, when it came to doing the same for the delete method, I ran into some trouble. I have changed the delete stored procedure and all the methods in the various layers to accept UserID as an additional parameter, but when I insert the line of code above into the ObjectDataSource_Deleting method, I get the following error:

"The OrderedDictionary is readonly and cannot be modified."

I Googled the error and most of the "solutions" indicate that you have to add your column to the DataKeyNames property of the Gridview.  I'm not expert in web development, this is why I chose DNN. But I'm not sure that I agree with this solution seeing as UserID is not part of the primary key in my table.

I would appreciate it if somebody can assist me in this regard or at least point me in the right direction.

Regards,
Michelle

Hi Michael

Thanks for your reply.  Your thought trail is one that I followed exactly.  I suppose I should give you more information to explain exactly where I'm getting stuck.

Seeing as the information is being deleted, I added an update statement to my "delete" stored procedure in the database.  Herewith the example code:

ALTER procedure xxxx

 @ModuleId Int,
 @DetailItemId int,
 @UserId int
 
as

--This is a work around for the auditing purposes
--We need to know who the user is that deleted this record.  But because the user is not SQL managed and rather
--managed in the Users table and by the DotNetNuke application, we have to provide the trigger with the user that
--actually deleted the record and therefore it is passed into this procedure as a parameter.

--We have to be careful now, because we need to update the ByUser, field for the trigger to use, but not log an "Update" in the audit table

--If you'll look carefully at the trigger,
--you'll see that we don't log an audit if just the "ByUser" column is updated

BEGIN
 UPDATE Table
 SET CreatedByUser = @UserId
 WHERE ItemId = @DetailItemId
END

BEGIN
 --Now that the user is updated to the person who is deleting the record, we will pick it up in the trigger and log it as such
 DELETE
 FROM Table
 WHERE ItemId = @DetailItemId
END

Where I get stuck, is that in order for me to update the UserID, it has to be passed into the stored procedure from the front end.  I am using the normal "delete" method of the object data source, which does not call any "update" related methods or procedures from the front end or in the other layers because that is how it is set up. 

So in essence, what I actually need, is to know who the person is on the front end that hit the "delete" button.  I only care about who that person is on the front end.  As soon as the delete proc is hit in the database, I run the update statement, then immediately after that the delete, and the trigger code is clever enough to log the user that deleted the information into the audit table.

Based on your suggestion, is there a way that I can call an "update" method from the front end when the object data source is asked to execute the procedure in the delete method?  The only way I can imagine doing this is with code on the front end, but I'm not sure where to start...

Am I making sense?

:-)

Hi Michael

Thank you for your reply and effort in assisting me, I greatly appreciate it.

I'm going to do what you suggest.  I hope that I understand how, seeing as I'm not a very clued up web developer.  I'm very much interested in your comment about wrapping 2 & 3 in a single transaction.

I would like to follow best practice.  I am not interested that you provide me with the code, merely that I understand the solution and do it well.

So would I be doing it correctly by calling the "update" method in vb of the same data source, force the input parameter for userid to be the current logged on user, and as soon as that is done, then the delete method will continue it's work, in which case the trigger will execute and I have my solution.

I'm going to implement what I have said in the above paragraph hoping that it is what I need. Should I be concerned about the fact that I'm making two trips to the host server on every delete? (i.e. calling update and then delete?)  I hope that doesn't interfere.

Thanks again!

Michelle