Ken Tucker

DataGridViewCombobox AutoComplete

Here is a quick example on using an autocomplete combobox in the DataGridView.  In this example I load all the possible values for the combobox into a AutoCompleteStringCollection and make that the DataGridViewComboBox's datasource.  In the editingControl showing event you need to set the ComboBox's DropDownStyle, and the auto complete settings.

Imports System.Data.SqlClient

Public Class Form1
    Dim scAutoComplete As New AutoCompleteStringCollection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim da As SqlDataAdapter
        Dim conn As SqlConnection
        Dim ds As New DataSet
        strConn = "Server = .\SQLEXPRESS;Database = NorthWind; Integrated Security = SSPI;"
        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter("Select * from [Orders]", conn)
        da.Fill(ds, "Orders")
        DataGridView1.DataSource = ds.Tables("Orders")

        Dim cmd As New SqlCommand("Select CustomerID From customers", conn)
        Dim dr As SqlDataReader
        conn.Open()
        dr = cmd.ExecuteReader
        Do While dr.Read
            scAutoComplete.Add(dr.GetString(0))
        Loop
        conn.Close()

        Dim dgvcbc As New DataGridViewComboBoxColumn
        With dgvcbc
            .DataPropertyName = "CustomerID"
            .DataSource = scAutoComplete
            .HeaderText = "Customer ID"
        End With
        DataGridView1.Columns.Remove("CustomerID")
        DataGridView1.Columns.Insert(1, dgvcbc)
    End Sub

    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        If DataGridView1.CurrentCell.ColumnIndex = 1 AndAlso TypeOf e.Control Is ComboBox Then
            With DirectCast(e.Control, ComboBox)
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
                .AutoCompleteCustomSource = scAutoComplete
            End With
        End If
    End Sub

End Class

Sync Services Part 2

In the second post of this series we will make it so changes we make to the local data will be sent back to the server.  Lets start by opening the project we created in the previous post.

So lets extend the SyncAgent Partial classes to make the sync 2 way. The class contains a partial method OnInitialized which you can add code to.   In this method we will make the Products table Sync Direction Bidirectional.

Partial Public Class NorthwindSyncAgent

    Private Sub OnInitialized()
        Me.Products.SyncDirection = SyncDirection.Bidirectional
    End Sub
End Class

Now what if there is a conflict? Let create a form to give the user the option of keeping the changes he/she made or to accept the changes on the server.  Add a form named frmConflict to the project.  On the form add 2 buttons (btnClient, and btnServer) and 2 datagridviews (dgvClient, and dgvServer).  Here is what my form looks like

Add the following code to the buttons

Private Sub btnClient_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClient.Click
    Me.DialogResult = Windows.Forms.DialogResult.OK
    Me.Close()
End Sub

Private Sub btnServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnServer.Click
    Me.DialogResult = Windows.Forms.DialogResult.Cancel
    Me.Close()
End Sub

Now we need to extend the ServerSyncProvider to raise an event when there is a conflict. If the user presses the Keep my changes button we force the changes to be written to the database otherwise we allow the change to be made

Partial Public Class NorthwindServerSyncProvider

    Private Sub OnInitialized()
        AddHandler ApplyChangeFailed, AddressOf ApplyChangesFailed
    End Sub

    Public Sub ApplyChangesFailed(ByVal sender As Object, ByVal e As ApplyChangeFailedEventArgs)
        Dim frm As New frmConflict
        frm.dgvClient.DataSource = e.Conflict.ClientChange
        frm.dgvServer.DataSource = e.Conflict.ServerChange
        frm.ShowDialog()
        If frm.DialogResult = DialogResult.OK Then
            e.Action = ApplyAction.RetryWithForceWrite
        Else
            e.Action = ApplyAction.Continue
        End If
    End Sub

End Class

Sync Services Part 1

In this post we will create a local cache of the Northwind database.  To start with lets create a new visual basic windows forms project in Visual Studio 2008.  From the project menu select add a new item and select a new local database cache and name it northwind.

In the server connect select a connection to the northwind database.

Press the add button and select the product table.  Press OK to close the dialog.  Go ahead and create a table adapter for the product table.  The drag the products table on to the form from the data source window.  Add a button to the binding navigator and set its text to Sync and change the display style to Text.

In the button you added to the toolbar add this code

Run the app and Open up the Sql Server Management Studio Express.  Make some changes in the Northwind database's Product table and Press the sync button.  

Notice the changes you made to the Products table show up in the datagridview.  The changes are also saved in the local sqlce database.