Save contents of a list back to a SQL database

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?
LVL 8
ApexCoAsked:
Who is Participating?
 
divinewind80Commented:
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?
0
 
ApexCoAuthor Commented:
I have worked with it but this list thing threw me for a loop.
0
 
ApexCoAuthor Commented:
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)
        Next

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.

Thanks.
0
 
ApexCoAuthor Commented:
Just a quick bump. Can anyone give me some guidance here?

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.