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:

Try

            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
                command.Parameters.Add(entry.Value)

            Next

affectedRows = command.ExecuteNonQuery

            connection.Close()


        Catch ex As Exception

            MsgBox(ex.Message)

        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