Create datatable dynamically or during runtime

Here is the code to create a datatable and add rows to it . This is the case when you want to add certain rows to the datatable which are manipulated in the code instead of bringing directly from the database.

Please find the code below to achieve this:


Public Class Form1 Inherits System.Windows.Forms.Form

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click
Dim Table1 As DataTable
Table1 = New DataTable("Customers")
'creating a table named Customers
Dim Row1, Row2, Row3 As DataRow
'declaring three rows for the table
Try
Dim Name As DataColumn = New DataColumn("Name")
'declaring a column named Name
Name.DataType = System.Type.GetType("System.String")
'setting the datatype for the column
Table1.Columns.Add(Name)
'adding the column to table
Dim Product As DataColumn = New DataColumn("Product")
Product.DataType = System.Type.GetType("System.String")
Table1.Columns.Add(Product)
Dim Location As DataColumn = New DataColumn("Location")
Location.DataType = System.Type.GetType("System.String")
Table1.Columns.Add(Location)

Row1 = Table1.NewRow()
'declaring a new row
Row1.Item("Name") = "Reddy"
'filling the row with values. Item property is used to set the field value.
Row1.Item("Product") = "Notebook"
'filling the row with values. adding a product
Row1.Item("Location") = "Sydney"
'filling the row with values. adding a location
Table1.Rows.Add(Row1)
'adding the completed row to the table
Row2 = Table1.NewRow()
Row2.Item("Name") = "Bella"
Row2.Item("Product") = "Desktop"
Row2.Item("Location") = "Adelaide"
Table1.Rows.Add(Row2)
Row3 = Table1.NewRow()
Row3.Item("Name") = "Adam"
Row3.Item("Product") = "PDA"
Row3.Item("Location") = "Brisbane"
Table1.Rows.Add(Row3)
Catch
End Try

Dim ds As New DataSet()
ds = New DataSet()
'creating a dataset
ds.Tables.Add(Table1)
'adding the table to dataset
DataGrid1.SetDataBinding(ds, "Customers")
'binding the table to datagrid
End Sub

End Class

Add Serial Number to Datagridview

This article explains how to add a Serial Number (Row Index) Column in DataGridView control without adding this column in Physical Database or in DataSet's DataTable.

Level: Beginner

Knowledge Required:
• DataGridView
• Data Binding

Description:
We use DataGridView control alot of times in Data Manipulation application. Sometimes we require to have a Serial Number (S.No.) Column in DataGridView Control in such a way that no matter how Sorting/Filtering is done, Serial Number should remain constant i.e. in a Sequence.

One way to accomplish this is to create a Column in DataSet's DataTable in which we can store the Serial Numbers, but this will make our job too complex if sorting/filtering is also done. Because we have to re-check the Serial Number column again and again each time the Sorting/Filtering is performed.

So the better way is to use the DataGridView control's Virtual Mode. Here are the steps that we will do,

1) Bind the DataGridView control to some BindingSource and setup its Columns
2) Add another column (Unbound Column) and make it the first column
3) Set its name = ColumnSNo
4) Set its ReadOnly = True
4) Set the DataGridView control's VirtualMode property to True
5) In CellValueNeeded event use the following code:


Private Sub DataGridView1_CellValueNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) Handles DataGridView1.CellValueNeeded
If e.RowIndex >= 0 AndAlso e.ColumnIndex = Me.ColumnSNo.Index Then
e.Value = e.RowIndex + 1
End If
End Sub

Note that if we don't set the VirtualMode Property to True then CellValueNeeded event wouldn't fire


Summary:
To display the Serial Numbers we have added an unbound column and set the Virtual Mode Property of DataGridView control to True. Then in CellValueNeeded Event we just return the Row Index whose value is required

Crystal Report Login Code

Dim cr As New crptCurrentEmployees
Dim crtableLogoninfos As New TableLogOnInfos
Dim crtableLogoninfo As New TableLogOnInfo
Dim crConnectionInfo As New ConnectionInfo
Dim CrTables As Tables
Dim CrTable As Table

'Get database connection info

With crConnectionInfo
.ServerName = My.Settings.ConnectionString.Split(";")(0).Split("=")(1)
.DatabaseName = My.Settings.ConnectionString.Split(";")(1).Split("=")(1)
.UserID = My.Settings.ConnectionString.Split(";")(3).Split("=")(1)
.Password = My.Settings.ConnectionString.Split(";")(4).Split("=")(1)
End With

'Set database connection for all tables in report

CrTables = cr.Database.Tables
For Each CrTable In CrTables
crtableLogoninfo = CrTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)
Next
' Loop through each section and find report objects

Dim crReportobjects As ReportObjects, crSubReportobject As SubreportObject, subReportDocument As ReportDocument
Dim crDatabase As Database
Dim crSections As Sections = cr.ReportDefinition.Sections
For Each crSection As Section In crSections
crReportobjects = crSection.ReportObjects
For Each crReportobject As ReportObject In crReportobjects
If crReportobject.Kind = ReportObjectKind.SubreportObject Then

' If a subreport is found cast as subreportobject
crSubReportobject = CType(crReportobject, SubreportObject)
' Open the sub report
subReportDocument = crSubReportobject.OpenSubreport(crSubReportobject.SubreportName)
crDatabase = subReportDocument.Database
CrTables = crDatabase.Tables
' Loop through each table in the sub report and set the connection info
For Each CrTable In CrTables
crtableLogoninfo = CrTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)
Next
End If
Next
Next

If Me.cboLocations.SelectedIndex < 0 Then
cr.RecordSelectionFormula = "{Employees.CustomerID} = {?clientid}"
Else
cr.RecordSelectionFormula = "{Employees.CustomerID} = {?clientid} AND {Employees.LocationID} = {?locationid}"
cr.SetParameterValue("locationid", cboLocations.SelectedValue)
End If
cr.SetParameterValue("clientid", cboCompanyName.SelectedValue)

With crptViewer
.ReportSource = cr
.Zoom(100)
.Visible = True
.Refresh()
End With