Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL VB.NET Dependency
I have two related tables (tbVehicles and tbVehiclesDoc) I am running the below sql dependency but it is firring many time and my program freezes. please assist.
 
I want the program to update these tables when another user modifies them.
my Code
Public Sub getdata()
    Try
        If tbdataset.Tables.Contains("tbVehicles") Then
 
            If tbdataset.Tables("tbVehiclesDoc") IsNot Nothing Then
                For f As Integer = tbdataset.Tables("tbVehiclesDoc").ChildRelations.Count - 1 To 0 Step -1
                    tbdataset.Tables("tbVehiclesDoc").ChildRelations(f).ChildTable.Constraints.Remove(tbdataset.Tables("tbVehiclesDoc").ChildRelations(f).RelationName)
                    tbdataset.Tables("tbVehiclesDoc").ChildRelations.RemoveAt(f)
                Next
                tbdataset.Tables("tbVehiclesDoc").ChildRelations.Clear()
                tbdataset.Tables("tbVehiclesDoc").ParentRelations.Clear()
                tbdataset.Tables("tbVehiclesDoc").Constraints.Clear()
                tbdataset.Tables.Remove("tbVehiclesDoc")
            End If
            tbdataset.Tables.Remove("tbVehicles")
            tbdataset.EnforceConstraints = True
        End If
        gridVehicles.DataSource = Nothing
    Catch ex As Exception
        MsgBox(ex.ToString)
        '   Exit Sub
    End Try
    command.Notification = Nothing
    Dim dependency As New SqlDependency(command)
    AddHandler dependency.OnChange, AddressOf dependency_OnChange
 
    Dim adapter As New SqlDataAdapter(command)
    adapter.Fill(tbdataset, "tbVehicles")
    '----
End Sub
 
Private Sub toupdate()
    If CanRequestNotifications() Then
        If connection Is Nothing Then
            connection = New SqlConnection(GetConnectionString())
            connection.Open()
End If
        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If
        If command Is Nothing Then
            command = New SqlCommand(GETSQL(), connection)
        End If
 
    End If
    getdata()
End Sub
 
Private Function GETSQL() As String
    Return "Select Rtrim(VehcID) as VehcID,RTrim(VehcModel), Rtrim(VehcRegNo) as VehcRegNo, Rtrim(VehcFourWheel), Rtrim(VehcCondition), Rtrim(VehcLastKM),Rtrim(VehcBranch), Rtrim(VehcDepartment), Rtrim(VehcDriver), Rtrim(VehcRemarks), VehcPic from dbo.tbVehicles"
End Function
 
Private Sub dependency_OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)
 
    ' This event will occur on a thread pool thread.
    ' It is illegal to update the UI from a worker thread
    ' The following code checks to see if it is safe
    ' update the UI.
    Dim i As ISynchronizeInvoke = CType(Me, ISynchronizeInvoke)
 
    ' If InvokeRequired returns True, the code
    ' is executing on a worker thread.
    If i.InvokeRequired Then
        ' Create a delegate to perform the thread switch
        Dim tempDelegate As New OnChangeEventHandler( _
            AddressOf dependency_OnChange)
 
        Dim args() As Object = {sender, e}
 
        ' Marshal the data from the worker thread
        ' to the UI thread.
        i.BeginInvoke(tempDelegate, args)
 
        Return
    End If
 
    ' Remove the handler since it's only good
    ' for a single notification
    Dim dependency As SqlDependency = _
        CType(sender, SqlDependency)
 
    RemoveHandler dependency.OnChange, _
       AddressOf dependency_OnChange
 
    ' Reload the dataset that's bound to the grid.
    getdata()
End Sub
Posted 5-Mar-13 8:50am
Edited 5-Mar-13 8:51am
v2
Comments
Sergey Alexandrovich Kryukov at 5-Mar-13 13:53pm
   
This question makes no sense. To address this problem you need to see the methods calling your sub "getData", not this sub itself.
Not enough information. And use "pre" tag to format your code.
—SA

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You are creating an infinite loop, in your get data you use the event dependancy change and then change the data, when you change the data dependancy change event fires and you start again.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,803
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,421
3 Manas Bhardwaj 4,841
4 Maciej Los 4,330


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 5 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100