Refresh Datagrid view When SQL Database Update?

See more:

VB

Hi dear;

I have Code that will update the Sql Database Change when Datagridview will automatically refresh

This code only works on my computer and when they need it or want a computer to connect to the server I'm connected to my computer and I do this operation gives the following message:

"Cannot create an event subscription. failed"

One solution I have this problem?

this my code:

First you import Dll From path "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies":

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

2. Run Triger by SQl Server Studio:

USE [master]

GO

ALTER DATABASE [Databasename] SET  ENABLE_BROKER WITH NO_WAIT

GO

USE [Databasename]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[Tablename_EventSender]

ON [dbo].[Tablename] 

FOR INSERT,UPDATE,DELETE AS

BEGIN

BEGIN TRANSACTION

CREATE TABLE [dbo].[Tablename_Tmp_For_Event](

 [ID] [bit] NULL

) ON [PRIMARY]

DROP TABLE [dbo].[Tablename_Tmp_For_Event]

COMMIT

END

GO

3. open Viual Studio and Create project and Create A class then Copy below code to Class:

Imports Microsoft.SqlServer.Management.Smo

Public Class DatabaseWatcher
    Private Shared WatcherServer As Server
    Private Shared db As Database
    Public Shared Event TableChanged As EventHandler(Of EventArgs)
    Private Shared Sub Table_Changed(ByVal sender As Object, ByVal e As ServerEventArgs)
        For Each prpty In e.Properties
            'If prpty.Value = "TableName_Tmp_For_Event" Then
            If prpty.Value.ToString = "TableName_Tmp_For_Event" Then
                'Do Here!
                ' MsgBox("ِYour Table Data Change!")
                RaiseEvent TableChanged(prpty.Value, New EventArgs)
            End If
        Next
    End Sub

    Public Shared Sub RegisterEvent()
        Try
            WatcherServer = New Server("Servername")
            WatcherServer.ConnectionContext.LoginSecure = False
            WatcherServer.ConnectionContext.Login = "SQL Username"
            WatcherServer.ConnectionContext.Password = "SQL Password"

            WatcherServer.ConnectionContext.AutoDisconnectMode = False
            db = WatcherServer.Databases("Databasename")
            Dim databaseCreateEventSet As New DatabaseEventSet
            databaseCreateEventSet.DropTable = True
            Dim serverCreateEventHandler As ServerEventHandler
            serverCreateEventHandler = New ServerEventHandler(AddressOf Table_Changed)
            db.Events.SubscribeToEvents(databaseCreateEventSet, serverCreateEventHandler)
            db.Events.StartEvents()
        Catch ex As Exception
            MsgBox("SMO ERROR : " & vbNewLine & ex.Message)
            'My.Computer.Clipboard.SetText(Err.Description)
        End Try

    End Sub

4. Create Form And Copy Below code!

Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        DoAsycEventraise()
        RefreshGrid()
    End Sub
   
    Private Sub DoAsycEventraise()
        Try
            CheckForIllegalCrossThreadCalls = False
            AddHandler DatabaseWatcher.TableChanged, AddressOf SQLEvent_Handler
            Dim t As New Threading.Thread(AddressOf DatabaseWatcher.RegisterEvent)
            t.SetApartmentState(Threading.ApartmentState.MTA)
            t.Start()
        Catch ex As Exception
        End Try
    End Sub
    Private Sub SQLEvent_Handler(sender As System.Object, e As System.EventArgs)
        RefreshGrid()
    End Sub
    Private Sub RefreshGrid()
        Try
            Application.DoEvents()
            Dim TSQL As String = "Select * From Tablename"
            Using Conn As New SqlConnection("Data Source=Servername;Initial Catalog=Databasename;Integrated Security=True;User ID=SQL Username;password=SQl Password")
                Dim adp As New SqlDataAdapter(TSQL, Conn)
                Dim res As New DataTable
                adp.Fill(res)
                adp.Dispose()
                Conn.Dispose()
                If res IsNot Nothing Then Me.DataGridView1.DataSource = res
            End Using
        Catch ex As Exception
        End Try
    End Sub

Please help me about this Application!