VB-Tips

DataGridView: Text and Image Column

Sunday, May 25, 2008

A VB version of the Text and Image Column in the DataGridView FAQ

Imports System.Data.SqlClient

Public Class Form1
Dim bs As New BindingSource
Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String = "Server = .\SQLEXPRESS;Database = Northwind;Integrated Security = SSPI;"
        Dim conn As New SqlConnection(strConn)
        Dim da As New SqlDataAdapter("Select * from Products", conn)
        da.Fill(dt)
        bs.DataSource = dt.DefaultView
        DataGridView1.DataSource = bs
        DataGridView1.Columns.Remove("ProductID")
        Dim dc As New TextAndImageColumn
        dc.DataPropertyName = "ProductID"
        dc.HeaderText = "ID"
        dc.Image = My.Resources.RedImage
        DataGridView1.Columns.Insert(0, dc)
        ' change the image for the second row
        DirectCast(DataGridView1.Item(0, 1), TextAndImageCell).Image = My.Resources.BlueImage
    End Sub
End Class

Public Class TextAndImageColumn
    Inherits DataGridViewTextBoxColumn
    Private imageValue As Image
    Private m_imageSize As Size
    Public Sub New()
        Me.CellTemplate = New TextAndImageCell
    End Sub
    Public Overloads Overrides Function Clone() As Object
        Dim c As TextAndImageColumn = TryCast(MyBase.Clone, TextAndImageColumn)
        c.imageValue = Me.imageValue
        c.m_imageSize = Me.m_imageSize
        Return c
    End Function
    Public Property Image() As Image
        Get
            Return Me.imageValue
        End Get
        Set(ByVal value As Image)
            Me.imageValue = value
            Me.m_imageSize = value.Size
            Dim inheritedPadding As Padding = Me.DefaultCellStyle.Padding
            Me.DefaultCellStyle.Padding = New Padding(ImageSize.Width, inheritedPadding.Top, inheritedPadding.Right, inheritedPadding.Bottom)
        End Set
    End Property
    Private ReadOnly Property TextAndImageCellTemplate() As TextAndImageCell
        Get
            Return TryCast(Me.CellTemplate, TextAndImageCell)
        End Get
    End Property
    Friend ReadOnly Property ImageSize() As Size
        Get
            Return m_imageSize
        End Get
    End Property
End Class
Public Class TextAndImageCell
    Inherits DataGridViewTextBoxCell
    Private imageValue As Image
    Private imageSize As Size
    Public Overloads Overrides Function Clone() As Object
        Dim c As TextAndImageCell = TryCast(MyBase.Clone, TextAndImageCell)
        c.imageValue = Me.imageValue
        c.imageSize = Me.imageSize
        Return c
    End Function
    Public Property Image() As Image
        Get
            If Me.OwningColumn Is Nothing OrElse Me.OwningTextAndImageColumn Is Nothing Then
                Return imageValue
            Else
                If Not (Me.imageValue Is Nothing) Then
                    Return Me.imageValue
                Else
                    Return Me.OwningTextAndImageColumn.Image
                End If
            End If
        End Get
        Set(ByVal value As Image)
            Me.imageValue = value
            Me.imageSize = value.Size
            Dim inheritedPadding As Padding = Me.InheritedStyle.Padding
            Me.Style.Padding = New Padding(imageSize.Width, inheritedPadding.Top, inheritedPadding.Right, inheritedPadding.Bottom)
        End Set
    End Property
    Protected Overloads Overrides Sub Paint(ByVal graphics As Graphics, ByVal clipBounds As Rectangle, ByVal cellBounds As Rectangle, ByVal rowIndex As Integer, ByVal cellState As DataGridViewElementStates, ByVal value As Object, ByVal formattedValue As Object, ByVal errorText As String, ByVal cellStyle As DataGridViewCellStyle, ByVal advancedBorderStyle As DataGridViewAdvancedBorderStyle, ByVal paintParts As DataGridViewPaintParts)
        MyBase.Paint(graphics, clipBounds, cellBounds, rowIndex, cellState, value, formattedValue, errorText, cellStyle, advancedBorderStyle, paintParts)
        If Not (Me.Image Is Nothing) Then
            Dim container As System.Drawing.Drawing2D.GraphicsContainer = graphics.BeginContainer
            graphics.SetClip(cellBounds)
            graphics.DrawImageUnscaled(Me.Image, cellBounds.Location)
            graphics.EndContainer(container)
        End If
    End Sub
    Private ReadOnly Property OwningTextAndImageColumn() As TextAndImageColumn
        Get
            Return TryCast(Me.OwningColumn, TextAndImageColumn)
        End Get
    End Property
End Class

Using Linq to Sql with Sql Compact Edition

Friday, May 09, 2008

The Linq to Sql supports Sql compact edition unfortunately the designer for Visual Studio 2008 does not. To generate the dbml file use the SqlMetal.exe utility.

