Validating Data entered in a DataRepeater control

• Posted by ken tucker on May 7, 2008
• 0 comments

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

Sql Server for Developers Space Coast .Net Meeting

• Posted by Ken Tucker on April 29, 2008
• 0 comments

When Wednesday June 11, 2008 @ 6:30PM

Want to know what you need to do to keep your DBA happy and make your application run really fast? Wondering if stored procedures are really faster than dynamic sql? Need some good guidelines for adding indexes? We'll spend an hour talking about performance in this very interactive presentation.

Andy Warren of End to End Training will be speaking.

Register if you plan to attend this event

Space Coast .Net Silverlight 2.0 Meeting

• Posted by Ken Tucker on April 29, 2008
• 0 comments

When: Wednesday May 21, 2008 @ 6:30 PM

Where: Space Coast Credit Union corp headquarters 

Silverlight 2 includes a cross-platform, cross-browser version of the .NET Framework, and enables a rich .NET development platform that runs in the browser.  Developers can write Silverlight applications using any .NET language (including VB, C#, JavaScript, IronPython and IronRuby).  We will ship Visual Studio 2008 and Expression Studio tool support that enables great developer / designer workflow and integration when building Silverlight applications.

Jeff Barnes of Microsoft will be presenting

There will be pizza and magazines available at this meeting. There will be a raffle for a Office 2007 pro at the end of the meeting.

Register if you plan to attend this event

Regular Expression Help

• Posted by Ken Tucker on April 28, 2008
• 0 comments

I got an regualr expression question today from one of my friends.  Basically she was using a regular expression  to validate a number was 4 or 6 digits long but the expression she was using ^\d{4,6}$ would validate numbers 5 digits long.  Lets look at this regular expression ^ means starts with. The \d means number and the {4,6} means 4 to 6 digits long.  The $ means ends with. The answer is to use a regular express with an or (the | means or)

Print to PDF

• Posted by Ken Tucker on April 4, 2008
• 0 comments

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

VB Cascading Drop Down Example

• Posted by Ken Tucker on April 1, 2008
• 0 comments

Here is simple VB example of the AjaxToolkit's CascadingDropDown extender.  For this example I use the CarsService.Xml found in the AjaxToolkits sample site.  The xml file needs to be placed in the App_data directory

Here is the Pages Html

DataGridViewCombobox AutoComplete

• Posted by Ken Tucker on March 26, 2008
• 0 comments

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

Uploading a Database to Dotster

• Posted by Ken Tucker on March 12, 2008
• 0 comments

The easiest way to upload a database to dotster is to use the sql hosting toolkit.   Visual studio 2008 installs the sql hosting toolkit for you otherwise you need to download and install from the link. 

Steps to do this

1) Create a database in the dotster control panel.

2) In the server explorer create a link to the database you want to upload. 

3) Right click on the database and select publish to provider in the wizard make sure you select sql 2000 as the target database schema.

4) On the codeplex website they use to have a webpage available to use to help run the script to create your database.  Now they have a webservice for this.   I kind of think the webservice is over kill to just publish 1 database.  So create a c# website which targets the .net framework 2.0

5) Add the script file you created in step 3 to the website.

6) Add the following code to webpage

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Web;
using System.IO;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace WebApplication3
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string fileName = Server.MapPath(@"<<YOUR_SCRIPTFILE>>.SQL");

            // Connection string to the server you want to execute against
            string connectionString = @"Server=<<YOUR_SERVER>>;User ID=<<YOUR_USERNAME>>;Password=<<YOUR_PASSWORD>>;Initial Catalog=<<YOUR_DATABASE>>";

            // Timeout of batches (in seconds)
            int timeout = 600;

            SqlConnection conn = null;
            try
            {
                Response.Write(String.Format("Opening file {0}<BR>", fileName));

                // read file
                using (StreamReader sr = new StreamReader(new FileStream(fileName, FileMode.Open)))
                {
                    Response.Write("Connecting to SQL Server database...<BR>");

                    // Create new connection to database
                    conn = new SqlConnection(connectionString);

                    conn.Open();

                    while (!sr.EndOfStream)
                    {
                        StringBuilder sb = new StringBuilder();
                        SqlCommand cmd = conn.CreateCommand();

                        while (!sr.EndOfStream)
                        {
                            string s = sr.ReadLine();
                            if (s != null && s.ToUpper().Trim().Equals("GO"))
                            {
                                break;
                            }

                            sb.AppendLine(s);
                        }

                        // Execute T-SQL against the target database
                        try
                        {
                            cmd.CommandText = sb.ToString();
                            cmd.CommandTimeout = timeout;

                            cmd.ExecuteNonQuery();

                        }
                        catch (Exception ex)
                        {
                            Response.Write(sb.ToString().Replace("\n", @"<br/>"));
                            Response.Write(ex.Message.ToString() + @"<br />");
                        }
                    }

                }
                Response.Write("T-SQL file executed successfully");
            }
            catch (Exception ex)
            {
                Response.Write(String.Format("An error occured: {0}", ex.ToString()));
            }
            finally
            {
                // Close out the connection
                //
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                    catch (Exception e1)
                    {
                        Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e1.ToString()));
                    }
                }
            }

        }
    }
}

Uploasd the website to dotster and open the webpage and your database should be copied to the new database.  Make sure you delete the website after you created the database to prevent someone from accidently undo changes to the database after you uploaded it.

VB Power Packs DataRepeater control

• Posted by Ken Tucker on February 21, 2008
• 0 comments

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

Datagridview and Linq issue

• Posted by Ken Tucker on January 28, 2008
• 1 comments

I had some one ask me an interesting question about using linq with the datagridview

When I bind a datagridview to this query

Dim names() As String = {"hello11", "hello212", "hello123", "hello124", "hello2325", "hello336", "hello457"}
Dim query = From s In names _
            Order By s _
            Select s
Dim bs As New BindingSource
bs.DataSource = query
DataGridView1.DataSource = bs

Why do I get these results?

The answer the datagridview will show the properties of the class in the list bound to the datagridview.  In this case we are bound to a list of string and the only bindable property is its Length.  

If you change the query to this

Dim names() As String = {"hello11", "hello212", "hello123", "hello124", "hello2325", "hello336", "hello457"}
Dim query = From s In names _
            Order By s _
            Select New With {.Name = s}
Dim bs As New BindingSource
bs.DataSource = query
DataGridView1.DataSource = bs

You will get the expected results

I should also mention that you can not bind a datagridview to a linq query directory.  You need to bind the query to a bindingsource and bind the datagridview to the bindingsource or bind the datagridview to the queries ToList method.