Click here to Skip to main content
11,790,816 members (49,501 online)
Rate this: bad
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()
        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
            End If
            tbdataset.EnforceConstraints = True
        End If
        gridVehicles.DataSource = Nothing
    Catch ex As Exception
        '   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())
End If
        If connection.State = ConnectionState.Closed Then
        End If
        If command Is Nothing Then
            command = New SqlCommand(GETSQL(), connection)
        End If
    End If
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)
    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.
End Sub
Posted 5-Mar-13 7:50am
Edited 5-Mar-13 7:51am
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.

1 solution

Rate this: bad
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.

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

  Print Answers RSS
0 OriginalGriff 385
1 ppolymorphe 339
2 Richard MacCutchan 118
3 Maciej Los 70
4 George Swan 70
0 OriginalGriff 1,154
1 Maciej Los 825
2 KrunalRohit 686
3 ppolymorphe 660
4 CPallini 621

Advertise | Privacy | Mobile
Web01 | 2.8.1509028.1 | Last Updated 5 Mar 2013
Copyright © CodeProject, 1999-2015
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