Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
Dim dt As New DataTable
dt.Load(objSQLdatareader)
now datatable contains all data from datareader
we can assign datatable to datagrid, gridview datalist etc. as below
gridview.DataSource = dt
gridview1.DataBind()
Monday, 7 January 2008
Datepicker control asp.net
1. create new page:DatePicker.aspx
2. code behild : DatePicker.aspx.vb
code to place the all this page
1. Under head section of sample.aspx:
2. code sample.aspx page
3. code DatePicker.aspx
see the picture for -html code
4. Code DatePicker.aspx.vb
Imports System.Text
Public Class DatePicker
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
End Sub
'Protected WithEvents Calendar1 As System.Web.UI.WebControls.Calendar
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Nothing to do, here
End Sub
Private Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender
'// Clear the link from this day
e.Cell.Controls.Clear()
'// Add the custom link
Dim Link As System.Web.UI.HtmlControls.HtmlGenericControl
Link = New System.Web.UI.HtmlControls.HtmlGenericControl
Link.TagName = "a"
Link.InnerText = e.Day.DayNumberText
Link.Attributes.Add("href", String.Format("JavaScript:window.opener.document.{0}.value = '{1:d}'; window.close();", Request.QueryString("field"), e.Day.Date))
'// By default, this will highlight today's date.
If e.Day.IsSelected Then
Link.Attributes.Add("style", Me.Calendar1.SelectedDayStyle.ToString())
End If
'// Now add our custom link to the page
e.Cell.Controls.Add(Link)
End Sub
End Class
Messagebox in ASP.Net using div
1. Place a 'place holder' control in asp.net page and
2. call the class below with setting values for the properties
sample:
Dim newBox As MessageBox = New MessageBox()
newBox.MessageBoxButton = 2
newBox.MessageBoxTop = 150
newBox.MessageBoxLeft = 50
newBox.MessageBoxWidth = 500
newBox.MessageBoxHeight = 150
newBox.MessageBoxButtonWidth = 50
newBox.MessageBoxIDYes = "yesno"
newBox.MessageBoxIDNo = "yesno"
newBox.MessageBoxButtonYesText = "Continue"
newBox.MessageBoxButtonNoText = "Cancel"
NewBox.MessageBoxTitle = "Postcode has been covered."
newBox.MessageBoxMessage = aMessage '"Do you want to continue?"
newBox.MessageBoxImage = "images/information.gif"
PlaceHolder1.Controls.Add(newBox)
'Main Class here
Imports Microsoft.VisualBasic
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Drawing.Drawing2D
Public Class js
Public Function js()
End Function
End Class
Public Class MessageBox
Inherits WebControl
Private strLeft As String
Private strTop As String
Private intButton As Integer
Private strMessage As String
Private strTitle As String
Private strImage As String
Private strCss As String
Private strCssTitle As String
Private strCssMessage As String
Private strButtonYes As String
Private strButtonNo As String
Private strButtonCancel As String
Private strButtonWidth As Integer
Private strMessageBoxIDYes As String
Private strMessageBoxIDNo As String
Private strMessageBoxIDCancel As String
Private intMessageBoxWidth As Integer
Private intMessageBoxHeight As Integer
Private intMessageBoxImageWidth As Integer
Private intMessageBoxImageHeight As Integer
Dim homedirectory As String
Private strMessageBoxWidth As String
' Message box left position
#Region "properties"
Public Property MessageBoxLeft() As String
Get
Return strLeft
End Get
Set(ByVal Value As String)
strLeft = Value
End Set
End Property
' Message box top position
Public Property MessageBoxTop() As String
Get
Return strTop
End Get
Set(ByVal Value As String)
strTop = Value
End Set
End Property
' Number of buttons you want to display in the message box
Public Property MessageBoxButton() As Integer
Get
Return intButton
End Get
Set(ByVal Value As Integer)
intButton = Value
End Set
End Property
' Customize message you want to display in the message box
Public Property MessageBoxMessage() As String
Get
Return strMessage
End Get
Set(ByVal Value As String)
strMessage = Value
End Set
End Property
' Title you want to display in the message box
Public Property MessageBoxTitle() As String
Get
Return strTitle
End Get
Set(ByVal Value As String)
strTitle = Value
End Set
End Property
' Image you want to display in the message box (like information / warning)
Public Property MessageBoxImage() As String
Get
Return strImage
End Get
Set(ByVal Value As String)
strImage = Value
End Set
End Property
' Message box ID for Yes button
Public Property MessageBoxIDYes() As String
Get
Return strMessageBoxIDYes
End Get
Set(ByVal Value As String)
strMessageBoxIDYes = Value
End Set
End Property
' Message box ID for No button
Public Property MessageBoxIDNo() As String
Get
Return strMessageBoxIDNo
End Get
Set(ByVal Value As String)
strMessageBoxIDNo = Value
End Set
End Property
' Message box ID for Cancel button
Public Property MessageBoxIDCancel() As String
Get
Return strMessageBoxIDCancel
End Get
Set(ByVal Value As String)
strMessageBoxIDCancel = Value
End Set
End Property
' Style you want to incorporate for message box
Public Property MessageBoxCss() As String
Get
Return strCss
End Get
Set(ByVal Value As String)
strCss = Value
End Set
End Property
Public Property MessageBoxCssTitle() As String
Get
Return strCssTitle
End Get
Set(ByVal Value As String)
strCssTitle = Value
End Set
End Property
Public Property MessageBoxCssMessage() As String
Get
Return strCssMessage
End Get
Set(ByVal Value As String)
strCssMessage = Value
End Set
End Property
' Message box Text for Yes button
Public Property MessageBoxButtonYesText() As String
Get
Return strButtonYes
End Get
Set(ByVal Value As String)
strButtonYes = Value
End Set
End Property
' Message box Text for No button
Public Property MessageBoxButtonNoText() As String
Get
Return strButtonNo
End Get
Set(ByVal Value As String)
strButtonNo = Value
End Set
End Property
' Message box Text for Cancel button
Public Property MessageBoxButtonCancelText() As String
Get
Return strButtonCancel
End Get
Set(ByVal Value As String)
strButtonCancel = Value
End Set
End Property
' Message box buttons width
Public Property MessageBoxButtonWidth() As Integer
Get
Return strButtonWidth
End Get
Set(ByVal Value As Integer)
strButtonWidth = Value
End Set
End Property
'' Message box width
'Public Property MessageBoxWidth() As Integer
' Get
' Return intMessageBoxWidth
' End Get
' Set(ByVal Value As Integer)
' intMessageBoxWidth = Value
' End Set
'End Property
' Message box width
Public Property MessageBoxWidth() As String
Get
Return strMessageBoxWidth
End Get
Set(ByVal Value As String)
strMessageBoxWidth = Value
End Set
End Property
' Message box height
Public Property MessageBoxHeight() As Integer
Get
Return intMessageBoxHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxHeight = Value
End Set
End Property
' Message box image width
Public Property MessageBoxImageWidth() As Integer
Get
Return intMessageBoxImageWidth
End Get
Set(ByVal Value As Integer)
intMessageBoxImageWidth = Value
End Set
End Property
' Message box image height
Public Property MessageBoxImageHeight() As Integer
Get
Return intMessageBoxImageHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxImageHeight = Value
End Set
End Property
#End Region
Protected Friend layer As HtmlGenericControl
Protected Friend ilayer As HtmlGenericControl
Protected Friend img As HtmlGenericControl
Protected Friend div As HtmlGenericControl
Protected Friend ButtonOK As Button
Protected Friend ButtonYes As Button
Protected Friend ButtonNo As Button
Protected Friend ButtonCancel As Button
Protected Alertimage As System.Web.UI.WebControls.Image
Public Sub New()
MyBase.New("div")
End Sub
Protected Overrides Sub OnInit(ByVal e As EventArgs)
MyBase.OnInit(e)
' Default properties settings for message box control
If strLeft Is Nothing Then
strLeft = "250"
End If
If strTop Is Nothing Then
strTop = "250"
End If
If strTitle Is Nothing Then
strTitle = "MessageBox"
End If
If intButton < 0 Then
intButton = 1
End If
If strMessageBoxIDYes Is Nothing Then
strMessageBoxIDYes = "MessageBoxIDYes"
End If
If strMessageBoxIDNo Is Nothing Then
strMessageBoxIDNo = "MessageBoxIDNo"
End If
If strMessageBoxIDCancel Is Nothing Then
strMessageBoxIDCancel = "MessageBoxIDCancel"
End If
If strCss Is Nothing Then
strCss = ""
End If
If strCssMessage Is Nothing Then
strCssMessage = ""
End If
If strCssTitle Is Nothing Then
strCssTitle = ""
End If
If strMessage Is Nothing Then
strMessage = "No message to display here."
End If
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
If strButtonYes Is Nothing Then
strButtonYes = "OK"
End If
ElseIf intButton > 1 And intButton < 4 Then
If strButtonYes Is Nothing Then
strButtonYes = "Approve"
End If
If strButtonNo Is Nothing Then
strButtonNo = "Cancel"
End If
If strButtonCancel Is Nothing Then
strButtonCancel = "Ignore"
End If
End If
If strButtonWidth < 5 Then
strButtonWidth = 70
End If
If intMessageBoxWidth < 10 Then
intMessageBoxWidth = 250
End If
If intMessageBoxHeight < 1 Then
intMessageBoxHeight = 8
End If
If intMessageBoxImageWidth < 5 Then
intMessageBoxImageWidth = 36
End If
If intMessageBoxImageHeight < 5 Then
intMessageBoxImageHeight = 36
End If
If homedirectory Is Nothing Then
homedirectory = Me.Page.Request.PhysicalApplicationPath
End If
End Sub
#Region "createChildControls"
Protected Overrides Sub createChildControls()
' Creating message box
Dim myRow As TableRow
Dim myCell As TableCell
Dim myTable As Table = New Table()
myTable.BorderWidth = New Unit(0)
myTable.CellSpacing = 0
myTable.Width = New Unit(intMessageBoxWidth)
myTable.Height = New Unit(intMessageBoxHeight)
Controls.Add(myTable)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
Dim NewLabel As Label = New Label()
NewLabel.Text = strTitle
NewLabel.CssClass = strCssTitle
myCell.Controls.Add(NewLabel)
myCell.ID = "dragbar"
myCell.ColumnSpan = 5
myCell.CssClass = strCssTitle
If strCssTitle = "" Then
myCell.ForeColor = System.Drawing.Color.White
myCell.BackColor = System.Drawing.Color.DarkBlue
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
myCell.Style.Add("CURSOR", "hand")
End If
myRow.Cells.Add(myCell)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
Dim myRow1 As TableRow
Dim myCell1 As TableCell
Dim myTable1 As Table = New Table()
myTable1.BorderWidth = New Unit(0)
myTable1.CellSpacing = 0
myCell.Controls.Add(myTable1)
myRow1 = New TableRow()
myRow1.BorderWidth = New Unit(0)
myTable1.Rows.Add(myRow1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.Width = New Unit(36)
Dim Alertimage As System.Web.UI.WebControls.Image = New System.Web.UI.WebControls.Image()
Alertimage.Height = New Unit(intMessageBoxImageHeight)
Alertimage.Width = New Unit(intMessageBoxImageWidth)
Alertimage.BorderWidth = New Unit(0)
Alertimage.ImageUrl = strImage
myCell1.Controls.Add(Alertimage)
myRow1.Cells.Add(myCell1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.CssClass = strCssMessage
If strCssMessage = "" Then
myCell1.HorizontalAlign = HorizontalAlign.Center
myCell1.ForeColor = System.Drawing.Color.Black
myCell1.BackColor = System.Drawing.Color.LightGray
myCell1.BorderColor = System.Drawing.Color.LightGray
myCell1.Font.Name = "Verdana"
myCell1.Font.Bold = True
myCell1.Font.Size = New FontUnit(8)
End If
Dim NewLabel1 As Label = New Label()
NewLabel1.Text = strMessage
myCell1.Controls.Add(NewLabel1)
myRow1.Cells.Add(myCell1)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
myCell.HorizontalAlign = HorizontalAlign.Center
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonOK = New Button()
ButtonOK.ID = strMessageBoxIDYes
ButtonOK.Text = strButtonYes
ButtonOK.Width = New Unit(strButtonWidth)
ButtonOK.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonOK)
myRow.Cells.Add(myCell)
End If
If intButton > 1 And intButton < 4 Then
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Right
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonYes = New Button()
ButtonYes.ID = strMessageBoxIDYes
ButtonYes.Text = strButtonYes
ButtonYes.Width = New Unit(strButtonWidth)
ButtonYes.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonYes)
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.Width = New Unit(20)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
If intButton = 2 Then
myCell.HorizontalAlign = HorizontalAlign.Left
ElseIf intButton = 3 Then
myCell.HorizontalAlign = HorizontalAlign.Center
End If
ButtonNo = New Button()
ButtonNo.ID = strMessageBoxIDNo
ButtonNo.Text = strButtonNo
ButtonNo.Width = New Unit(strButtonWidth)
ButtonNo.Attributes("WIDTH") = strButtonWidth.ToString()
ButtonNo.Attributes("HEIGHT") = strButtonWidth.ToString()
ButtonNo.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonNo)
myRow.Cells.Add(myCell)
If intButton = 3 Then
myCell = New TableCell()
myCell.Width = New Unit(10)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Left
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonCancel = New Button()
ButtonCancel.ID = strMessageBoxIDCancel
ButtonCancel.Text = strButtonCancel
ButtonCancel.Width = New Unit(strButtonWidth)
ButtonCancel.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonCancel)
myRow.Cells.Add(myCell)
End If
End If
End Sub
#End Region
Protected Overrides Sub AddAttributesToRender(ByVal writer As HtmlTextWriter)
' Rendering message box control to the browser
MyBase.AddAttributesToRender(writer)
writer.AddAttribute(HtmlTextWriterAttribute.Id, "showimage")
'writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strLeft + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction="135," strength="3;"")
writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strMessageBoxWidth + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction=135), strength=3;")
End Sub
End Class
2. call the class below with setting values for the properties
sample:
Dim newBox As MessageBox = New MessageBox()
newBox.MessageBoxButton = 2
newBox.MessageBoxTop = 150
newBox.MessageBoxLeft = 50
newBox.MessageBoxWidth = 500
newBox.MessageBoxHeight = 150
newBox.MessageBoxButtonWidth = 50
newBox.MessageBoxIDYes = "yesno"
newBox.MessageBoxIDNo = "yesno"
newBox.MessageBoxButtonYesText = "Continue"
newBox.MessageBoxButtonNoText = "Cancel"
NewBox.MessageBoxTitle = "Postcode has been covered."
newBox.MessageBoxMessage = aMessage '"Do you want to continue?"
newBox.MessageBoxImage = "images/information.gif"
PlaceHolder1.Controls.Add(newBox)
'Main Class here
Imports Microsoft.VisualBasic
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Drawing.Drawing2D
Public Class js
Public Function js()
End Function
End Class
Public Class MessageBox
Inherits WebControl
Private strLeft As String
Private strTop As String
Private intButton As Integer
Private strMessage As String
Private strTitle As String
Private strImage As String
Private strCss As String
Private strCssTitle As String
Private strCssMessage As String
Private strButtonYes As String
Private strButtonNo As String
Private strButtonCancel As String
Private strButtonWidth As Integer
Private strMessageBoxIDYes As String
Private strMessageBoxIDNo As String
Private strMessageBoxIDCancel As String
Private intMessageBoxWidth As Integer
Private intMessageBoxHeight As Integer
Private intMessageBoxImageWidth As Integer
Private intMessageBoxImageHeight As Integer
Dim homedirectory As String
Private strMessageBoxWidth As String
' Message box left position
#Region "properties"
Public Property MessageBoxLeft() As String
Get
Return strLeft
End Get
Set(ByVal Value As String)
strLeft = Value
End Set
End Property
' Message box top position
Public Property MessageBoxTop() As String
Get
Return strTop
End Get
Set(ByVal Value As String)
strTop = Value
End Set
End Property
' Number of buttons you want to display in the message box
Public Property MessageBoxButton() As Integer
Get
Return intButton
End Get
Set(ByVal Value As Integer)
intButton = Value
End Set
End Property
' Customize message you want to display in the message box
Public Property MessageBoxMessage() As String
Get
Return strMessage
End Get
Set(ByVal Value As String)
strMessage = Value
End Set
End Property
' Title you want to display in the message box
Public Property MessageBoxTitle() As String
Get
Return strTitle
End Get
Set(ByVal Value As String)
strTitle = Value
End Set
End Property
' Image you want to display in the message box (like information / warning)
Public Property MessageBoxImage() As String
Get
Return strImage
End Get
Set(ByVal Value As String)
strImage = Value
End Set
End Property
' Message box ID for Yes button
Public Property MessageBoxIDYes() As String
Get
Return strMessageBoxIDYes
End Get
Set(ByVal Value As String)
strMessageBoxIDYes = Value
End Set
End Property
' Message box ID for No button
Public Property MessageBoxIDNo() As String
Get
Return strMessageBoxIDNo
End Get
Set(ByVal Value As String)
strMessageBoxIDNo = Value
End Set
End Property
' Message box ID for Cancel button
Public Property MessageBoxIDCancel() As String
Get
Return strMessageBoxIDCancel
End Get
Set(ByVal Value As String)
strMessageBoxIDCancel = Value
End Set
End Property
' Style you want to incorporate for message box
Public Property MessageBoxCss() As String
Get
Return strCss
End Get
Set(ByVal Value As String)
strCss = Value
End Set
End Property
Public Property MessageBoxCssTitle() As String
Get
Return strCssTitle
End Get
Set(ByVal Value As String)
strCssTitle = Value
End Set
End Property
Public Property MessageBoxCssMessage() As String
Get
Return strCssMessage
End Get
Set(ByVal Value As String)
strCssMessage = Value
End Set
End Property
' Message box Text for Yes button
Public Property MessageBoxButtonYesText() As String
Get
Return strButtonYes
End Get
Set(ByVal Value As String)
strButtonYes = Value
End Set
End Property
' Message box Text for No button
Public Property MessageBoxButtonNoText() As String
Get
Return strButtonNo
End Get
Set(ByVal Value As String)
strButtonNo = Value
End Set
End Property
' Message box Text for Cancel button
Public Property MessageBoxButtonCancelText() As String
Get
Return strButtonCancel
End Get
Set(ByVal Value As String)
strButtonCancel = Value
End Set
End Property
' Message box buttons width
Public Property MessageBoxButtonWidth() As Integer
Get
Return strButtonWidth
End Get
Set(ByVal Value As Integer)
strButtonWidth = Value
End Set
End Property
'' Message box width
'Public Property MessageBoxWidth() As Integer
' Get
' Return intMessageBoxWidth
' End Get
' Set(ByVal Value As Integer)
' intMessageBoxWidth = Value
' End Set
'End Property
' Message box width
Public Property MessageBoxWidth() As String
Get
Return strMessageBoxWidth
End Get
Set(ByVal Value As String)
strMessageBoxWidth = Value
End Set
End Property
' Message box height
Public Property MessageBoxHeight() As Integer
Get
Return intMessageBoxHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxHeight = Value
End Set
End Property
' Message box image width
Public Property MessageBoxImageWidth() As Integer
Get
Return intMessageBoxImageWidth
End Get
Set(ByVal Value As Integer)
intMessageBoxImageWidth = Value
End Set
End Property
' Message box image height
Public Property MessageBoxImageHeight() As Integer
Get
Return intMessageBoxImageHeight
End Get
Set(ByVal Value As Integer)
intMessageBoxImageHeight = Value
End Set
End Property
#End Region
Protected Friend layer As HtmlGenericControl
Protected Friend ilayer As HtmlGenericControl
Protected Friend img As HtmlGenericControl
Protected Friend div As HtmlGenericControl
Protected Friend ButtonOK As Button
Protected Friend ButtonYes As Button
Protected Friend ButtonNo As Button
Protected Friend ButtonCancel As Button
Protected Alertimage As System.Web.UI.WebControls.Image
Public Sub New()
MyBase.New("div")
End Sub
Protected Overrides Sub OnInit(ByVal e As EventArgs)
MyBase.OnInit(e)
' Default properties settings for message box control
If strLeft Is Nothing Then
strLeft = "250"
End If
If strTop Is Nothing Then
strTop = "250"
End If
If strTitle Is Nothing Then
strTitle = "MessageBox"
End If
If intButton < 0 Then
intButton = 1
End If
If strMessageBoxIDYes Is Nothing Then
strMessageBoxIDYes = "MessageBoxIDYes"
End If
If strMessageBoxIDNo Is Nothing Then
strMessageBoxIDNo = "MessageBoxIDNo"
End If
If strMessageBoxIDCancel Is Nothing Then
strMessageBoxIDCancel = "MessageBoxIDCancel"
End If
If strCss Is Nothing Then
strCss = ""
End If
If strCssMessage Is Nothing Then
strCssMessage = ""
End If
If strCssTitle Is Nothing Then
strCssTitle = ""
End If
If strMessage Is Nothing Then
strMessage = "No message to display here."
End If
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
If strButtonYes Is Nothing Then
strButtonYes = "OK"
End If
ElseIf intButton > 1 And intButton < 4 Then
If strButtonYes Is Nothing Then
strButtonYes = "Approve"
End If
If strButtonNo Is Nothing Then
strButtonNo = "Cancel"
End If
If strButtonCancel Is Nothing Then
strButtonCancel = "Ignore"
End If
End If
If strButtonWidth < 5 Then
strButtonWidth = 70
End If
If intMessageBoxWidth < 10 Then
intMessageBoxWidth = 250
End If
If intMessageBoxHeight < 1 Then
intMessageBoxHeight = 8
End If
If intMessageBoxImageWidth < 5 Then
intMessageBoxImageWidth = 36
End If
If intMessageBoxImageHeight < 5 Then
intMessageBoxImageHeight = 36
End If
If homedirectory Is Nothing Then
homedirectory = Me.Page.Request.PhysicalApplicationPath
End If
End Sub
#Region "createChildControls"
Protected Overrides Sub createChildControls()
' Creating message box
Dim myRow As TableRow
Dim myCell As TableCell
Dim myTable As Table = New Table()
myTable.BorderWidth = New Unit(0)
myTable.CellSpacing = 0
myTable.Width = New Unit(intMessageBoxWidth)
myTable.Height = New Unit(intMessageBoxHeight)
Controls.Add(myTable)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
Dim NewLabel As Label = New Label()
NewLabel.Text = strTitle
NewLabel.CssClass = strCssTitle
myCell.Controls.Add(NewLabel)
myCell.ID = "dragbar"
myCell.ColumnSpan = 5
myCell.CssClass = strCssTitle
If strCssTitle = "" Then
myCell.ForeColor = System.Drawing.Color.White
myCell.BackColor = System.Drawing.Color.DarkBlue
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
myCell.Style.Add("CURSOR", "hand")
End If
myRow.Cells.Add(myCell)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
Dim myRow1 As TableRow
Dim myCell1 As TableCell
Dim myTable1 As Table = New Table()
myTable1.BorderWidth = New Unit(0)
myTable1.CellSpacing = 0
myCell.Controls.Add(myTable1)
myRow1 = New TableRow()
myRow1.BorderWidth = New Unit(0)
myTable1.Rows.Add(myRow1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.Width = New Unit(36)
Dim Alertimage As System.Web.UI.WebControls.Image = New System.Web.UI.WebControls.Image()
Alertimage.Height = New Unit(intMessageBoxImageHeight)
Alertimage.Width = New Unit(intMessageBoxImageWidth)
Alertimage.BorderWidth = New Unit(0)
Alertimage.ImageUrl = strImage
myCell1.Controls.Add(Alertimage)
myRow1.Cells.Add(myCell1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.CssClass = strCssMessage
If strCssMessage = "" Then
myCell1.HorizontalAlign = HorizontalAlign.Center
myCell1.ForeColor = System.Drawing.Color.Black
myCell1.BackColor = System.Drawing.Color.LightGray
myCell1.BorderColor = System.Drawing.Color.LightGray
myCell1.Font.Name = "Verdana"
myCell1.Font.Bold = True
myCell1.Font.Size = New FontUnit(8)
End If
Dim NewLabel1 As Label = New Label()
NewLabel1.Text = strMessage
myCell1.Controls.Add(NewLabel1)
myRow1.Cells.Add(myCell1)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)
If intButton = 1 Or intButton > 3 Or intButton < 1 Then
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
myCell.HorizontalAlign = HorizontalAlign.Center
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonOK = New Button()
ButtonOK.ID = strMessageBoxIDYes
ButtonOK.Text = strButtonYes
ButtonOK.Width = New Unit(strButtonWidth)
ButtonOK.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonOK)
myRow.Cells.Add(myCell)
End If
If intButton > 1 And intButton < 4 Then
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Right
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonYes = New Button()
ButtonYes.ID = strMessageBoxIDYes
ButtonYes.Text = strButtonYes
ButtonYes.Width = New Unit(strButtonWidth)
ButtonYes.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonYes)
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.Width = New Unit(20)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
If intButton = 2 Then
myCell.HorizontalAlign = HorizontalAlign.Left
ElseIf intButton = 3 Then
myCell.HorizontalAlign = HorizontalAlign.Center
End If
ButtonNo = New Button()
ButtonNo.ID = strMessageBoxIDNo
ButtonNo.Text = strButtonNo
ButtonNo.Width = New Unit(strButtonWidth)
ButtonNo.Attributes("WIDTH") = strButtonWidth.ToString()
ButtonNo.Attributes("HEIGHT") = strButtonWidth.ToString()
ButtonNo.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonNo)
myRow.Cells.Add(myCell)
If intButton = 3 Then
myCell = New TableCell()
myCell.Width = New Unit(10)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If
myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Left
If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If
ButtonCancel = New Button()
ButtonCancel.ID = strMessageBoxIDCancel
ButtonCancel.Text = strButtonCancel
ButtonCancel.Width = New Unit(strButtonWidth)
ButtonCancel.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonCancel)
myRow.Cells.Add(myCell)
End If
End If
End Sub
#End Region
Protected Overrides Sub AddAttributesToRender(ByVal writer As HtmlTextWriter)
' Rendering message box control to the browser
MyBase.AddAttributesToRender(writer)
writer.AddAttribute(HtmlTextWriterAttribute.Id, "showimage")
'writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strLeft + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction="135," strength="3;"")
writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strMessageBoxWidth + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction=135), strength=3;")
End Sub
End Class
One database reusable class for SQL Server communication
create two classes
1. 'SQLConnection' (main class to communicate with db)
2. 'SQLStoredProcedureParameterInfo' to be called from main class to get parameter info.
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Collections.Generic
Imports System.Data.SqlClient
'class 1
Public Class SQLConnection
Inherits MarshalByRefObject
Implements IDisposable
'Private variables
Private m_sConnectionString As String
Private m_objConnection As SqlClient.SqlConnection = Nothing
Private m_objTransaction As SqlClient.SqlTransaction = Nothing
Private disposedValue As Boolean = False ' To detect redundant calls
Private Shared m_objCommandParameterInfo As New Hashtable
Public Sub New(ByVal ConnectionString As String)
m_sConnectionString = ConnectionString
End Sub
Public Function TestConnection() As Boolean
Dim bRetVal As Boolean = False
Const iTestTimeout As Integer = 5 'seconds
Try
With OpenConnection(iTestTimeout)
bRetVal = (.State = ConnectionState.Open)
End With
Catch ex As Exception
bRetVal = False
End Try
Return bRetVal
End Function
Public Function OpenConnection(Optional ByVal Timeout As Integer = 15) As SqlClient.SqlConnection
If (m_objConnection Is Nothing) Then
Dim sConnString As New SqlClient.SqlConnectionStringBuilder(m_sConnectionString)
sConnString.ConnectTimeout = Timeout
m_objConnection = New SqlClient.SqlConnection(sConnString.ToString)
End If
If m_objConnection.State = ConnectionState.Closed Then
With m_objConnection
.Open()
End With
End If
Return m_objConnection
End Function
Public Sub CloseConnection()
If (m_objConnection IsNot Nothing) Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End Sub
Public Function ConnectSP(ByVal Name As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.StoredProcedure
RetrieveCommandParameters(objRetVal)
End With
Return objRetVal
End Function
Public Function ConnectSQL(ByVal SQL As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.Text
End With
Return objRetVal
End Function
Public Function BeginTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
With OpenConnection()
m_objTransaction = .BeginTransaction()
End With
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function CommitTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Commit()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function RollbackTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Rollback()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Private Sub RetrieveCommandParameters(ByVal SQLCommand As SqlClient.SqlCommand)
Dim lstParameterInfo As List(Of SQLStoredProcedureParameterInfo)
With SQLCommand
If m_objCommandParameterInfo.Contains(.CommandText) Then
'Get cached parameter info
lstParameterInfo = m_objCommandParameterInfo(.CommandText)
'Loop through and add a new parameter
For Each objParameterInfo As SQLStoredProcedureParameterInfo In lstParameterInfo
.Parameters.Add(objParameterInfo.CreateParameter())
Next
Else
'Retrieve parameters from database server
SqlClient.SqlCommandBuilder.DeriveParameters(SQLCommand)
'Build parameter info list
lstParameterInfo = New List(Of SQLStoredProcedureParameterInfo)
For Each objParameter As SqlClient.SqlParameter In .Parameters
lstParameterInfo.Add(New SQLStoredProcedureParameterInfo(objParameter))
Next
'...and add into cache for next time
m_objCommandParameterInfo.Add(.CommandText, lstParameterInfo)
End If
End With
End Sub
' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
m_objTransaction = Nothing
If m_objConnection IsNot Nothing Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End If
' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
#Region " IDisposable Support "
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class
'Class 2
Public Class SQLStoredProcedureParameterInfo
Public Name As String
Public Type As System.Data.SqlDbType
Public Direction As System.Data.ParameterDirection
Public DefaultValue As Object
Public Size As Integer
Public Sub New(ByVal Parameter As SqlClient.SqlParameter)
With Parameter
Name = .ParameterName
Type = .SqlDbType
Direction = .Direction
DefaultValue = .Value
Size = .Size
End With
End Sub
Public Function CreateParameter() As SqlClient.SqlParameter
Dim objRetVal As New SqlClient.SqlParameter
With objRetVal
.ParameterName = Name
.SqlDbType = Type
.Direction = Direction
.Value = DefaultValue
.Size = Size
End With
Return objRetVal
End Function
End Class
''''method to use this class as below:
Using objSqlConnection As SQLConnection = New SQLConnection(ConfigurationManager.AppSettings.Item("SQLConnString").ToString)
Dim strSQL As String
'for SQL
Dim BusinessRegionIntroducerGroupId As Integer
strSQL = "select * from table1 where employid=" & Session("empId")
With objSqlConnection.ConnectSQL(strSQL)
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
If objSQLdatareader.Read Then
empname= objSQLdatareader.Item("empname")
End If
objSQLdatareader.Close()
End With
'for SP
strSQL = "spSaveEmployee"
With objSqlConnection.ConnectSP(strSQL)
.Parameters("@DeptId").Value = DeptId
.Parameters("@empid").Value = Session("empid").ToString
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
objSQLdatareader.Close()
End With
End Using
1. 'SQLConnection' (main class to communicate with db)
2. 'SQLStoredProcedureParameterInfo' to be called from main class to get parameter info.
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Collections.Generic
Imports System.Data.SqlClient
'class 1
Public Class SQLConnection
Inherits MarshalByRefObject
Implements IDisposable
'Private variables
Private m_sConnectionString As String
Private m_objConnection As SqlClient.SqlConnection = Nothing
Private m_objTransaction As SqlClient.SqlTransaction = Nothing
Private disposedValue As Boolean = False ' To detect redundant calls
Private Shared m_objCommandParameterInfo As New Hashtable
Public Sub New(ByVal ConnectionString As String)
m_sConnectionString = ConnectionString
End Sub
Public Function TestConnection() As Boolean
Dim bRetVal As Boolean = False
Const iTestTimeout As Integer = 5 'seconds
Try
With OpenConnection(iTestTimeout)
bRetVal = (.State = ConnectionState.Open)
End With
Catch ex As Exception
bRetVal = False
End Try
Return bRetVal
End Function
Public Function OpenConnection(Optional ByVal Timeout As Integer = 15) As SqlClient.SqlConnection
If (m_objConnection Is Nothing) Then
Dim sConnString As New SqlClient.SqlConnectionStringBuilder(m_sConnectionString)
sConnString.ConnectTimeout = Timeout
m_objConnection = New SqlClient.SqlConnection(sConnString.ToString)
End If
If m_objConnection.State = ConnectionState.Closed Then
With m_objConnection
.Open()
End With
End If
Return m_objConnection
End Function
Public Sub CloseConnection()
If (m_objConnection IsNot Nothing) Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End Sub
Public Function ConnectSP(ByVal Name As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.StoredProcedure
RetrieveCommandParameters(objRetVal)
End With
Return objRetVal
End Function
Public Function ConnectSQL(ByVal SQL As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand
'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection, m_objTransaction)
End If
With objRetVal
.CommandType = CommandType.Text
End With
Return objRetVal
End Function
Public Function BeginTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
With OpenConnection()
m_objTransaction = .BeginTransaction()
End With
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function CommitTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Commit()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Public Function RollbackTransaction() As Boolean
Dim bRetVal As Boolean = True
Try
m_objTransaction.Rollback()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try
Return bRetVal
End Function
Private Sub RetrieveCommandParameters(ByVal SQLCommand As SqlClient.SqlCommand)
Dim lstParameterInfo As List(Of SQLStoredProcedureParameterInfo)
With SQLCommand
If m_objCommandParameterInfo.Contains(.CommandText) Then
'Get cached parameter info
lstParameterInfo = m_objCommandParameterInfo(.CommandText)
'Loop through and add a new parameter
For Each objParameterInfo As SQLStoredProcedureParameterInfo In lstParameterInfo
.Parameters.Add(objParameterInfo.CreateParameter())
Next
Else
'Retrieve parameters from database server
SqlClient.SqlCommandBuilder.DeriveParameters(SQLCommand)
'Build parameter info list
lstParameterInfo = New List(Of SQLStoredProcedureParameterInfo)
For Each objParameter As SqlClient.SqlParameter In .Parameters
lstParameterInfo.Add(New SQLStoredProcedureParameterInfo(objParameter))
Next
'...and add into cache for next time
m_objCommandParameterInfo.Add(.CommandText, lstParameterInfo)
End If
End With
End Sub
' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
m_objTransaction = Nothing
If m_objConnection IsNot Nothing Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If
m_objConnection = Nothing
End If
End If
' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
#Region " IDisposable Support "
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class
'Class 2
Public Class SQLStoredProcedureParameterInfo
Public Name As String
Public Type As System.Data.SqlDbType
Public Direction As System.Data.ParameterDirection
Public DefaultValue As Object
Public Size As Integer
Public Sub New(ByVal Parameter As SqlClient.SqlParameter)
With Parameter
Name = .ParameterName
Type = .SqlDbType
Direction = .Direction
DefaultValue = .Value
Size = .Size
End With
End Sub
Public Function CreateParameter() As SqlClient.SqlParameter
Dim objRetVal As New SqlClient.SqlParameter
With objRetVal
.ParameterName = Name
.SqlDbType = Type
.Direction = Direction
.Value = DefaultValue
.Size = Size
End With
Return objRetVal
End Function
End Class
''''method to use this class as below:
Using objSqlConnection As SQLConnection = New SQLConnection(ConfigurationManager.AppSettings.Item("SQLConnString").ToString)
Dim strSQL As String
'for SQL
Dim BusinessRegionIntroducerGroupId As Integer
strSQL = "select * from table1 where employid=" & Session("empId")
With objSqlConnection.ConnectSQL(strSQL)
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
If objSQLdatareader.Read Then
empname= objSQLdatareader.Item("empname")
End If
objSQLdatareader.Close()
End With
'for SP
strSQL = "spSaveEmployee"
With objSqlConnection.ConnectSP(strSQL)
.Parameters("@DeptId").Value = DeptId
.Parameters("@empid").Value = Session("empid").ToString
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
objSQLdatareader.Close()
End With
End Using
Tuesday, 11 December 2007
Creating chart in Excel:
The class "cExcelChart" does everything, just use this class.
Using example:This will create graph and convert graph to image and return image path.
call this code in form:
Dim sGraphQuery As String = "SELECT Periodname,"
sGraphQuery = sGraphQuery & " Instructed,Completed,Cancelled "
'sGraphQuery = sGraphQuery & " Instructed + 8 as Instructed ,Completed + 8 as completed, Cancelled + 8 as cancelled "
sGraphQuery = sGraphQuery & " FROM vwCaseTypeInstructionsCompletionsCancellations"
sGraphQuery = sGraphQuery & " where casetypeid = 0 "
Dim arCols(3) As String
'"Period"
arCols(0) = "Period"
arCols(1) = "Cases Instructed"
arCols(2) = "Cases Completed"
arCols(3) = "Cases Cancellated"
Dim chartTitle As String = "" '"Sale Summary"
Dim xaxisTitle As String = "Period"
Dim yaxisTitle As String = "Instructions"
Dim ChartFullpath As String = System.IO.Path.Combine(chartOutputPath, "ICC_Sale.gif")
Dim sPath As String = generateGraph(sGraphQuery, ChartFullpath, chartTitle, xaxisTitle, yaxisTitle, arCols)
Public Function generateGraph(ByVal sQuery As String, Optional ByVal outputImagePath As String = "", Optional ByVal ChartTitle As String = "", Optional ByVal xAxisTitle As String = "", Optional ByVal yAxisTitle As String = "", Optional ByVal arrColumns() As String = Nothing) As String
Dim ds As DataSet = getDataset(sQuery)
Dim objChart As Object
If arrColumns.Length = 0 Then
objChart = New cExcelChart(ds)
Else
objChart = New cExcelChart(ds, arrColumns)
End If
'Dim objChart As New cExcelChart(ds)
'objChart.ColumnNames = arrColumns
objChart.ChartTitle = ChartTitle
objChart.xAxisTitle = xAxisTitle
objChart.yAxisTitle = yAxisTitle
objChart.AxisTitleFontColor = Color.Blue
objChart.chartOutputPath = outputImagePath
'settings
'ChartTitle
objChart.ChartTitleFontName = "Arial"
objChart.ChartTitleFontSize = 20
objChart.ChartTitleFontColor = Color.Blue
'axes title
objChart.AxisTitleFontColor = Color.DodgerBlue
objChart.AxisTitleFontName = "Arial"
objChart.AxisTitleFontSize = 8
'x and y and legand text colors
objChart.AxisTickLabelsFontColor = Color.DodgerBlue
Return objChart.GenerateChart
End Function
'getDataset
Public Function getDataset(ByVal sQuery As String) As DataSet
'Status Display
lblStatus.Text = "Status:" & vbCrLf & "1. Process started... "
lblStatus.Refresh()
Dim con As SqlConnection
con = New SqlConnection(My.Settings.SQLConnString)
Dim da As SqlDataAdapter = New SqlDataAdapter(sQuery, con)
da.SelectCommand.CommandType = CommandType.Text
Dim ds As DataSet = New DataSet("Report")
con.Open()
System.Threading.Thread.Sleep(1500)
da.Fill(ds)
Return ds
End Function
Imports System
Imports System.Data
Imports System.IO
Imports System.Runtime.InteropServices
Public Class cExcelChart
'constructers
#Region "Constructors"
Friend Sub New(ByVal ds As DataSet)
m_ds = ds
Dim iColumns As Integer
'default column names
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
ReDim Preserve m_ColumnNames(iColumns)
m_ColumnNames(iColumns) = m_ds.Tables(0).Columns(iColumns).ColumnName
Next
End Sub
Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String)
m_ColumnNames = ColumnNames
m_ds = ds
End Sub
'If File.Exists(Filename) Then
' File.Delete(Filename)
'End If
Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String, ByVal chartImageFullPath As String)
m_ColumnNames = ColumnNames
m_ds = ds
m_chartImageFullPath = chartImageFullPath
End Sub
#End Region
#Region "properties"
'properties
' Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
'Private m_Filename As String = "abc.xls"
Private m_ColumnNames() As String 'column names
Private m_ChartTitle As String = "" ' Chart title
Private m_xAxisTitle As String = "" 'x axis title
Private m_yAxisTitle As String = "" 'y axis
Private m_FileFullpath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "abc.xls")
Private m_ds As DataSet 'dataset
Private m_chartImageFullPath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Now.Millisecond & ".gif")
Private m_ChartTitleFontColor As Color = Color.Black
Private m_ChartTitleFontName As String = "Arial"
Private m_ChartTitleFontSize As Integer = 14
Private m_AxisTitleFontColor As Color = Color.Black
Private m_AxisTitleFontName As String = "Arial"
Private m_AxisTitleFontSize As Integer = 10
Private m_AxisTickLabelsFontColor As Color = Color.Black
Public Property ColumnNames() As Array
Get
Return m_ColumnNames
End Get
Set(ByVal value As Array)
m_ColumnNames = value
End Set
End Property
Public Property ChartTitle() As String
Get
Return m_ChartTitle
End Get
Set(ByVal value As String)
m_ChartTitle = value
End Set
End Property
Public Property ChartTitleFontColor() As Color
Get
Return m_ChartTitleFontColor
End Get
Set(ByVal value As Color)
m_ChartTitleFontColor = value
End Set
End Property
Public Property ChartTitleFontName() As String
Get
Return m_ChartTitleFontName
End Get
Set(ByVal value As String)
m_ChartTitleFontName = value
End Set
End Property
Public Property ChartTitleFontSize() As Integer
Get
Return m_ChartTitleFontSize
End Get
Set(ByVal value As Integer)
m_ChartTitleFontSize = value
End Set
End Property
Public Property AxisTitleFontColor() As Color
Get
Return m_AxisTitleFontColor
End Get
Set(ByVal value As Color)
m_AxisTitleFontColor = value
End Set
End Property
Public Property AxisTitleFontSize() As Integer
Get
Return m_AxisTitleFontSize
End Get
Set(ByVal value As Integer)
m_AxisTitleFontSize = value
End Set
End Property
Public Property AxisTitleFontName() As String
Get
Return m_AxisTitleFontName
End Get
Set(ByVal value As String)
m_AxisTitleFontName = value
End Set
End Property
Public Property AxisTickLabelsFontColor() As Color
Get
Return m_AxisTickLabelsFontColor
End Get
Set(ByVal value As Color)
m_AxisTickLabelsFontColor = value
End Set
End Property
Public Property xAxisTitle() As String
Get
Return m_xAxisTitle
End Get
Set(ByVal value As String)
m_xAxisTitle = value
End Set
End Property
'TickLabels
Public Property yAxisTitle() As String
Get
Return m_yAxisTitle
End Get
Set(ByVal value As String)
m_yAxisTitle = value
End Set
End Property
'excel filepath
Public Property chartOutputPath() As String
Get
Return m_chartImageFullPath
End Get
Set(ByVal value As String)
m_chartImageFullPath = value
End Set
End Property
Public WriteOnly Property dsDataset() As DataSet
Set(ByVal value As DataSet)
m_ds = value
End Set
End Property
#End Region
'Function GenerateChart(ByVal arrColumns() As String, ByVal ds As DataSet) As String 'Excel.Chart
Function GenerateChart() As String 'Excel.Chart
'create new excel application
Dim oexcel As Excel.Application
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
oexcel = CreateObject("Excel.Application")
Try
'add a new workbook
obook = oexcel.Workbooks.Add
'check total sheets in workboob
Dim S As Integer = oexcel.Application.Sheets.Count()
'leaving first sheet delete all the remaining sheets
If S > 1 Then
oexcel.Application.DisplayAlerts = False
Dim J As Integer = S
Do While J > 1
oexcel.Application.Sheets(J).delete()
J = oexcel.Application.Sheets.Count()
Loop
End If
osheet = oexcel.Worksheets(1)
oexcel.Visible = True
'column heading
Dim iColumns As Integer
For iColumns = 0 To m_ColumnNames.Length - 1
osheet.Range(getChar(iColumns + 1) & "3").Value = m_ColumnNames(iColumns)
Next
'start of row
Dim R As Integer = 3
Dim intI As Integer
For intI = 0 To m_ds.Tables(0).Rows.Count - 1
R = R + 1
'For iColumns = 0 To m_ColumnNames.Length - 1
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
osheet.Range(getChar(iColumns + 1) & 4 + intI).Value = m_ds.Tables(0).Rows(intI).Item(iColumns)
Next
Next
'create chart objects
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = osheet.ChartObjects
'set chart location
MyCharts1 = MyCharts.Add(150, 130, 430, 250)
oChart = MyCharts1.Chart
With oChart
'set data range for chart
Dim chartRange As Excel.Range
chartRange = osheet.Range("A3", getChar(m_ds.Tables(0).Columns.Count) & R)
.SetSourceData(chartRange)
'set how you want to draw chart i.e column wise or row wise
.PlotBy = Excel.XlRowCol.xlColumns
.HasTitle = True
'.ChartTitle.Font.Bold = True
'.ChartTitle.Caption = "mix"
.ChartTitle.Font.Size = ChartTitleFontSize
.ChartTitle.Font.Name = ChartTitleFontName
.ChartTitle.Font.Color = ColorTranslator.ToOle(ChartTitleFontColor)
.ChartTitle.Text = ChartTitle
'select chart type
.ChartType = Excel.XlChartType.xlLineMarkers
'set data lables for bars
'.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabelAndPercent)
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
'set legend to be displayed or not
.HasLegend = True
'
''remove border
.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone
'set legend location
.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
''format legend keys
Dim le As Excel.LegendEntry
Dim lk As Excel.LegendKey
For Each le In .Legend.LegendEntries
lk = le.LegendKey
lk.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone
le.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor) 'RGB(1, 2, 3)
lk.Border.Weight = Excel.XlBorderWeight.xlThin
'le.LegendKey.Border.LineStyle = Excel.XlLineStyle.xlContinuous
Next
'Dim i As Integer = 1
'For i = 1 To .SeriesCollection.count()
' .SeriesCollection(i).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)
'Next
'series colors
'.SeriesCollection(1).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)
'display title for X axis and Y axis
Dim xlAxisCategory, xlAxisValue As Excel.Axes
xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Name = AxisTitleFontName
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = xAxisTitle
xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Name = AxisTitleFontName
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = yAxisTitle
'to start x axes from zero
xlAxisValue.Item(Excel.XlAxisType.xlCategory).AxisBetweenCategories = False
'color of x and y axis labels
xlAxisValue.Item(Excel.XlAxisType.xlCategory).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
'x-axis grid lines
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Weight = Excel.XlBorderWeight.xlHairline
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Select()
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(Color.Transparent)
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone
'xlAxisValue.Item(Excel.XlAxisType.xlValue).MinimumScale = 0
'Yaxis grid lines
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = RGB(0, 255, 0)
End With
'oChart.Export("C:\download\GraphSheetControl\" & Now.Millisecond & ".gif", "GIF")
oChart.Export(m_chartImageFullPath, System.IO.Path.GetExtension(m_chartImageFullPath).Replace(".", ""))
osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
Catch ex As Exception
Throw ex
Finally
oexcel.Quit()
oexcel = Nothing
End Try
Return m_chartImageFullPath
End Function
Function getChar(ByVal iNum As Integer) As Char
Select Case iNum
Case 1
Return "A"
Case 2
Return "B"
Case 3
Return "C"
Case 4
Return "D"
Case 5
Return "E"
Case 6
Return "F"
Case 7
Return "G"
Case 8
Return "H"
Case 9
Return "I"
Case 10
Return "J"
Case 11
Return "K"
Case 12
Return "L"
Case 13
Return "M"
Case 14
Return "N"
Case 15
Return "O"
Case 16
Return "P"
Case 17
Return "Q"
Case 18
Return "R"
Case 19
Return "S"
Case 20
Return "T"
Case 21
Return "U"
Case 22
Return "V"
Case 23
Return "W"
Case 24
Return "X"
Case 25
Return "Y"
Case 26
Return "Z"
End Select
End Function
End Class
Using example:This will create graph and convert graph to image and return image path.
call this code in form:
Dim sGraphQuery As String = "SELECT Periodname,"
sGraphQuery = sGraphQuery & " Instructed,Completed,Cancelled "
'sGraphQuery = sGraphQuery & " Instructed + 8 as Instructed ,Completed + 8 as completed, Cancelled + 8 as cancelled "
sGraphQuery = sGraphQuery & " FROM vwCaseTypeInstructionsCompletionsCancellations"
sGraphQuery = sGraphQuery & " where casetypeid = 0 "
Dim arCols(3) As String
'"Period"
arCols(0) = "Period"
arCols(1) = "Cases Instructed"
arCols(2) = "Cases Completed"
arCols(3) = "Cases Cancellated"
Dim chartTitle As String = "" '"Sale Summary"
Dim xaxisTitle As String = "Period"
Dim yaxisTitle As String = "Instructions"
Dim ChartFullpath As String = System.IO.Path.Combine(chartOutputPath, "ICC_Sale.gif")
Dim sPath As String = generateGraph(sGraphQuery, ChartFullpath, chartTitle, xaxisTitle, yaxisTitle, arCols)
Public Function generateGraph(ByVal sQuery As String, Optional ByVal outputImagePath As String = "", Optional ByVal ChartTitle As String = "", Optional ByVal xAxisTitle As String = "", Optional ByVal yAxisTitle As String = "", Optional ByVal arrColumns() As String = Nothing) As String
Dim ds As DataSet = getDataset(sQuery)
Dim objChart As Object
If arrColumns.Length = 0 Then
objChart = New cExcelChart(ds)
Else
objChart = New cExcelChart(ds, arrColumns)
End If
'Dim objChart As New cExcelChart(ds)
'objChart.ColumnNames = arrColumns
objChart.ChartTitle = ChartTitle
objChart.xAxisTitle = xAxisTitle
objChart.yAxisTitle = yAxisTitle
objChart.AxisTitleFontColor = Color.Blue
objChart.chartOutputPath = outputImagePath
'settings
'ChartTitle
objChart.ChartTitleFontName = "Arial"
objChart.ChartTitleFontSize = 20
objChart.ChartTitleFontColor = Color.Blue
'axes title
objChart.AxisTitleFontColor = Color.DodgerBlue
objChart.AxisTitleFontName = "Arial"
objChart.AxisTitleFontSize = 8
'x and y and legand text colors
objChart.AxisTickLabelsFontColor = Color.DodgerBlue
Return objChart.GenerateChart
End Function
'getDataset
Public Function getDataset(ByVal sQuery As String) As DataSet
'Status Display
lblStatus.Text = "Status:" & vbCrLf & "1. Process started... "
lblStatus.Refresh()
Dim con As SqlConnection
con = New SqlConnection(My.Settings.SQLConnString)
Dim da As SqlDataAdapter = New SqlDataAdapter(sQuery, con)
da.SelectCommand.CommandType = CommandType.Text
Dim ds As DataSet = New DataSet("Report")
con.Open()
System.Threading.Thread.Sleep(1500)
da.Fill(ds)
Return ds
End Function
Imports System
Imports System.Data
Imports System.IO
Imports System.Runtime.InteropServices
Public Class cExcelChart
'constructers
#Region "Constructors"
Friend Sub New(ByVal ds As DataSet)
m_ds = ds
Dim iColumns As Integer
'default column names
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
ReDim Preserve m_ColumnNames(iColumns)
m_ColumnNames(iColumns) = m_ds.Tables(0).Columns(iColumns).ColumnName
Next
End Sub
Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String)
m_ColumnNames = ColumnNames
m_ds = ds
End Sub
'If File.Exists(Filename) Then
' File.Delete(Filename)
'End If
Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String, ByVal chartImageFullPath As String)
m_ColumnNames = ColumnNames
m_ds = ds
m_chartImageFullPath = chartImageFullPath
End Sub
#End Region
#Region "properties"
'properties
' Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
'Private m_Filename As String = "abc.xls"
Private m_ColumnNames() As String 'column names
Private m_ChartTitle As String = "" ' Chart title
Private m_xAxisTitle As String = "" 'x axis title
Private m_yAxisTitle As String = "" 'y axis
Private m_FileFullpath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "abc.xls")
Private m_ds As DataSet 'dataset
Private m_chartImageFullPath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Now.Millisecond & ".gif")
Private m_ChartTitleFontColor As Color = Color.Black
Private m_ChartTitleFontName As String = "Arial"
Private m_ChartTitleFontSize As Integer = 14
Private m_AxisTitleFontColor As Color = Color.Black
Private m_AxisTitleFontName As String = "Arial"
Private m_AxisTitleFontSize As Integer = 10
Private m_AxisTickLabelsFontColor As Color = Color.Black
Public Property ColumnNames() As Array
Get
Return m_ColumnNames
End Get
Set(ByVal value As Array)
m_ColumnNames = value
End Set
End Property
Public Property ChartTitle() As String
Get
Return m_ChartTitle
End Get
Set(ByVal value As String)
m_ChartTitle = value
End Set
End Property
Public Property ChartTitleFontColor() As Color
Get
Return m_ChartTitleFontColor
End Get
Set(ByVal value As Color)
m_ChartTitleFontColor = value
End Set
End Property
Public Property ChartTitleFontName() As String
Get
Return m_ChartTitleFontName
End Get
Set(ByVal value As String)
m_ChartTitleFontName = value
End Set
End Property
Public Property ChartTitleFontSize() As Integer
Get
Return m_ChartTitleFontSize
End Get
Set(ByVal value As Integer)
m_ChartTitleFontSize = value
End Set
End Property
Public Property AxisTitleFontColor() As Color
Get
Return m_AxisTitleFontColor
End Get
Set(ByVal value As Color)
m_AxisTitleFontColor = value
End Set
End Property
Public Property AxisTitleFontSize() As Integer
Get
Return m_AxisTitleFontSize
End Get
Set(ByVal value As Integer)
m_AxisTitleFontSize = value
End Set
End Property
Public Property AxisTitleFontName() As String
Get
Return m_AxisTitleFontName
End Get
Set(ByVal value As String)
m_AxisTitleFontName = value
End Set
End Property
Public Property AxisTickLabelsFontColor() As Color
Get
Return m_AxisTickLabelsFontColor
End Get
Set(ByVal value As Color)
m_AxisTickLabelsFontColor = value
End Set
End Property
Public Property xAxisTitle() As String
Get
Return m_xAxisTitle
End Get
Set(ByVal value As String)
m_xAxisTitle = value
End Set
End Property
'TickLabels
Public Property yAxisTitle() As String
Get
Return m_yAxisTitle
End Get
Set(ByVal value As String)
m_yAxisTitle = value
End Set
End Property
'excel filepath
Public Property chartOutputPath() As String
Get
Return m_chartImageFullPath
End Get
Set(ByVal value As String)
m_chartImageFullPath = value
End Set
End Property
Public WriteOnly Property dsDataset() As DataSet
Set(ByVal value As DataSet)
m_ds = value
End Set
End Property
#End Region
'Function GenerateChart(ByVal arrColumns() As String, ByVal ds As DataSet) As String 'Excel.Chart
Function GenerateChart() As String 'Excel.Chart
'create new excel application
Dim oexcel As Excel.Application
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
oexcel = CreateObject("Excel.Application")
Try
'add a new workbook
obook = oexcel.Workbooks.Add
'check total sheets in workboob
Dim S As Integer = oexcel.Application.Sheets.Count()
'leaving first sheet delete all the remaining sheets
If S > 1 Then
oexcel.Application.DisplayAlerts = False
Dim J As Integer = S
Do While J > 1
oexcel.Application.Sheets(J).delete()
J = oexcel.Application.Sheets.Count()
Loop
End If
osheet = oexcel.Worksheets(1)
oexcel.Visible = True
'column heading
Dim iColumns As Integer
For iColumns = 0 To m_ColumnNames.Length - 1
osheet.Range(getChar(iColumns + 1) & "3").Value = m_ColumnNames(iColumns)
Next
'start of row
Dim R As Integer = 3
Dim intI As Integer
For intI = 0 To m_ds.Tables(0).Rows.Count - 1
R = R + 1
'For iColumns = 0 To m_ColumnNames.Length - 1
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
osheet.Range(getChar(iColumns + 1) & 4 + intI).Value = m_ds.Tables(0).Rows(intI).Item(iColumns)
Next
Next
'create chart objects
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = osheet.ChartObjects
'set chart location
MyCharts1 = MyCharts.Add(150, 130, 430, 250)
oChart = MyCharts1.Chart
With oChart
'set data range for chart
Dim chartRange As Excel.Range
chartRange = osheet.Range("A3", getChar(m_ds.Tables(0).Columns.Count) & R)
.SetSourceData(chartRange)
'set how you want to draw chart i.e column wise or row wise
.PlotBy = Excel.XlRowCol.xlColumns
.HasTitle = True
'.ChartTitle.Font.Bold = True
'.ChartTitle.Caption = "mix"
.ChartTitle.Font.Size = ChartTitleFontSize
.ChartTitle.Font.Name = ChartTitleFontName
.ChartTitle.Font.Color = ColorTranslator.ToOle(ChartTitleFontColor)
.ChartTitle.Text = ChartTitle
'select chart type
.ChartType = Excel.XlChartType.xlLineMarkers
'set data lables for bars
'.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabelAndPercent)
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
'set legend to be displayed or not
.HasLegend = True
'
''remove border
.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone
'set legend location
.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
''format legend keys
Dim le As Excel.LegendEntry
Dim lk As Excel.LegendKey
For Each le In .Legend.LegendEntries
lk = le.LegendKey
lk.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone
le.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor) 'RGB(1, 2, 3)
lk.Border.Weight = Excel.XlBorderWeight.xlThin
'le.LegendKey.Border.LineStyle = Excel.XlLineStyle.xlContinuous
Next
'Dim i As Integer = 1
'For i = 1 To .SeriesCollection.count()
' .SeriesCollection(i).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)
'Next
'series colors
'.SeriesCollection(1).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)
'display title for X axis and Y axis
Dim xlAxisCategory, xlAxisValue As Excel.Axes
xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Name = AxisTitleFontName
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = xAxisTitle
xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Name = AxisTitleFontName
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = yAxisTitle
'to start x axes from zero
xlAxisValue.Item(Excel.XlAxisType.xlCategory).AxisBetweenCategories = False
'color of x and y axis labels
xlAxisValue.Item(Excel.XlAxisType.xlCategory).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
'x-axis grid lines
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Weight = Excel.XlBorderWeight.xlHairline
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Select()
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(Color.Transparent)
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone
'xlAxisValue.Item(Excel.XlAxisType.xlValue).MinimumScale = 0
'Yaxis grid lines
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = RGB(0, 255, 0)
End With
'oChart.Export("C:\download\GraphSheetControl\" & Now.Millisecond & ".gif", "GIF")
oChart.Export(m_chartImageFullPath, System.IO.Path.GetExtension(m_chartImageFullPath).Replace(".", ""))
osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
Catch ex As Exception
Throw ex
Finally
oexcel.Quit()
oexcel = Nothing
End Try
Return m_chartImageFullPath
End Function
Function getChar(ByVal iNum As Integer) As Char
Select Case iNum
Case 1
Return "A"
Case 2
Return "B"
Case 3
Return "C"
Case 4
Return "D"
Case 5
Return "E"
Case 6
Return "F"
Case 7
Return "G"
Case 8
Return "H"
Case 9
Return "I"
Case 10
Return "J"
Case 11
Return "K"
Case 12
Return "L"
Case 13
Return "M"
Case 14
Return "N"
Case 15
Return "O"
Case 16
Return "P"
Case 17
Return "Q"
Case 18
Return "R"
Case 19
Return "S"
Case 20
Return "T"
Case 21
Return "U"
Case 22
Return "V"
Case 23
Return "W"
Case 24
Return "X"
Case 25
Return "Y"
Case 26
Return "Z"
End Select
End Function
End Class
solution for combo box and listbox value and text
define a class for value and text strings and use them to get the information
example as below:
Dim sValue As String
Dim sText As String
Dim mli As MyListBoxItem = CType(cboMonth.SelectedItem, MyListBoxItem)
sText=mli.ToString 'or sText=cboMonth.Text
svalue=mli.Value.ToString()
If cboMonth.Text = "" Then
MsgBox("please select a month")
Exit Sub
Else
sMonth = mli.ToString
End If
Class MyListBoxItem
Protected m_text As String
Protected m_value As Integer
Public Sub New(ByVal Text As String)
m_text = Text
m_value = 0
End Sub
Public Sub New(ByVal Text As String, ByVal Value As Integer)
m_text = Text
m_value = Value
End Sub
Public Property Value() As Integer
Get
Return m_value
End Get
Set(ByVal Value As Integer)
m_value = Value
End Set
End Property
Public Overrides Function ToString() As String
Return m_text
End Function
End Class
example as below:
Dim sValue As String
Dim sText As String
Dim mli As MyListBoxItem = CType(cboMonth.SelectedItem, MyListBoxItem)
sText=mli.ToString 'or sText=cboMonth.Text
svalue=mli.Value.ToString()
If cboMonth.Text = "" Then
MsgBox("please select a month")
Exit Sub
Else
sMonth = mli.ToString
End If
Class MyListBoxItem
Protected m_text As String
Protected m_value As Integer
Public Sub New(ByVal Text As String)
m_text = Text
m_value = 0
End Sub
Public Sub New(ByVal Text As String, ByVal Value As Integer)
m_text = Text
m_value = Value
End Sub
Public Property Value() As Integer
Get
Return m_value
End Get
Set(ByVal Value As Integer)
m_value = Value
End Set
End Property
Public Overrides Function ToString() As String
Return m_text
End Function
End Class
Thursday, 6 December 2007
Combine file path with out worring about '\' problems
System.IO.Path.Combine does the '\' checking for us, we don't need to worry about \\ problems which occur sometimes.
when building a path string use System.IO.Path.Combine
example:
System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "filename.gif")
will output: fullpath\filename.gif
when building a path string use System.IO.Path.Combine
example:
System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "filename.gif")
will output: fullpath\filename.gif
Subscribe to:
Posts (Atom)