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) 'if tab visible If isVisible Then .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 |
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. 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
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/
htm#i1029599
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".
|
| 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 |
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
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)
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));"
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) |
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. |
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
** Others PaperSize Options
|
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)
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 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/ |