First add the sql compact edition database to your project and cancel out of the wizard to generate a typed dataset. For this example I added the Northwind database. Open the Visual Studio 2008 command prompt go to the projects directory. The command to generate the dbml is Sqlmetal.exe /dbml:northwind.dbml northwind.sdf /language:vb

After the file is generated from the project menu select add existing item and add the dbml file just generated.

Sample Linq query

Dim db As New Northwind("Data Source= Northwind.sdf")
db.Log = Console.Out
Dim q = From p In db.Products _
Where p.UnitsInStock > 3 _
Select p.ProductName, p.UnitPrice

Dim bs As New BindingSource

bs.DataSource = q
DataGridView1.DataSource = bs

Validating Data entered in a DataRepeater control

Wednesday, May 07, 2008

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

VB Power Packs DataRepeater control

Saturday, May 03, 2008

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

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

Sync Services Part 2

Saturday, January 26, 2008

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 Getting started

Friday, January 25, 2008

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

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

    Me.ProductsBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.NorthwindDataSet)
    ' Call SyncAgent.Synchronize() to initiate the synchronization process.
    ' Synchronization only updates the local database, not your project’s data source.
    Dim syncAgent As NorthwindSyncAgent = New NorthwindSyncAgent()
    Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()
    Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)
End Sub

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.

Simple version of a Random Listbox

Monday, December 31, 2007

To show in a simple way how to get numbers from a textbox.
To see a better way use the tip Truly Random Numbers like this.

Public Class Form1

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

Me.ListBox1.Items.Clear()
Me.ListBox1.Items.AddRange(New Object() {"Bach", "Beethoven", "Brahms", "Debussy", "Handel", "Mozart", "Ravel", "Strauss", "Wagner"})

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If ListBox1.Items.Count > 0 Then

Dim shuffle As New Random(Now.Millisecond)
ListBox1.SelectedIndex = shuffle.Next(0, ListBox1.Items.Count)
ListBox2.Items.Add(ListBox1.SelectedItem)
ListBox1.Items.RemoveAt(ListBox1.SelectedIndex)
ListBox1.SelectedIndex = -1

End If

End Sub

End Class

Cultures: Simple sample

Wednesday, December 26, 2007

Cultures and globalisation looks often much difficult than it is

Threading.Thread.CurrentThread.CurrentCulture = New Globalization.CultureInfo("en-US")
MessageBox.Show(Now.ToLongDateString)
Threading.Thread.CurrentThread.CurrentCulture = New Globalization.CultureInfo("en-GB")
MessageBox.Show(Now.ToLongDateString)
Threading.Thread.CurrentThread.CurrentCulture = Globalization.CultureInfo.InstalledUICulture
MessageBox.Show(Now.ToLongDateString)

DateTime: Calculate the time between some dates (VB 2008)

Wednesday, December 26, 2007

In this Tip we show the possibilities of VB2008 with option Infer.
As well is showed how the timespan in calculated between 2 dates

The code results in strongly typed DateTime and TimeSpan types as you can see.
(Changed from a sample from Armin Zingler)

'This is standard in version 2008
Option Infer ON
Option Strict ON

Dim d1 = Date.Parse(InputBox("Enter a date"))
Dim d2 = Now
Dim ts = d1.Subtract(d2)
MessageBox.Show("Difference is: " & ts.TotalMinutes & " minutes")

OpenXml: Using Linq to XML to create an excel spreadsheet

Friday, November 30, 2007

For this example we are going to create a Excel 2007 spreadsheet using the Microsoft OpenXml Sdk and Linq to XML.

To start with lets create a new Windows forms app which targets the .Net Framework 3.5. Add a Linq to Sql design surface to your project and name it Northwind and drag the Northwind Products table on to the surface. On the windows form I added a DataGridview to display the data we are going to export to excel. We also need a button named btnExport on the form.

To create a excel spreadsheet we need to use the openxml sdk to create a spreadsheet document, workbook, worksheet, and a string table.

       Using doc = SpreadsheetDocument.Create("Export.xlsx", SpreadsheetDocumentType.Workbook)
            Dim workbook = doc.AddWorkbookPart
            Dim stringTable = workbook.AddNewPart(Of SharedStringTablePart)()
            Dim worksheet = workbook.AddNewPart(Of WorksheetPart)()

The worksheet, workbook, and string table are xml documents contained inside a package. Before we get to far we need to import a few xml namespaces

Imports <xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

Now that we imported the name space we can use some of the new xml features in VB 2008 to create the xml documents. Since we are not using a string table we just need a blank xml file

Dim xmlStringTable = <sst></sst>

The workbook xml needs to relate the spreadsheet with its id

           Dim xmlWorkbook = <workbook>
                                  <sheets>
                                      <sheet name="Exported" sheetId="1" r:id=<%= sheetId %>></sheet>
                                  </sheets>
                              </workbook>

