none
IQueryable is not updated RRS feed

  • Question

  • Hi,

    In a form there are textboxes, combo boxes or date time pickers, every one representing one of the text fields, foreign keys or date fields in a table [Result] in a database.

    With this form the user can select certain records from the whole record set. For every text box the user can also indicate by selecting from a combo box whether the fields starts with, is equal to or contains the string he/she filled in the text box

    When the user want the selection be made, he/she click a button and the following code is executed:

    Dim db As New DataClassesDataContext
    db.Connection.ConnectionString = "Data Source=" & MyNameSpace.Server & ";Initial Catalog=" & MyNameSpace.Database & ";Integrated Security=True"

    Dim allrecords = (From r In db.Results Select r)
    For Each rec In allrecords
        rec.Selected = True
    Next
    db.SubmitChanges()

    After that all the fields are processed in this procedure (has also overloads for combo boxes and date time pickers):

    Private Sub SelectBy(ByRef Txt As TextBox, ByRef Cbo As ComboBox)
        Dim db As New DataClassesDataContext
        db.Connection.ConnectionString = "Data Source=" & MyNameSpace.Server & ";Initial Catalog=" & MyNameSpace.Database & ";Integrated Security=True"

        Dim sValue As String = Txt.Text.Trim
        Dim spec As SelectionSpecification = CType(CType(CboSpecificatie.SelectedItem, ComboBoxListItem).Key, SelectionSpecification)

        Dim deselected As IQueryable(Of Result)
        Select Case Cbo.Name
            Case "CboSpecContactName"
                Select Case spec
                    Case SelectionSpecification.IsEqualTo
                        deselected = (From r In db.Results Where r.Selected And Not r.ContactName.Equals(sValue) Select r)
                    Case SelectionSpecification.IsStartingWith
                        deselected = (From r In db.Results r.Selected And Not r.ContactName.StartsWith(sValue) Select r)
                    Case SelectionSpecification.IsContaining
                        deselected = (From r In db.Results r.Selected And Not r.ContactName.Contains(sValue) Select r)
               
    End Select
    'etc etc etc ... for every text box

        For Each des As Result In deselected
            des.Selected = False
        Next
        db.SubmitChanges()

    Finally the selected records are retrieved with:

        Dim selected = (From r In db.Results Where r.Selected Select r)

    Strangly enough, the field Selected has not been updated.

    Everytime a record of the IQueryable (Of Result) deselected is updated, the value changes but when the next is updated the previous update is reversed.

    I tried it also like this:

    For Each des As Result In deselected
        des.Selected = False
        db.SubmitChanges()
    Next

    But that doesn’t work either.

    Anybody an idea what goes wrong? It looks as if the IQueryable (Of Result) is read Only, although I don’t get any error message in that way (or any other way for that matter).

     

    additional information

    Definition of SelectionSpecification

    Public Enum SelectionSpecification
    IsEqualTo = 0
    IsStartingWith= 1
    IsContaining = 2
    End Enum

    Class ComboBoxListItem has two properties:

    Public Property Key() As Object
    Public
    Property Value() As String

    RuWel





    • Edited by RuWel Tuesday, October 4, 2011 1:00 PM
    Tuesday, October 4, 2011 12:44 PM

Answers

  • I just did a small test and the problem you are having (you should see that the GetChangeSet has zero as it's counts) is that the table in question has no primary key.

    I created a table with no primary key and updated all the rows as you did.  The update count was zero.

    Then I added a PK to the table and recreated the linq to sql class and using the same code now the update count was the count of rows.

     

    Hope this helps

    LS


    Lloyd Sheen
    • Marked as answer by RuWel Friday, October 7, 2011 2:01 PM
    Wednesday, October 5, 2011 4:32 PM

All replies

  • Can you try the following line of code prior to your SubmitChanges:

    dim changeCount as integer = db.GetChangeSet.Updates.Count

    This should show you how many rows are to be updated.  


    Lloyd Sheen
    Wednesday, October 5, 2011 3:18 PM
  • I just did a small test and the problem you are having (you should see that the GetChangeSet has zero as it's counts) is that the table in question has no primary key.

    I created a table with no primary key and updated all the rows as you did.  The update count was zero.

    Then I added a PK to the table and recreated the linq to sql class and using the same code now the update count was the count of rows.

     

    Hope this helps

    LS


    Lloyd Sheen
    • Marked as answer by RuWel Friday, October 7, 2011 2:01 PM
    Wednesday, October 5, 2011 4:32 PM
  • Hi Lloyd,

     

    thanks for your reply.

    This absolutely correct. I added a PK and now it works.

    RuWel

    Friday, October 7, 2011 2:00 PM