Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 10th, 2008, 05:39 PM
Authorized User
Join Date: Sep 2004
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.

Old June 10th, 2008, 06:35 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi

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 June 10th, 2008, 07:24 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

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.
Old June 11th, 2008, 08:54 AM
Authorized User
Join Date: Sep 2004
Posts: 62
Thanks: 0
Thanked 1 Time in 1 Post

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")?

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to pass array to oracle stored procedure? zhao2007 Hibernate 1 March 21st, 2008 03:51 PM
Passing Array to Oracle Stored Procedure kulkarnimonica Oracle ASP 1 August 6th, 2007 02:22 AM
Pass an Array as a Parameter to a Stored Procedure booksnore2 Oracle 0 August 31st, 2004 09:20 AM
Sending Dates to Stored Procedure pete_m ASP.NET 1.0 and 1.1 Basics 1 June 30th, 2004 06:00 AM
Sending an e-mail from a stored procedure cooldude87801 Oracle 0 August 5th, 2003 04:35 PM

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