18 February 2011

Create Simple Dynamic Report in VB6 and MySQL as Database

Create Simple Dynamic Report in VB6 and MySQL as Database



First of all you need Microsoft Visual Basic 6 software, WAMPSERVER/Xampp Server to test locally, and MySQL ODBC connector(3.51,5.1 version)

Granting you are already created your DSN( Database Source Name ) ; and already binded with MYSQL
(Overview) Run Wamp/xampp server so that the mysql service (port: 3306) is up!


In Your VB environment create a form: for this i created simple one!


Heres the important details highlighted in green


Heres my simple database in phpmyadmin before creating dynamic report!

DATABASE: reporttest
TABLE: mytable
Fields: ID, DESCRIPTION, DATE


HERES the DESIGN FORM in DATA REPORT


NOW THE CODING PART! 

BASICALLY you need to connect first before you query something in the database:

CODES:
Function openConnectDB()
    Set con = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
    con.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=reporttest; User=root;Option=3;"
'since i dont have password: i exclude: password=yourpass;   
 con.Open
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = con
    cmd.CommandType = adCmdText
End Function

Generate REPORT






Private Sub cmdGenerate_Click()


Dim i As Integer
Call openConnectDB


rst.Open "select * from mytable", con, adOpenKeyset, adLockOptimistic

With myreport
Set .DataSource = Nothing
.DataMember = ""
Set .DataSource = rst.DataSource

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
'This section to display information in the header section of the report
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''
.Sections("section2").Controls("lblusername").Caption = Me.lblusername.Caption
'display the MABZICLE caption into report

''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
'This section to display information in the section1 of the report. A loop is ised to display multiple records.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''


With .Sections("Section1").Controls
For i = 1 To .Count
If TypeOf .Item(i) Is RptTextBox Then
.Item(i).DataMember = ""
.Item(i).DataField = rst.Fields(i - 1).Name


End If
Next i



End With

End With
myreport.ReportWidth = 6000
myreport.Show


Call closeDB
end sub




Function closeDB()
    Set rst = Nothing
    Set con = Nothing
    
End Function

FINAL OUTPUT



SAMPLE PROJECT: VB Project



Advertisement


Featured Offers:
Advertisement