Ken Tucker

How to Dynamically add bound controls to a Data repeater

In the datarepeater's ItemCloning event you have the option to add bound controls to the datarepeater item.  For this example I have a datarepeater named datarepeater1.  I am using Linq to Sql to get data from the northwind database employee table

Public Class Form1

    Dim bs As New BindingSource

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim db As New NorthwindDataContext

        Dim q = From emp In db.Employees Select emp

        bs.DataSource = q

        DataRepeater1.DataSource = bs

    End Sub

    Private Sub DataRepeater1_ItemCloning(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemCloneEventArgs) Handles DataRepeater1.ItemCloning

        Dim lbl As New Label

        Dim b As New Binding("Text", bs, "LastName")

        lbl.DataBindings.Add(b)

        e.Source.Controls.Add(lbl)

    End Sub

End Class

Validating Data entered in a DataRepeater control

In this example I will show how to validate the data entered into a datarepeater control. For this example I am added the northwind SQL compact edition database to the project and created a typed dataset for the products table.  So from the toolbox drop a datarepeater on the form.  Inside the datarepeater drag the ProductName, UnitPrice, and Units in stock fields. Your form should look something like

Now in the drawitem event for the datarepeater we can add a handler to validating event.

Private Sub DataRepeater1_DrawItem(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles DataRepeater1.DrawItem
    Dim currItem As DataRowView = DirectCast(ProductsBindingSource.Item(e.DataRepeaterItem.ItemIndex), DataRowView)
    Dim txt As TextBox = DirectCast(e.DataRepeaterItem.Controls("Unit_PriceTextBox"), TextBox)
    AddHandler txt.Validating, AddressOf TextBox_Validating
End Sub

Private Sub TextBox_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs)
    Dim dec As Decimal
    If Not Decimal.TryParse(DirectCast(sender, TextBox).Text, dec) Then
        MessageBox.Show("Please enter a valid number")
        e.Cancel = True
    End If
End Sub

The complete code

Public Class Form1

    Private Sub ProductsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ProductsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.NorthwindDataSet)

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'NorthwindDataSet.Products' table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)

    End Sub

    Private Sub DataRepeater1_DrawItem(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles DataRepeater1.DrawItem
        Dim currItem As DataRowView = DirectCast(ProductsBindingSource.Item(e.DataRepeaterItem.ItemIndex), DataRowView)
        Dim txt As TextBox = DirectCast(e.DataRepeaterItem.Controls("Unit_PriceTextBox"), TextBox)
        AddHandler txt.Validating, AddressOf TextBox_Validating
    End Sub

    Private Sub TextBox_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs)
        Dim dec As Decimal
        If Not Decimal.TryParse(DirectCast(sender, TextBox).Text, dec) Then
            MessageBox.Show("Please enter a valid number")
            e.Cancel = True
        End If
    End Sub
End Class

Print to PDF

The .Net framework provides a print document class for printing.  There are times that it would be nice to redirect what you are printing to a pdf.   In this example we are going to use the Sharp Pdf lib version 1.3.1 to print to a pdf. 

The sharp pdf lib allows you to add an image to a page in a pdf.  To make it possible to print to a pdf we are going to create a new print controller class which creates a bitmap and has the print document draw the page on the bitmap.  Then it adds the bitmap as a pdf page.  Once the document is done printing it saves the pdf to disk. 

http://sharppdf.sourceforge.net/

Update this project is now available on CodePlex

http://www.codeplex.com/Print2Pdf

Imports sharpPDF

Public Class PdfPrintController
    Inherits Printing.PrintController
    Dim pdf As pdfDocument
    Dim bm As Image

    Private _Author As String = "Unknown"

    Public Property Author() As String
        Get
            Return _Author
        End Get
        Set(ByVal value As String)
            _Author = value
        End Set
    End Property

    Private _FileName As String = "Printed.pdf"
    Public Property FileName() As String
        Get
            Return _FileName
        End Get
        Set(ByVal value As String)
            _FileName = value
        End Set
    End Property

    Private _Title As String = "Unknown"
    Public Property Title() As String
        Get
            Return _Title
        End Get
        Set(ByVal value As String)
            _Title = value
        End Set
    End Property

    Public Overrides ReadOnly Property IsPreview() As Boolean
        Get
            Return True
        End Get
    End Property

    Public Overrides Function OnStartPage(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintPageEventArgs) As System.Drawing.Graphics
        bm = New Bitmap(e.PageBounds.Width, e.PageBounds.Height)
        Dim g As Graphics = Graphics.FromImage(bm)
        g.Clear(Color.White)
        Return g
    End Function

    Public Overrides Sub OnStartPrint(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintEventArgs)
        pdf = New pdfDocument(Title, Author)
        MyBase.OnStartPrint(document, e)
    End Sub

    Public Overrides Sub OnEndPage(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintPageEventArgs)
        Dim p As pdfPage = pdf.addPage(e.PageBounds.Height, e.PageBounds.Width)
        p.addImage(bm, 0, 0)
        MyBase.OnEndPage(document, e)
    End Sub

    Public Overrides Sub OnEndPrint(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintEventArgs)
        pdf.createPDF(FileName)
        MyBase.OnEndPrint(document, e)
    End Sub

End Class

Here is a sample which creates a pdf of the Northwind product list.

Imports System.Data.SqlClient

