none
SQL dependency

    Question

  • Sir

    I am testing a Vb.net application to have SQL dependency with SQL server database. Infact I copied the sample code given in MSDN and only changed the database connection string.

    I am getting the connection but the dependency_OnChange event gets fired repeatedly as in a loop.

    The args e.Type is "Subscribe"

    and e.info is Invalid.

    I checked the SQL statement for the format but could not find any mistake.

    Private Function GetConnectionString() As String Return "Server=OPS-TMIT\SQLEXPRESS;Database=FlightStatus;Integrated Security=true;" & _ "User ID=;Password=;Trusted_Connection=True;Encrypt=False;" & _ "Connection Timeout=30;Pooling=False;" End Function

    Private Function GetSQL() As String Return "Select [FlightStatus].[dbo].[Data].[Auto], " & _ "[FlightStatus].[dbo].[Data].[Arr Date] " & _ "FROM [FlightStatus].[dbo].[Data] " & _ "WHERE ([FlightStatus].[dbo].[Data].[Arr Date] = @Date1 );" End Function

    Private Sub dependency_OnChange( _ ByVal sender As Object, ByVal e As SqlNotificationEventArgs) Dim someType = e.Type Dim someInfo = e.Info Dim someScource = e.Source ' 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 ' At this point, the code is executing on the ' UI thread, so it is safe to update the UI. changeCount += 1 ' Add information from the event arguments to the list box ' for debugging purposes only. ' Reload the dataset that's bound to the grid. GetData() End Sub Private Sub GetData()

    ' Empty the dataset so that there is only ' one batch worth of data displayed. dtSet.Clear() ' Make sure the command object does not already have ' a notification object associated with it. command.Notification = Nothing ' Create and bind the SqlDependency object ' to the command object. Dim dependency As New SqlDependency(command) AddHandler dependency.OnChange, AddressOf dependency_OnChange Using adapter As New SqlDataAdapter(command) adapter.Fill(dtSet, tableName) Me.DataGridView1.DataSource = dtSet Me.DataGridView1.DataMember = tableName End Using End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Try ' Remove any existing dependency connection, then create a new one. SqlDependency.Stop(GetConnectionString()) SqlDependency.Start(GetConnectionString()) If connection Is Nothing Then connection = New SqlConnection(GetConnectionString()) End If If command Is Nothing Then ' GetSQL is a local procedure that returns ' a paramaterized SQL string. You might want ' to use a stored procedure in your application. command = New SqlCommand(GetSQL(), connection) Dim prm As New SqlParameter("@Date1", SqlDbType.DateTime2) prm.Direction = ParameterDirection.Input prm.DbType = DbType.DateTime2 prm.Value = "2013-11-18 00:00:00" command.Parameters.Add(prm) End If If dtSet Is Nothing Then dtSet = New DataSet() End If GetData() Catch ex As Exception MsgBox(ex.Message) End Try End Sub


    
    

    Please advice where I have gone wrong !!


    Manoj

    Friday, January 03, 2014 12:16 AM

Answers

  • I am getting the connection but the dependency_OnChange event gets fired repeatedly as in a loop.

    The args e.Type is "Subscribe"

    and e.info is Invalid.

    According to http://msdn.microsoft.com/en-us/library/z0fkxc6y(v=vs.110).aspx, "Subscribe" indicates "There was a failure to create a notification subscription. Use the SqlNotificationEventArgs object's SqlNotificationInfo item to determine the cause of the failure."

    I ran your code successfully, changing the connection string, query and adding missing Imports and variables.  So the problem looks to be environmental.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, January 03, 2014 1:41 PM added info that the code works
    • Marked as answer by Manoj Joseph Sunday, January 05, 2014 11:39 PM
    Friday, January 03, 2014 2:35 AM

All replies

  • I am getting the connection but the dependency_OnChange event gets fired repeatedly as in a loop.

    The args e.Type is "Subscribe"

    and e.info is Invalid.

    According to http://msdn.microsoft.com/en-us/library/z0fkxc6y(v=vs.110).aspx, "Subscribe" indicates "There was a failure to create a notification subscription. Use the SqlNotificationEventArgs object's SqlNotificationInfo item to determine the cause of the failure."

    I ran your code successfully, changing the connection string, query and adding missing Imports and variables.  So the problem looks to be environmental.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, January 03, 2014 1:41 PM added info that the code works
    • Marked as answer by Manoj Joseph Sunday, January 05, 2014 11:39 PM
    Friday, January 03, 2014 2:35 AM
  • Hi Manoj,

    I'm getting the same problem as you described. The UI is getting the updated data successfully, but the method dependency_OnChange keeps getting fired as if in infinite loop. Even when I know there is know change on the database table. Can you please help if you got this fixed?

    Dan Guzman's reply didn't help as the link he posted no longer available, probably removed. Also, I couldn't locate the SqlNotificationInfo item that he asked to check.


    Thursday, January 15, 2015 8:19 PM
  • Manisha,

    An extraneous comma got added to the link in my answer.  Try http://msdn.microsoft.com/en-us/library/z0fkxc6y(v=vs.110).aspx

    If the SqlNotificationType is Subscribe (indicating an error), examine SqlNotificationInfo for more details about the cause.  If you need more help, I suggest you start a new thread rather than continuing this answered thread.  That will provide more visibility for your new question.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, January 16, 2015 3:31 PM