p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 2.0 Basics (http://p2p.wrox.com/forumdisplay.php?f=136)
-   -   Sending Array to Stored Procedure in ItemUpdating (http://p2p.wrox.com/showthread.php?t=68727)

kevorkian June 10th, 2008 05:39 PM

Sending Array to Stored Procedure in ItemUpdating
Ok, I am new to this and am not sure if it will even work like I want it to, but here it goes. I have two tables - one which displays a row in a DetailsView on my web form, and the second to store a snapshot of the old record if it gets edited and updated. I assumed I could just write the old values to the snapshot table in the ItemUpdating() sub - but I am getting an error. Here is what my code looks like:


            Dim str_SQL As String = "sp_Update_LOG"
            Dim affectedRows As Integer
            Dim OldValuesArray As New ArrayList()
            Dim entry As DictionaryEntry

            Dim connection As SqlConnection = ConnectionManager.WebAdvConnection()
            Dim command As SqlCommand = New SqlCommand(str_SQL, connection)
            command.CommandType = Data.CommandType.StoredProcedure

            For Each entry In e.OldValues
'Here I am trying to loop through all of the old values and add them to the stored procedure parameters.
The first example I commented out because I am unsure of how to specify the data type when I loop through it.
the second is just sending the value - or so I thought.

                'command.Parameters.Add(entry.Key, Data.SqlDbType.Int).Value = entry.Value


affectedRows = command.ExecuteNonQuery


        Catch ex As Exception


        End Try

'The error I am getting is:
The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Any ideas on how to make this work, or even a better way of saving the old records to a different table? (And please - no SQL Triggers ;)

Appreciate it.

gbianchi June 10th, 2008 06:35 PM

Hi there..

Select into is not a solution??

what is e???? (from e.oldvalues)

And if you are using an SP, why you pass several parameters, how many parameters will it receive?



================================================== =========
Read this if you want to know how to get a correct reply for your question:
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
================================================== =========
I know that CVS was evil, and now i got the proof:
================================================== =========

Old Pedant June 10th, 2008 07:24 PM

Why not do it all in SQL???

GBianchi suggested using SELECT INTO, but of course that doesn't work because you can only use it when the "INTO" table does not yet exist. Not so good for a historical log. <grin/>

If you simply record the Primary Key of the record you are displaying, then part of the process of updating the main table would be to just do
    INSERT INTO myHistoryTable
    SELECT * FROM myMainTable WHERE primaryKey = @pk

The only "trick" here is that the history table has to *NOT* declare any of its fields to be primary keys or foreign keys or IDENTITY, but otherwise they have the same types as the fields in the main table.

And a good "trick", too, is to add one more field to the history table:
     WhenAddedToHistory DATETIME DEFAULT getDate()

So now you automatically have a complete audit trail.

If you use a SP to update the main table, you can just put this INSERT INTO as the first action in the SP. Presto. You are done.

kevorkian June 11th, 2008 08:54 AM

Ok, that makes more sense - but I am still unclear on how to reference the old values inside this ItemUpdating event. I was probably not clear about that before, but I was trying to make all of this happen when a detailsview has been edited, the sub is:

Protected Sub DataTable_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventAr gs) Handles DataTable.ItemUpdating

How do I get the key of the record that is about to be updated in this subroutine to pass to the stored procedure? Is it simply, e.Keys("KEY_FIELD")?

All times are GMT -4. The time now is 02:14 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.