Note the <%= sheetId %> allows you to get data from a variable

Finally we need to create the worksheet. In the worksheet we set the column widths and use a linq query to populate the data.

            Dim xmlWorkSheet = <worksheet>
                                   <sheetFormatPr defaultRowHeight="15"/>
                                   <cols>
                                       <col min="1" max="1" width="30" bestFit="1" customWidth="1"/>
                                       <col min="2" max="2" width="10" bestFit="1" customWidth="1"/>
                                   </cols>
                                   <sheetData>
                                       <row>
                                           <c t="inlineStr">
                                               <is>
                                                   <t>Product Name</t>
                                               </is>
                                           </c>
                                           <c t="inlineStr">
                                               <is>
                                                   <t>Unit Price</t>
                                               </is>
                                           </c>
                                       </row>
                                       <%= From p In db.Products Select _
                                           <row>
                                               <c t="inlineStr">
                                                   <is>
                                                       <t><%= p.ProductName %></t>
                                                   </is>
                                               </c>
                                               <c>
                                                   <v><%= p.UnitPrice %></v>
                                               </c>
                                           </row> %>
                                   </sheetData>
                               </worksheet>

Here is the function for writing the xml to the file

Sub WriteXmlToPart(ByVal part As OpenXmlPart, ByVal x As XElement)
Dim fs As New IO.StreamWriter(part.GetStream, New System.Text.UTF8Encoding)

        Dim xmlWriter As New Xml.XmlTextWriter(part.GetStream, New UTF8Encoding)
        xmlWriter.Formatting = Xml.Formatting.Indented
        Dim enc As New UTF8Encoding

        xmlWriter.WriteStartDocument()
        x.WriteTo(xmlWriter)
        xmlWriter.WriteEndDocument()
        xmlWriter.Flush()
        xmlWriter.Close()
    End Sub

Here is the complete listing for program

Imports Microsoft.Office.DocumentFormat.OpenXml.Packaging
Imports System.Text
Imports <xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

Public Class Form1
Dim bs As New BindingSource
Dim db As New NorthwindDataContext

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        bs.DataSource = From p In db.Products _
                        Select p.ProductName, p.UnitPrice

DataGridView1.DataSource = bs
End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Using doc = SpreadsheetDocument.Create("Export.xlsx", SpreadsheetDocumentType.Workbook)
            Dim workbook = doc.AddWorkbookPart
            Dim stringTable = workbook.AddNewPart(Of SharedStringTablePart)()
            Dim worksheet = workbook.AddNewPart(Of WorksheetPart)()
            Dim sheetId = workbook.GetIdOfPart(worksheet)

            'create the string table
            Dim xmlStringTable = <sst></sst>
            WriteXmlToPart(stringTable, xmlStringTable)

            'create the workbook
            Dim xmlWorkbook = <workbook>
                                  <sheets>
                                      <sheet name="Exported" sheetId="1" r:id=<%= sheetId %>></sheet>
                                  </sheets>
                              </workbook>
            WriteXmlToPart(workbook, xmlWorkbook)

            'create the spreadsheet
            Dim xmlWorkSheet = <worksheet>
                                   <sheetFormatPr defaultRowHeight="15"/>
                                   <cols>
                                       <col min="1" max="1" width="30" bestFit="1" customWidth="1"/>
                                       <col min="2" max="2" width="10" bestFit="1" customWidth="1"/>
                                   </cols>
                                   <sheetData>
                                       <row>
                                           <c t="inlineStr">
                                               <is>
                                                   <t>Product Name</t>
                                               </is>
                                           </c>
                                           <c t="inlineStr">
                                               <is>
                                                   <t>Unit Price</t>
                                               </is>
                                           </c>
                                       </row>
                                       <%= From p In db.Products Select _
                                           <row>
                                               <c t="inlineStr">
                                                   <is>
                                                       <t><%= p.ProductName %></t>
                                                   </is>
                                               </c>
                                               <c>
                                                   <v><%= p.UnitPrice %></v>
                                               </c>
                                           </row> %>
                                   </sheetData>
                               </worksheet>

WriteXmlToPart(worksheet, xmlWorkSheet)

End Using
End Sub

Sub WriteXmlToPart(ByVal part As OpenXmlPart, ByVal x As XElement)
Dim fs As New IO.StreamWriter(part.GetStream, New System.Text.UTF8Encoding)

        Dim xmlWriter As New Xml.XmlTextWriter(part.GetStream, New UTF8Encoding)
        xmlWriter.Formatting = Xml.Formatting.Indented
        Dim enc As New UTF8Encoding

        xmlWriter.WriteStartDocument()
        x.WriteTo(xmlWriter)
        xmlWriter.WriteEndDocument()
        xmlWriter.Flush()
        xmlWriter.Close()
    End Sub

End Class