Save contents of a list back to a SQL database

Posted on 2007-10-19
Last Modified: 2009-12-16
I'm trying to modify a project that I found on the web.

It basically consists of adding items from a SQL DB to a list and then allowing them to be reordered with the AJAX ReOrderList tool from Microsoft. It currently saves the changes to the list order to the session, but I need to figure out how to save it back to the DB for the appropriate changes.

Here is the code that creates the list:
 'Returns the WorkItems - from database
    Public Shared Function GetWorkItems() As List(Of WorkItem)
        If HttpContext.Current.Session("WorkItems") Is Nothing Then
            Dim table As DataTable = New ReOrderTableAdapterTableAdapters.QuestionTableAdapter().GetItems()
            Dim workItems As List(Of WorkItem) = New List(Of WorkItem)()
            For Each row As DataRow In Table.Rows
                workItems.Add(New WorkItem(Integer.Parse(row("QID").ToString()), Integer.Parse(row("QOrder").ToString()), row("QDesc").ToString()))
            Next row
            Return workItems
        End If
        Return CType(HttpContext.Current.Session("WorkItems"), List(Of WorkItem))
    End Function

And right now this is the code that saves it to the session state:
    Public Shared Sub SaveWorkItems(ByVal workItems As List(Of WorkItem))
        HttpContext.Current.Session("WorkItems") = workItems
    End Sub

Can anyone assist me with modifying the SaveWorkItems portion so it will loop through each item in the list and write back to a SQL table?
Question by:ApexCo
    LVL 9

    Accepted Solution

    One way is to alter the For Each section with an Insert SQL command.  That way, you would add the records, one by one, to the database.  Have you worked with SQL in ASP.NET before?
    LVL 8

    Author Comment

    I have worked with it but this list thing threw me for a loop.
    LVL 8

    Author Comment

    Well I've gotten a little further and now I'm stuck again.

    I applied this to my save section:
            Dim tableadapter As New ReOrderTableAdapterTableAdapters.QuestionTableAdapter
            For Each workitem As WorkItem In workItems
                tableadapter.UpdateQuery(workitem.QOrder, workitem.QID)

    But the problem is that it saves it in the exact same order because the data is coming from the current DB, not the new order of the "workitems" list. I looked through the properties I can use with the workitems list but cannot figure out a way to get the current index of the list item to work with my update.

    There is an "items" property, but it is giving me syntax errors no matter what I try.

    LVL 8

    Author Comment

    Just a quick bump. Can anyone give me some guidance here?


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now