Public Class Form1
    Public WithEvents p As New Printing.PrintDocument
    Dim iRecord As Integer = 0
    Dim fntPrice As New Font("Arial", 12)
    Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim conn As SqlConnection
        Dim da As SqlDataAdapter

        strConn = "Server = .\SQLEXPRESS;"
        strConn &= "Database = Northwind; Integrated Security = SSPI;"
        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter("Select ProductName, UnitPrice From Products", conn)
        da.Fill(ds, "Products")

        DataGridView1.DataSource = ds.Tables("Products")
    End Sub

    Private Sub p_BeginPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles p.BeginPrint
        iRecord = 0
    End Sub

    Private Sub p_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles p.PrintPage
        Dim g As Graphics = e.Graphics
        Dim iPageHeight As Integer = e.PageBounds.Height
        Dim iPageWidth As Integer = e.PageBounds.Width
        Dim iFntHeight As Integer = CInt(g.MeasureString("Test", fntPrice).Height)
        Dim iLinesPerPage As Integer = iPageHeight \ iFntHeight - 15
        Dim yPos As Integer = 0
        Dim iTop As Integer
        Dim iMax As Integer = ds.Tables("Products").Rows.Count
        Dim strDescription As String
        Dim x As Integer
        Dim xPos As Integer
        Dim strPrice As String
        Dim fntTitle As Font = New Font("Microsoft Sans Serf", 14)
        Dim iCount As Integer = ds.Tables("Products").Rows.Count
        Dim strDate As String = Trim(Now.ToLongDateString)
        Dim sf As New StringFormat
        sf.Alignment = StringAlignment.Far

        xPos = CInt(iPageWidth - g.MeasureString("Price List", fntTitle).Width) \ 2
        g.DrawString("Price List", fntTitle, Brushes.Black, xPos, 10)
        yPos = 10 + CInt(g.MeasureString("Price List", fntTitle).Height)

        xPos = CInt(iPageWidth - g.MeasureString(strDate, fntPrice).Width) \ 2
        g.DrawString(strDate, fntPrice, Brushes.Black, xPos, yPos)
        yPos += 2 * iFntHeight
        g.DrawString("Product", fntPrice, Brushes.Black, 50, yPos)

        g.DrawString("Price", fntPrice, Brushes.Black, _
                New Rectangle(430, yPos, 100, 2 * iFntHeight), sf)

        yPos += iFntHeight
        g.DrawLine(Pens.Black, 0, yPos, iPageWidth, yPos)

        e.HasMorePages = True
        iTop = yPos

        For x = 0 To iLinesPerPage
            If iRecord < imax Then
                With ds.Tables("Products").Rows(iRecord)
                    strDescription = .Item("ProductName").ToString
                    strPrice = Convert.ToDecimal(.Item("UnitPrice")).ToString("c")
                End With
                Dim rName As New Rectangle(5, yPos, 400, iFntHeight)
                Dim rPrice As New Rectangle(430, yPos, 100, iFntHeight)

                g.DrawString(strDescription, fntPrice, Brushes.Black, rName)
                g.DrawString(strPrice, fntPrice, Brushes.Black, rPrice, sf)
            Else
                e.HasMorePages = False
            End If
            yPos += iFntHeight
            iRecord += 1
        Next
        fntTitle.Dispose()
        If e.HasMorePages = False Then iRecord = 0
    End Sub

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        Dim pc As New PdfPrintController
        pc.Title = "Test Pdf"
        pc.Author = "Ken Tucker"
        pc.FileName = "Test.pdf"
        p.PrintController = pc
        p.Print()
    End Sub
End Class

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.

VB Power Packs Data Repeater Control

In Feburary 2008 the VB Power packs team released version 3 of there VB Power Packs 2005.  This version included a DataRepeater control.

               The DataRepeater control allows you use standard windows controls to display your data in a scrollable container.   The included documentation shows you how to bind the DataRepeater to a typed dataset.  This article will show you how to bind to a datatable in code. 

               To start off with create a new windows forms application and drop a datarepeater on the form.  In the datarepeater drop a picturebox (pbCategory) and a label (lblName).  

In the forms load event lets add some code to connect to the database.  I am binding the datarepeater to a bindingsource because it is a good practice. 

Dim bs As New BindingSource

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim conn As New SqlConnection("Server = .\SQLExpress;Database = NorthWind; Integrated Security = SSPI;")
    Dim dt As New DataTable

    Dim da As New SqlDataAdapter("Select * from Categories", conn)
    da.Fill(dt)
    bs.DataSource = dt

    DataRepeater1.DataSource = bs
End Sub

The DataRepeater has a DrawItem event which allows us to put the data in the controls for each item.  You can also use this event for formatting the data for display

Private Function GetBitmap(ByVal Pic() As Byte) As Bitmap
    Dim ms As New System.IO.MemoryStream
    Dim bm As Bitmap
    ms.Write(Pic, 78, Pic.Length - 78)
    bm = New Bitmap(ms)
    Return bm
End Function

Private Sub DataRepeater1_DrawItem(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles DataRepeater1.DrawItem
    Dim currItem As DataRowView = bs.Item(e.DataRepeaterItem.ItemIndex)

    DirectCast(e.DataRepeaterItem.Controls("pbCategory"), PictureBox).Image = GetBitmap(DirectCast(currItem.Item("Picture"), Byte()))
    DirectCast(e.DataRepeaterItem.Controls("lblName"), Label).Text = currItem.Item("CategoryName").ToString
End Sub