- Posted by ken tucker on May 7, 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
- Posted by Ken Tucker on April 29, 2008
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
- Posted by Ken Tucker on April 29, 2008
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
- Posted by Ken Tucker on April 28, 2008
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)
Dim regNum As New Regex("^\d{4}$|^\d{6}$")
Debug.Print(regNum.IsMatch("1234").ToString)
Debug.Print(regNum.IsMatch( "12345").ToString)
Debug.Print(regNum.IsMatch("123456").ToString)
Output
True
False
True
- Posted by Ken Tucker on April 4, 2008
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
- Posted by Ken Tucker on April 1, 2008
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
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="sm1" EnablePageMethods="true" runat="server">
</asp:ScriptManager>
<table>
<tr>
<td>
Make
</td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server" Width="341px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Model
</td>
<td>
<asp:DropDownList ID="DropDownList2" runat="server" Width="341px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Color
</td>
<td>
<asp:DropDownList ID="DropDownList3" runat="server" Width="341px">
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<cc1:CascadingDropDown ID="CascadingDropDown1" runat="server" TargetControlID="DropDownList1"
Category="Make" PromptText="Please select a make" LoadingText="[Loading makes...]" ServiceMethod="GetDropDownContents">
</cc1:CascadingDropDown>
<cc1:CascadingDropDown ID="CascadingDropDown2" runat="server" TargetControlID="DropDownList2"
Category="Model" PromptText="Please select a model" LoadingText="[Loading models...]"
ServiceMethod="GetDropDownContents" ParentControlID="DropDownList1">
</cc1:CascadingDropDown>
<cc1:CascadingDropDown ID="CascadingDropDown3" runat="server" TargetControlID="DropDownList3"
Category="Color" PromptText="Please select a color" LoadingText="[Loading colors...]"
ServiceMethod="GetDropDownContents"
ParentControlID="DropDownList2">
</cc1:CascadingDropDown>
</form>
</body>
</html>
In the code behind I am using a shared class so I only have to load the xml document once.
Imports System.Xml
Partial Class _Default
Inherits System.Web.UI.Page
<System.Web.Services.WebMethod()> _
<System.Web.Script.Services.ScriptMethod()> _
Public Shared Function GetDropDownContents(ByVal knownCategoryValues As String, ByVal category As String) As AjaxControlToolkit.CascadingDropDownNameValue()
Dim knownCategoryValuesDictionary As StringDictionary = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
Return AjaxControlToolkit.CascadingDropDown.QuerySimpleCascadingDropDownDocument(CarsInfo.Document, CarsInfo.Hierarchy, knownCategoryValuesDictionary, category)
End Function
End Class
Public Class CarsInfo
Private Shared _Doc As XmlDocument
Private Shared _load As Boolean = True
Public Shared ReadOnly Property Document() As XmlDocument
Get
If _load Then
_Doc = New XmlDocument
_Doc.Load(HttpContext.Current.Server.MapPath("~/App_Data/CarsService.xml"))
_load = False
End If
Return _Doc
End Get
End Property
Public Shared ReadOnly Property Hierarchy() As String()
Get
Return New String() {"make", "model"}
End Get
End Property
End Class
- Posted by Ken Tucker on March 26, 2008
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
- Posted by Ken Tucker on March 12, 2008
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.
- Posted by Ken Tucker on February 21, 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
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
- Posted by Ken Tucker on January 28, 2008
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.