Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 10th, 2008, 05:39 PM
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
  #2 (permalink)  
Old June 10th, 2008, 06:35 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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?

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Reply With Quote
  #3 (permalink)  
Old June 10th, 2008, 07:24 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old June 11th, 2008, 08:54 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 62
Thanks: 0
Thanked 1 Time in 1 Post
Default

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")?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:33 AM.


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