none
Isingleresult has data from a cash od the first SQL call rather than querying SQL again -- how to fix? RRS feed

  • Question

  • TargetRoutersList = dc.qry_RouterBaseCopy_TargetRouters(JobID).ToList

    The above line ALWAYS returns the exact same information -- even when the JobID value changes.  data context function is a reference to a stored procedure whose results in SQL manager are as expected  where the call in VB 2008 are set by the very first call and never changed.  WHY? What can I do to make the linQ to SQL result look like the results I expect from SQL Manager with the same select query?

    I am beginning to distrust Linq and will not use it again nor recommend that anybody in their right mind user it unless this can be resolved in a reasionable manner.  There is No warning of this behavior in any of the literature i have encountered so far.  This is A BIG PROBLEM for CRUD editors.

     

    Wednesday, July 14, 2010 1:01 AM

Answers

  • I think it's unlikely it's going to be a corruption issue.  I put a link in my message that will let you turn on things behind the scenes so you can see LINQ to SQL executing the stored procedure and what parameters it is using.

    With that we can see either if it's something going into SQL that's the problem or the handling of what's returned.

    [)amien

    Wednesday, July 14, 2010 3:29 PM
    Moderator

All replies

  • I haven't seen such behavior - are you absolutely sure that is what is happening?

    I would try turning on the Logging to see what SQL commands are being emitted and post the results here.

    [)amien

    Wednesday, July 14, 2010 5:43 AM
    Moderator
  •  

    Private TargetRoutersList As List(Of TargetRouters)

    ....

     

    Private Sub dgTargetJobs_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgTargetJobs.DoubleClick

    LoadTargetRouters()

     

    End Sub

     

    Private Sub LoadTargetRouters()

     

    ' start copying data to the target router table

     

    Me.AcceptButton = Nothing

     

    If dgTargetJobs.SelectedRows.Count = 0 Then Return

     

    If dgTargetJobs.SelectedRows.Count > 0 Then

     

    Dim dgr As DataGridViewRow = dgTargetJobs.SelectedRows(0)

     

    Dim JobID As Integer

     

    JobID = dgTargetJobs.CurrentRow.Cells(dgTargetJobsCells.JobID).Value

    ' get the current Job Number

    dgSelectedRouters.DataSource =

    Nothing

    TargetRoutersList =

    Nothing   ' I confirmed this is dset to nothing as expected

     

    'Do While TargetRoutersList.Count > 0

     

    ' Application.DoEvents()

     

    'Loop

    TargetRoutersList = dc.qry_RouterBaseCopy_TargetRouters(JobID).ToList

    'load the routers for the job  -- this command does not retrieve anything except the result set of the initial call no matter how many times this statement is called

    The application deletes rows and then inserts rows to replace those deleted, then re-runs the above code to retrieve the result.

    I thought at first this was a timing problem -- but not true.  SQL Manager running the same stored procedure returns the correct data.

    Here is a sample of what it did:

    Here is a sampling the last 5 values returned by the query within the VB 2008 application  and SQL Manager:

    VB APP                   SQL MGR               VB App after form reload

    Row RouterID           Row RouterID         Row  RouterID

    35   28138                35    28151             35   28151

    36   28139                36    28152             36   28152

    37   28140                37    28153             37   28153

    38   28155                38    28154             38   28154

                                  39    28155             38   28155

     

    The result during the edit should have added an extra row.  ONLY the extra row at the end of the result set is returned by the calling the query from the DataClass1 object after the edit was performed.  Is there some setting on a dataclass object that produuces this odd behavior?

    I tried to fix the problem by removing the select query and reinstalling it. Same result.  I noticed I had 2 bad data sources and removed all references to them. Same result.  I force the local table empty by setting the table to nothing and then filling it, yet the query result is always wrong after the edit.   I am out of ideas about how fix the dataClass object.  It really looks buggy to me at the Microsoft end.

    Could the entire DataClass be corrupt and need rebuilding?  In this case it is ather large.....

     

    I will need your help to learn how to monitor the SQL server in real time so i can pick up the query's execution request.

     

     

     

     

     

     

     

    Wednesday, July 14, 2010 3:20 PM
  • I think it's unlikely it's going to be a corruption issue.  I put a link in my message that will let you turn on things behind the scenes so you can see LINQ to SQL executing the stored procedure and what parameters it is using.

    With that we can see either if it's something going into SQL that's the problem or the handling of what's returned.

    [)amien

    Wednesday, July 14, 2010 3:29 PM
    Moderator