Thursday, July 14, 2011

How to Declare and Use Global Static Variable in VB .Net

Generally you can not declare a static and global variable in VB.Net. You ned to declare them as Static in a Function or Subroutine, but declared variable in this way will not be available publically for further use.

Way To declare Globally a Static type variable :

In VB.Net we have a keyword named as ‘Module’, with the help of this keyword we can declare a static variable globally.

Code Systex :

Module Identifier
            Public Variable_Name As DataType
            ‘Optioanlly you can create object of this variable also. like
            Variable_Name = New DataType
End Module

·        It is not a class level variable, it should be file level.
·        You need to write this code outside your class.
·        It will be accessible in complete file.

Example :

Module myNewModule
            Public myDataSet As DataSet
            myDataSet = new DataSet
End Module

Way to Use :
·        You can use this variable as a simple normal global variable in any function or subroutine
·        It will be accessible in complete file.
·        You can update its value anywhere in the same file.
·        You can access its value anywhere in same file.

For any questions feel free to comment….

Friday, June 17, 2011

Create an Excel worksheet from Recordset using VBA(Visual Basic Access)

' Subroutine to create an excel work sheet having data from a RecordSet
Public Sub ExcelSheetGeneration(ByVal arg_RecordSet As ADODB.Recordset)

    ' Creating Excel workbook with three sheets
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)

    Dim iCols As Integer

    DoCmd.Hourglass True
    'Formatting of excel sheet
    'Set row 1 text, bold and center
    xlSheet.Range(xlSheet.Cells(1, 1), _
        xlSheet.Cells(1, arg_RecordSet .Fields.count)).NumberFormat = "@"
    xlSheet.Range(xlSheet.Cells(1, 1), _
        xlSheet.Cells(1, arg_RecordSet .Fields.count)).Font.Bold = True
    xlSheet.Range(xlSheet.Cells(1, 1), _
        xlSheet.Cells(1, arg_RecordSet .Fields.count)).HorizontalAlignment = xlCenter
    'Get column names for excel worksheet from recoedsheet
     For iCols = 0 To arg_RecordSet .Fields.count - 1
        'Look for Excel override column name.  If not, then look for regular Display Column name.
         xlSheet.Cells(1, iCols + 1).Value = arg_RecordSet .Fields(iCols).Name
    'Load spreadsheet
    xlSheet.Cells(2, 1).CopyFromRecordset arg_RecordSet
    On Error GoTo Error_Exit
    'Formatting of excel sheet
    xlSheet.Range(xlSheet.Cells(1, 1), _
        xlSheet.Cells(65536, arg_RecordSet .Fields.count)).VerticalAlignment = xlCenter
    xlSheet.Range(xlSheet.Cells(1, 1), _
        xlSheet.Cells(65536, arg_RecordSet .Fields.count)).WrapText = True

    xlBook.Sheets("Sheet1").Name = Left("Excel Work Sheet Name", 31)
    xlBook.Application.Visible = True
    xlApp.ActiveWindow.FreezePanes = True

    DoCmd.Hourglass False
    Set xlSheet = Nothing
    Set arg_RecordSet = Nothing
    Exit Sub
    DoCmd.Hourglass False
    MsgBox Err.Number & "-" & Err.Description
    Resume Normal_Exit
End Sub

Getting selected fields in a Recordset from another Recordset using VBA(Visual Basic Access)

' It is a function having a parent recordset which will return another recordset having selected columns
Function InfoSheet(ByVal rst_parent As ADODB.Recordset) As ADODB.Recordset

    'Temporary recordset, which will have selected fields from parent recordset and will be returned from this function
    Dim rst_Temp As ADODB.Recordset
    Set rst_Temp = New ADODB.Recordset

    'Adding required number of Columns in temporary recordset : Datatypes must be same
    'If there is null values then it must be declared  adFldIsNullable
    With rst_Temp.Fields
        .Append "Field 1", adInteger, 4, adFldIsNullable
        .Append "Field 2", adVarChar, 50, adFldIsNullable
        .Append "Field 3", adVarChar, 25, adFldIsNullable
    End With
    'Add rows into recordset, having all data of those columns from parent recordset
    With rst_Temp
        If arg_rst.RecordCount > 0 Then
            Do While Not (arg_rst.EOF)
                    ![Field 1] = rst_parent .Fields.Item(0).Value
                    ![Field 2] = rst_parent .Fields.Item(1).Value
                    ![Field 3] = rst_parent .Fields.Item(6).Value
            MsgBox "No Records Found"
        End If

        'Dont close temporary recordset else data will not be available in this inside calling routine or function
    End With
    Set InfoSheet = rst_Temp   
End Function

My Projects

  • J2EE Online entertainment world(fully running)

My Blog List