Save contents of a list back to a SQL database

Posted on 2007-10-19
Medium Priority
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
  • 3

Accepted Solution

divinewind80 earned 2000 total points
ID: 20110542
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?

Author Comment

ID: 20112010
I have worked with it but this list thing threw me for a loop.

Author Comment

ID: 20123783
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.


Author Comment

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


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

831 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