Thursday, December 31, 2009

How to check which version of the dotnet framework is installed

1) Go To Start and click on Run
2) Type --> "%windir%\Microsoft.NET\Framework\"
You will see all the version installed folder name (Ex: v1.0.3705, v1.1.4322, v2.0.50727, v3.0, v3.5)

Friday, November 13, 2009

How to Display/Hide Crystal Report Viewer Tab and Status Bar at run time using VB.NET

How to Display/Hide Crystal Report Viewer Tab and Status Bar at run time using VB.NET


I am working with Visual Studio 2005 and faced some issues while working with crystal report, my requirement is to display and hide tab and status bar control from crystal report viewer, event tab control should display customized name in the tab text. I found the way to do this, below the code in vb.net

Display/Hide crystal report viewer tab control and rename the tab control


''' <summary>

''' This method will make tab controls visibile True/False

''' </summary>

''' <remarks>Ritesh</remarks>

Private Sub CrystalReportViewerTab(ByVal rptViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer, ByVal isVisible As Boolean)

Dim reportTabs As TabControl = rptViewer.Controls(0).Controls(0)

'if tab visible

If isVisible Then
With reportTabs

.ItemSize = New Size(70, 18)

.SizeMode = TabSizeMode.FillToRight

.Appearance = TabAppearance.Normal

'Here you can give customize name to tab

.TabPages(0).Text = "My Tab Text"

End With

Else 'if tab hide

With reportTabs

.ItemSize = New Size(0, 1)

.SizeMode = TabSizeMode.Fixed

.Appearance = TabAppearance.Buttons

End With

End If

End Sub

Display/Hide crystal report viewer status bar


