View Single Post
  #1 (permalink)  
Old June 10th, 2008, 05:39 PM
kevorkian kevorkian is offline
Authorized User
Join Date: Sep 2004
Location: , , .
Posts: 62
Thanks: 0
Thanked 1 Time in 1 Post
Default 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.

Reply With Quote