Visual Basic Dot Net – Practical Approach
· To start a application in a new thread
System.Windows.Forms.Application.Run(New FrmMainMDI)
· Database Handling
1) Opening a new connection
Try
Dim CnString As String
Dim ActiveDBConnection = New SqlClient.SqlConnection
CnString = “Put your connection string here”
ActiveDBConnection.ConnectionString = CnString
ActiveDBConnection.Open()
Catch e As SqlClient.SqlException
ActiveDBConnection = Nothing
End Try
2) Executing a non select query
Dim RowAffected As Integer
Dim TSQLString As String
Dim DBCommand As SqlClient.SqlCommand
DBCommand = New SqlClient.SqlCommand(TSQLString, _
ActiveDBConnection)
RowAffected = DBCommand.ExecuteNonQuery()
If RowAffected = 0 Then
Return False
End If
3) Executing a select query for DataSet
Dim DBCommand As SqlClient.SqlCommand = New _
SqlClient.SqlCommand(TSQLString)
Dim DBDataAd As SqlClient.SqlDataAdapter = New _
SqlClient.SqlDataAdapter
Dim TempDS As New DataSet
DBCommand.Connection = ActiveDBConnection
DBDataAd.SelectCommand = DBCommand
DBDataAd.Fill(TempDS)
Return TempDS
· Getting value of a particular field in a DataSet
DataSet.Tables(0).Rows(“Row Index”)(“Column Name/Index”)
· Getting value of a particular field in a DataRow
DataRow.Item(“Column Name/Index”)
· Function to check whether a MDI Child is active or not
Private Function CheckActiveMDIChild(ByVal MDIChildName As
String) As Boolean
Dim X As Short
X = 0
Do While X <= Me.MdiChildren.Length - 1
If Me.MdiChildren(X).Name = MDIChildName Then
Return True
Exit Do
End If
X = X + 1
Loop
Return False
End Function
· Creating a new form object and assigning it to MDI form
Dim TempFrmProduct As New FrmProduct
TempFrmProduct.MdiParent = Me
TempFrmProduct.Show()
‘ Or to show form as dialog (Does not work in case
of a MDI child)
TempFrmProduct.ShowDialog()
· Checking data type of a column in a DataSet
If DataSet.Tables(0).Columns("Name/Index").DataType.FullName =
_ "System.Decimal"
End If
· Reading a text file
Dim ConnectionStringText As String
Dim FS As New System.IO.FileStream(FileName, _
IO.FileMode.Open, IO.FileAccess.Read)
Dim R As New System.IO.StreamReader(FS)
ConnectionStringText = R.ReadLine
R.Close()
· Handling Crystal Reports
First add the crystal report (.rpt) file to project. Visual Basic automatically creates the .VB file for that particular report. This file is basically contains the class for the report added. We can use this class to call the particular report (By making a object of the class).
For example you have a report named “RBalanceSheet”. Now do the following to call the report through code.
Dim TReportDocument As New RBalanceSheet
‘ Passing values to formula fields defined in the
‘ report
TReportDocument.DataDefinition.FormulaFields.Item("OpeningText").Text = "’To Opening Balance’
‘ Setting DataSet (Data source) for the report
TReportDocument.Database.Tables.Item("XMLRBalanceSheet").SetDataSource(ReportDS.Tables(0))
‘ Calling the report through Crystal Report Viewer
CrystalReportViewer1.ReportSource = TReportDocument
· Handling DataGrid KeyPress and mouse click events
Add following event handler on form load event:
AddHandler ProductColumnStyle.TextBox.KeyPress, AddressOf
ProductColumn_KeyPress
AddHandler ProductColumnStyle.TextBox.DoubleClick, AddressOf ProductColumn_DoubleClick
Now add following event procedure:
Private Sub ProductColumn_KeyPress(ByVal sender As Object,
ByVal e As System.Windows.Forms.KeyPressEventArgs)
If Asc(e.KeyChar) = Keys.Space Then
‘ Write code here
End If
End Sub
Private Sub ProductColumn_DoubleClick(ByVal sender As
Object, ByVal e As System.EventArgs)
‘ Write code here
End Sub
· Handling Stored Procedures
1) Filling dataset using stored procedure:
Dim TempDS = New DataSet
MyDbCommand = New SqlCommand("sp_Select", MyConnection)
MyDbCommand.CommandType = CommandType.StoredProcedure
MyParamTempID = New SqlParameter("@TempID", “1”)
MyDbCommand.Parameters.Add(MyParamTempID)
MyDbAdapter.SelectCommand = MyDbCommand
MyDbAdapter.Fill(TempDS)
2) Using output parameter of the stored procedure:
Dim MyDbAdpater As New SqlDataAdapter
Dim MyDbCommand As New SqlCommand("sp_OutputValue", MyConnection)
MyDbCommand.CommandType = CommandType.StoredProcedure
Dim TempDs = New DataSet
Dim MyParamTempID1 As New SqlParameter("@TempID1", “1”) Dim MyParamTempID2 As New SqlParameter("@TempID2", “2”) Dim MyParamNameAdd As New SqlParameter("@NameAdd", SqlDbType.VarChar, 50)
MyParamNameAdd.Direction = ParameterDirection.Output
MyDbCommand.Parameters.Add(MyParamTempID1)
MyDbCommand.Parameters.Add(MyParamTempID2)
MyDbCommand.Parameters.Add(MyParamNameAdd)
MyDbCommand.ExecuteScalar()
MsgBox(MyParamNameAdd.Value)
3) Inserting values through stored procedure:
Dim MyDbCommand As New SqlCommand("sp_Insert", MyConnection)
Dim MyParamName As New SqlParameter("@Name", “John”)
MyDbCommand.CommandType = CommandType.StoredProcedure
MyDbCommand.Parameters.Add(MyParamName)
MyDbCommand.ExecuteNonQuery()
4) Using Parameterized query:
Dim TempDOB As Date
TempDOB = "01/05/1980"
Dim MyDBCommand As New SqlCommand("Insert InTo Personal (Name, Age, DOB) Values (@Name, @Age, @DOB)", MyConnection)
Dim MyParamName As New SqlParameter("@Name", “John”)
Dim MyParamAge As New SqlParameter("@Age", 25)
Dim MyParamDOB As New SqlParameter("@DOB", TempDOB)
MyDBCommand.Parameters.Add(MyParamName)
MyDBCommand.Parameters.Add(MyParamAge)
MyDBCommand.Parameters.Add(MyParamDOB)
MyDBCommand.ExecuteNonQuery()
· Binding DataGrid To Dataset
MyDBAdapter.Fill(MyTempDS, "MyTable")
DataGrid1.DataSource = MyTempDS.Tables(“MyTable”)
DataGrid1.DataBindings()
· Binding Combo Box To Dataset
MyDBAdapter.Fill(MyTempDS, "MyTable")
ComboBox1.DataSource = MyTempDS
ComboBox1.DisplayMember = "MyTable.Name"