''' This method will make status bar visibile True/False

Public Sub CrystalReportViewerStatusBar(ByVal rptViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer, ByVal isVisible As Boolean)

Dim ctl As Control

For Each ctl In rptViewer.Controls

If ctl.GetType().Name.ToString() = "StatusBar" Then

ctl.Visible = isVisible

End If

Next

End Sub

Wednesday, November 11, 2009

Datagridview CellDoubleClick event not getting fires with Datagridview drag drop functionality in VB.NET Window form

Differentiate between DataGridView Mousedown and CellDoubleclick events

OR

Datagridview CellDoubleClick event not getting fires with Datagridview drag drop functionality in VB.NET Window form


Problem


In VB.NET with Visual Studio 2005 I face a problem with working Datagridview drag and drop functionality. When I write a code to implement drag and drop functionality in Datagridview MouseDown event

Ex: DataGridView1.DoDragDrop(Index, DragDropEffects.Move)


And trying to fire CellDoubleClick event for same datagrid then this event is not getting fire, problem is when you do double click then first MouseDown event will fire then CelldoubleClick, but if control goes to MouseDown event then this line of code "DataGridView1.DoDragDrop(Index, DragDropEffects.Move)" canel CellDoubleClick event and CellDoubleClick event not getting fire.


Solution


To work with Both events togather we have to differenciate this by "Clicks" property in MouseDown event

Ex:

Private Sub DataGridView1_MouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDown


' 'Clicks' property can be used to solve this issue

If e.Clicks = 1 Then

' Write Code for Single Click

Else

'Write code for Double Click

End If

End Sub


Source code


So finally cell double and drag drop code should be like below


Private Sub DataGridView1_MouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDown


'Get the Index of Row which is being Dragged

'We would use this Index on Drop to identify which Row was dragged and get the values from that row

Dim Index As Integer

If e.Button = Windows.Forms.MouseButtons.Left Then

Index = DataGridView1.HitTest(e.X, e.Y).RowIndex

If e.Clicks = 1 Then ' For Single Click

If Index > -1 Then

'Pass the Index as "Data" argument of the DoDragDrop 'Function,

'Instead of Index as "Data" we can pass some data like 'array, object or images,

'That can be collected DataGridView2_DragOve event like

'e.Data.GetData(GetType(System.String))

DataGridView1.DoDragDrop(Index, DragDropEffects.Move)

End If

End If

End If

End Sub


Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick

MessageBox.Show("DGV1_CellDoubleClick")

End Sub


Private Sub DataGridView2_DragOver(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DataGridView2.DragOver

e.Effect = DragDropEffects.Move

End Sub


Private Sub DataGridView2_DragDrop(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DataGridView2.DragDrop

'Write your code do operation in DataGridView2

End Sub

How to implements Drag Drop functionality with DataGridView : click here

Thursday, November 05, 2009

How to write SQL query to generate XML string, How to get XML as an output parameter from stored procedure

How to write SQL query to generate XML string, How to get XML as an output parameter from stored procedure

You can very well write SQL query to generate XML string or write stored procedure to give output as XML string

For Oracle Database
1) http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.
htm#i1029599

For Microsoft SQL Server Database
1) http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

Monday, November 02, 2009

Problem to display date in the MaskedTextBoxControl in dotnet

 

When you try to display date value to MaskedTextBox then it show different date in the field especially when you have single digit on the date. Even though MaskedTextBox control "Mask" property set it to "00/00/0000".

 

When you try to assign the date into this field like:

 

Problem

 

MaskTextBox.Text = "1/12/2009", it will display in the field like "11/22/009_"

 

Solution

 

To overcome this issue you have to send your date with proper format like below

 

MaskedTextBox.Text = Format(Convert.ToDateTime("1/12/2009 5:58:00 PM"), "MM/dd/yyyy")

OR

MaskedTextBox.Text = Format(Convert.ToDateTime(Date.Now), "MM/dd/yyyy")

 

Now it will display in the field as a correct date format "1/12/2009".

 


Tuesday, October 27, 2009

Parent node image getting selected when click on others child nodes in the TreeView

Parent node image getting selected when click on others child nodes in the TreeView
 
While working with tree view control having some images, I faced following issue.
My treeview control has some text value and image in the parent nodes and corresponding child nodes. I bind treeview control with imagelist control to display image in the treeview. Image got binded properly and working fine, BUT when I clicks on the child nodes or some other nodes, very first parent node image get replaced with existing node image.
Solution: while binding the image into treeview nodes use "SelectedImageIndex" property along with "ImageIndex" property with same image index number.
 
Example:
parentNode.ImageIndex = 0
parentNode.SelectedImageIndex = 0
 
VB.NET Source Code
 
Below the vb.net source code to bind image with the treeview controls
 
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
'Load the Imagelist with desired images
Dim customImageList As New ImageList
customImageList.Images.Add("AMandatory", Bitmap.FromFile("C:\AMandatory.ico"))
customImageList.Images.Add("BDiscretionary", Bitmap.FromFile("C:\BDiscretionary.ico"))
customImageList.Images.Add("AAMandatory", Bitmap.FromFile("C:\AAMandatory.ico"))
customImageList.Images.Add("BBDiscretionary", Bitmap.FromFile("C:\BBDiscretionary.ico"))
 
'Bind ImageList with TreeView
TreeView1.ImageList = customImageList
 
'Create a ParentNode
Dim rootNodeMandatory As TreeNode = TreeView1.Nodes.Add("Mandatory Text")
'Bind first image from imagelist
rootNodeMandatory.ImageIndex = 0
rootNodeMandatory.SelectedImageIndex = 0
 
'Create the child Node
Dim childNodeMandatory As TreeNode
childNodeMandatory = rootNodeMandatory.Nodes.Add("Mandatory Child Text")
'Bind third image from imagelist
childNodeMandatory.ImageIndex = 2
childNodeMandatory.SelectedImageIndex = 2
 
'Create another ParentNode
Dim rootNodeDiscretionary As TreeNode = TreeView1.Nodes.Add("Discretionary Text")
'bind second image from imagelist
rootNodeDiscretionary.ImageIndex = 1
rootNodeDiscretionary.SelectedImageIndex = 1
 
'Create the child Node
Dim childNodeDiscretionary As TreeNode
childNodeDiscretionary = rootNodeDiscretionary.Nodes.Add("Discretionary Child Text")
'Bind fourth image from imagelist
childNodeDiscretionary.ImageIndex = 3
childNodeDiscretionary.SelectedImageIndex = 3
 
End Sub

Thursday, October 15, 2009

Error: $Oracle.EXCEPTION_ORA_6532: or ORA-06532: Subscript outside of limit

Error: $Oracle.EXCEPTION_ORA_6532: or ORA-06532: Subscript outside of limit

This error occurs when I try to fill data into "String_Table" (table of varchar) with extend. In details; I was getting this error when I tried to fill value into "codeTable" (variable of "String_Table") event I am doing extend immediately.

i := 0;

codeTable (i) := "Some Value"

codeTable.extend;

Above code will throw "ORA-06532: Subscript outside of limit"

Solution is "String_Table" need to be start filling from 1 not 0, so Correct code is

i := 1;

codeTable (i) := "Some Value"

codeTable.extend;

After giving couple of hours I found this and solved by using above solution (By changing "i" value 0 to 1)

Following are the example I have tested with Oracle 11g or Oracle 10g too. These are very important and very useful stored procedure example described How to use XML type and String Array in Oracle stored procedure.

OBJECT-1 (STRING_TABLE)

Create a global package which includes Ref Cursor and String_Table to store value to create IN Clause value or others

CREATE OR REPLACE PACKAGE GLOBAL_PACKAGE AS

TYPE REF_CUR IS REF CURSOR;

TYPE "STRING_TABLE" AS TABLE OF varchar2 (100) INDEX BY BINARY_INTEGER;

END GLOBAL_PACKAGE;

OBJECT-3 (STORED PROCEDURE-1)

Below stored procedure is an example to take a value (String_Array) as an Input parameter to create In Clause value for select query and a Output parameter (REF_CUR) that will return record set (No of rows from employee table) of employee data.

PROCEDURE SELECT_DATA_FROM_EMP

(

prmIds IN Global_Package.STRING_TABLE,

prmOutCursor OUT Global_Package.REF_CUR

)

IS

codeTable string_table := string_table(NULL);

i Number;

BEGIN

FOR i IN 1..prmIds.count LOOP --Never start loop with 0

codeTable (i) := prmIds(i);

codeTable.extend;

END LOOP;

Open prmOutCursor for

SELECT *

FROM employee

WHERE empid IN (select column_value from table(codeTable));

END SELECT_DATA_FROM_EMP;

OBJECT-4 (STORED PROCEDURE-2)

Sample xml to send from code for stored procedure, this XML format you can change as per your requirement but you have to change the XML node path in the stored procedure ("XMLTYPE.EXTRACT")

<ArrayList>

<Values><Value>1000</Value></Values>

<Values><Value>2000</Value></Values>

<Values><Value>3000</Value></Values>

<Values><Value>4000</Value></Values>

</ArrayList>

Below stored procedure is an example to take a value (XMLTYPE) as an Input parameter to create In Clause value for select query and a Output parameter (REF_CUR) that will return record set (No of rows from employee table) of employee data.

CREATE OR REPLACE PROCEDURE SELECT_DATA_FROM_EMP

(

prmXmlCode IN XMLTYPE,

prmOutCursor OUT Global_Package.REF_CUR

)

AS

codeTable string_table := string_table(NULL);

i Number := 1; --Never initialized with 0

BEGIN

FOR vRow IN

(

SELECT

XMLTYPE.EXTRACT (VALUE (Code), '/Value/text()').getstringval() AS iCode

FROM TABLE

(XMLSEQUENCE (prmXmlCode.EXTRACT ('ArrayList/Values/Value'))) Code)

LOOP

codeTable (i) := vRow. iCode;

i := i + 1;

codeTable.extend;

END LOOP;

OPEN prmOutCursor FOR

SELECT * FROM EMPLOYEE

WHERE EMPID IN (select column_value from table(codeTable));

END SELECT_DATA_FROM_EMP;

Note: If you have more than one select query in a single stored procedure and you are sending XMLTYPE as an input parameter, instead of writing XMLTYPE.EXTRACT in each SQL select statement, it is better to do XMLTYPE.EXTRACT once and keep the value into "String_Table" and use "String_Table" for all your SQL select statement like "select column_value from table(codeTable));"

Wednesday, September 16, 2009

Horizontal scroll bar NOT appears in DataGridView control in Window application

Horizontal scroll bar NOT appears in DataGridView control in Window application


I am using Visual Studio.Net 2005 and working with VB.NET Window application, in one of the window form when I tried to set DataGridView ScrollBars property as ScrollBars.Both then only Vertical scroll bar display in the grid But Horizontal scroll bar not appears in the grid.


After doing some analysis I found if DataGridView AutoSizeColumnsMode property is set as DataGridViewAutoSizeColumnsMode.Fill then you will not able to see the horizoltal scroll bar in the grid. SO to see both the scroll bar in the grid make sure you set


DataGridView1.AutoSizeColumnsMode = NONE (Default) And

DataGridView1.ScrollBars = ScrollBars.Both (Default)

Friday, September 11, 2009

Sort Datagridview with multiple columns using BindingSource in VB.NET

Sort Datagridview with multiple columns using BindingSource in VB.NET

 

When you have to sort datagridview with multiple columns then follow the code below. This code is tested with Visual Studio 2005 and VB.net window application.

Here I am making sorting with two columns, first column is dynamic (as user clicked) and second column is fixed (default). You can extend this as per your requirement.

Basically this code will help you to understand how you can do sorting in datagridview with multiple columns.

 

'Declare one shared/Static variable to take care of persisting the state of sort direction

Shared sortDirection As String = " ASC "

 

**dtDataTable is the table which used to bind the DataGridView1

 

Private Sub DataGridView1_ColumnHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick

 

  Dim dataGridBindingSource As New BindingSource

  dataGridBindingSource.DataSource = dtDataTable

 

  'This is (ID ASC) first column I made it fixed

  Dim defaultColumn As String = "ID ASC"

  Dim sortColumnOrder As New StringBuilder

  Const ASC As String = " ASC "

  Const DESC As String = " DESC "

 

  'This code will sort the grid with secondary column as Option

  If sortDirection = ASC Then

    sortDirection = DESC

  ElseIf sortDirection = DESC Then

    sortDirection = ASC

  End If

 

  'Collect Column name and built Sort string

  sortColumnOrder.Append(DataGridView1.Columns(e.ColumnIndex).Name)

  sortColumnOrder.Append(sortDirection)

  sortColumnOrder.Append(GlobalConstants.COMMA)

  sortColumnOrder.Append(DefaultColumn)

 

  'Sort the Binding Source

  dataGridBindingSource.Sort = sortColumnOrder.ToString()

 

  'Finally Bind the DataGrid with sorted data table source

  Me.DataGridView1.DataSource = dataGridBindingSource

 

 End Sub

 

When user click on the datagridview header, any column to sort then records would be sorted based on the sortColumnOrder string created. For example if user clicks on the second column header to sort then sortColumnOrder string would be

"Second Column ASC/DESC, First Column ASC/DESC"

If user clicks on the fourth column to sort then sortColumnOrder string would be

"Fourth Column ASC/DESC, First Column ASC/DESC" etc

You can make this code to create sort string as per user requirement like

"1st column ASC/DESC, 2nd column ASC/DESC, 3rd column ASC/DESC" etc. 


Monday, August 31, 2009

How programmatically set Custom Page Size while exporting Crystal Report using VB.NET

How programmatically set Custom Page Size while exporting Crystal Report using VB.NET


You can easily set paper size with export functionality. Simply design crystal report as you do and bind it with database, dataset or any other data source.


When you export crystal report its takes paper size based on the crystal report design page size, if your requirement to display or print exported file in particular format then following code will help to do this.


Following example tested in


1) .NET Framework 2.0 and Visual Studio 2005

2) Crystal Report with Visual Studio 2005


DLL references requires


VB.NET Code


'Fill dataset as with desired data needs to populates crystal report
Dim dsReportOut As New DataSet
Dim rptDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim reportPath As String = "CrystalReportFilePath"
Dim fileName As String = "ExportFileName.doc"
'Build the report logic here. Declare a report document.
rptDoc = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
rptDoc.Load(String.Concat(reportPath, "CrystalReportFileName.rpt"))
rptDoc.SetDataSource(dsReportOut)
'Set Paper Size as A4 its letter size
rptDoc.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter
--**(See below for other PaperSize options)
'Export crystal report into MSWord
rptDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.WordForWindows, String.Concat(reportPath, fileName))
--***(See below for other Export Format Options)
'Open(Exported file)MSWord File
Dim ProcessStartInfo As New System.Diagnostics.ProcessStartInfo
ProcessStartInfo.FileName = String.Concat(reportPath, fileName)
ProcessStartInfo.WindowStyle = ProcessWindowStyle.Maximized
System.Diagnostics.Process.Start(ProcessStartInfo)

** Others PaperSize Options


*** Others Export Options


Wednesday, July 01, 2009

Fastest way to insert bulk records into SQL Server DB using bulkCopy in VB.NET

Fastest way to insert bulk records into SQL Server DB using SqlBulkCopy in VB.NET


There are multiple ways to insert bulk data into SQL server database table to increase better

performance and reduce multiple database hits.

Following example tested in

1) .NET Framework 2.0 and Visual Studio 2005

2) SQL Server 2005 and SQL Express

3) Microsoft Enterprises Library 3.0 for database operation


1. Passing XML string to Stored Procedure


In this method you can combine your data into XML and pass this XML as a string to

SQL server stored procedure, this way you can increase your database hit performance up to

70% instead of using Line By Line insert methods.


Example: I am giving example for two records you can increase records in XML (no limit)


Sample XML String


<Employees>

<Emp>

<Empno>1000</Empno>

<Ename>Ritesh</Ename>

<Deptno>A91</Deptno>

</Emp>

<Emp>

<Empno>2000</Empno>

<Ename>Rajiv</Ename>

<Deptno>B56</Deptno>

</Emp>

</Employees>


SQL Server Database table "Employee"


CREATE TABLE [dbo].[Employee](

[Empno] [int] NOT NULL,

[Ename] [nvarchar](100) NULL,

[Deptno] [nvarchar](100) NULL

) ON [PRIMARY]

END

GO


SQL Server Stored Procedure


ALTER PROCEDURE ADDXML_EMP

(

@prmEmployee XML,

@prmOutStatus INT OUT

)

AS

DECLARE @XMLDocPointer INT

EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @prmEmployee


BEGIN


SET NOCOUNT ON

INSERT INTO Employee

(

[Empno],

[Ename],

[Deptno]

)

SELECT

Empno,

Ename,

Deptno

FROM

OPENXML(@XMLDocPointer,'/Employees/Emp',2)

WITH (

Empno integer,

Ename nvarchar(100),

Deptno nvarchar(100)

)

SET @prmOutStatus = @@Error

RETURN @@Error

END


VB.NET Code to execute stored procedure


Namespace


Imports Microsoft.Practices.EnterpriseLibrary.Data

Imports System.Data.SqlClient

Imports System.Xml

Imports System.Data.Common


Dim XMLString As String

'USE GetXML method for XMLString


XMLString = "<Employees><Emp><Empno>1000</Empno><Ename>Ritesh

</Ename><Deptno>A91</Deptno></Emp><Emp><Empno>2000

</Empno><Ename>Rajiv</Ename><Deptno>B56</Deptno></Emp>

</Employees>"


'CreateDatabase method will create database object based on the connection name

Dim connectDB As Database = ConfigDatabaseFactory.CreateDatabase("SQLServerDBConnectionString")

Dim command As DbCommand

command = connectDB.GetStoredProcCommand("AddXML_EMP")

connectDB.AddInParameter(command, "@prmEmployee", DbType.Xml, XMLString)

connectDB.AddOutParameter(command, "@prmOutStatus", DbType.Int32, 0)

connectDB.ExecuteNonQuery(command)


Note: If you are reading XML from file then constructed your string using following lines of code


Dim xmlFilePath As String = "C:\Employees.xml"

Dim xmlDoc As New XmlDocument

xmlDoc.Load(xmlFilePath)

Dim XMLString As String = xmlDoc.OuterXml.ToString()


Data Inserted into Database table


Empno Ename Deptno

1000 Ritesh A91

2000 Rajiv B56


Drawback


XML methods is very reliable and fast performing method but If you have any special character in your data like notes field in database table column then this method will give error saying "Invalid character in XML" or if you have Image in the column then XML would help, to over come this another method to use SqlBulkCopy


Note: If you want to do above method for Oracle Stored procedure the see below link.

http://riteshk.blogspot.com/2008/09/sending-xml-string-as-input-parameter.html


2. Using "SqlBulkCopy" method to insert data into Database table


'Fill this data from any database like Oracle or SQL Server

'using some query like "Select * from Employee"

Dim dsServerData As New DataSet


'Create Databse

Dim localConnectDB As Database = ConfigDatabaseFactory.CreateDatabase("DBConString")

'Open Connection

Using localConnection As DbConnection = localConnectDB.CreateConnection()

'Open Connection

localConnection.Open()

'Create Transaction if you want

Using transaction As DbTransaction = localConnection.BeginTransaction()

'Create SqlBulkCopy

Using localCopy As New SqlBulkCopy(localConnection, SqlBulkCopyOptions.TableLock, transaction)

Try

'loop if you have multiple table in a dataset

For tableCount As Integer = 0 To dsServerData.Tables.Count - 1

'Define the BatchSize , it can be configurable too

localCopy.BatchSize = dsServerData.Tables(tableCount).Rows.Count

'Define the table name where you want to insert data

'if you have exact table name in dataset then use

'localCopy.DestinationTableName = dsServerData.Tables(tableCount).TableName

localCopy.DestinationTableName = "Employee"

'This "WriteToServer" will insert your complete table data into database table localCopy.WriteToServer(dsServerData.Tables(tableCount))

Next

transaction.Commit()

Catch ex As Exception

transaction.Rollback()

Finally

'Close Connection and bulkCopy

localCopy.Close()

localConnection.Close()

End Try

End Using

End Using

End Using


Note: This method inserts 0.28 minutes to insert 1 million records in database

Some other methods to load huge TextFile into SQL server database table

http://blog.stevienova.com/2008/01/16/net-fastest-way-to-load-text-file-to-sql-sqlbulkcopy/