Membuat Laporan dengan Crystal Report for Visual Studio

Laporan merupakan output dari sebuah sistem yang kita buat, maka oleh itu laporan sangat penting dari setiap sistem yang dibuat.
Berikut langkah-langkah singkat untuk membuat sebuat laporan dengan menggunakan Crystal Report for Visual Studio (Crystal Report yang sudah build in didalam Visual Studio diatas versi Express), sebenarnya Anda juga dapat menggunkan Crystal Report dengan aplikasi terpisah.

Pertama-tama, Anda harus membuat DataSet dengan menggunakan file .XSD, yang digunakan untuk menarik/ menempatkan field2 kedalam report designer nantinya:
... (next update)

Langkah-langkah berikutnya akan saya update nantinya beserta gambarnya...

Berikut Code untuk memunculkan Report:
- Buatlah sebuah Class sebagai DataAccess ke Database (misalnya: Class Sale)

Imports System.Data

Imports System.Data.SqlClient

Public Class Sales

    'Get Sales Invoice value and return in DATASET

    Public Function GenerateInvoice( _

            ByVal SalesOrderID As Integer, _

            ByVal PrintedBy As String) As DataSet

        Dim ds As New DataSet

        Using sqlConn As New SqlConnection(My.Settings.AdventureWorksConnectionString)

            Using sqlDA As New SqlDataAdapter("Sales.spGenerateInvoice", sqlConn)

                sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure

                With sqlDA.SelectCommand.Parameters

                    .AddWithValue("@SalesOrderID", SalesOrderID)

                    .AddWithValue("@PrintedBy", PrintedBy)

                End With

                sqlDA.Fill(ds)

            End Using

        End Using

        Return ds

    End Function

    'Get Sales Invoice value without return the value but

    'passing the control ByRef on parameter to fill directly

    Public Sub GenerateInvoice(ByRef dtReport As DataTable, _

         ByVal SalesOrderID As Integer, _

         ByVal PrintedBy As String)

        Using sqlConn As New SqlConnection(My.Settings.AdventureWorksConnectionString)

            Using sqlDA As New SqlDataAdapter("Sales.spGenerateInvoice", sqlConn)

                sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure

                With sqlDA.SelectCommand.Parameters

                    .AddWithValue("@SalesOrderID", SalesOrderID)

                    .AddWithValue("@PrintedBy", PrintedBy)

                End With

                sqlDA.Fill(dtReport)

            End Using

        End Using

    End Sub

End Class

- Berikut Code dari Form yang digunakan untuk memanggil Crystal Report

Public Class Form1

    Private dsR As New DataSet

    Private objDSReport As New dsReport

    Private objSale As New Sales

    Private Sub btnDirectPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDirectPrint.Click

        '----------- CARA 1 -------------

        'Dim ds As New DataSet

        'ds = objSale.GenerateInvoice(txtSalesOrderID.Text, "Budiyanto")

        'For i As Integer = 0 To ds.Tables(0).Rows.Count - 1

        '    objDSReport.Tables("dtSalesInvoice").Rows.Add()

        '    With objDSReport.Tables("dtSalesInvoice").Rows(i)

        '        .Item(0) = ds.Tables(0).Rows(i).Item(0)

        '        .Item(1) = ds.Tables(0).Rows(i).Item(1)

        '        .Item(2) = ds.Tables(0).Rows(i).Item(2)

        '        .Item(3) = ds.Tables(0).Rows(i).Item(3)

        '        .Item(4) = ds.Tables(0).Rows(i).Item(4)

        '        .Item(5) = ds.Tables(0).Rows(i).Item(5)

        '        .Item(6) = ds.Tables(0).Rows(i).Item(6)

        '        .Item(7) = ds.Tables(0).Rows(i).Item(7)

        '        .Item(8) = ds.Tables(0).Rows(i).Item(8)

        '        .Item(9) = ds.Tables(0).Rows(i).Item(9)

        '        .Item(10) = ds.Tables(0).Rows(i).Item(10)

        '    End With

        'Next

        'Dim rSalesInvoice As New rptSalesInvoice

        'rSalesInvoice.SetDataSource(objDSReport.Tables("dtSalesInvoice"))

        'rSalesInvoice.PrintToPrinter(1, False, 1, 999)

        '------------ CARA 2 --------------

        objSale.GenerateInvoice(objDSReport.Tables("dtSalesInvoice"), _

                                txtSalesOrderID.Text, "Budiyanto")

        Dim rSalesInvoice As New rptSalesInvoice

        rSalesInvoice.SetDataSource(objDSReport.Tables("dtSalesInvoice"))

        rSalesInvoice.PrintToPrinter(1, True, 1, 999)

    End Sub

    Private Sub btnPreview_Click(ByVal sender As System.Object, _

        ByVal e As System.EventArgs) Handles btnPreview.Click

        objSale.GenerateInvoice(objDSReport.Tables("dtSalesInvoice"), _

                                txtSalesOrderID.Text, "Budiyanto")

        Dim rSalesInvoice As New rptSalesInvoice

        rSalesInvoice.SetDataSource(objDSReport.Tables("dtSalesInvoice"))

        crvSalesInvoice.ReportSource = rSalesInvoice

        crvSalesInvoice.Refresh()

    End Sub

End Class

Silahkan dicoba, jika ada yang ingin membuat tutorial step by step, bisa email kesaya dalam bentuk document MS. Word, nanti saya postkan....

(dari mahasiswa untuk mahasiswa)

Stream Reader

Stream reader digunakan untuk membaca file text, yang disimpan dalam “NotePad”, berikut contoh aplikasi sederhana untuk membaca isi dari notepad perbaris (3 baris).

 

Code:

'untuk menggunakan stream reader, harus melakukan IMPORTS component
'yaitu System.IO
Imports System.IO
 
Public Class Form1
 
    Private Sub btnGetData_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnGetData.Click
        'mengambil lokasi file exe
        Dim myPath As String = AppDomain.CurrentDomain.BaseDirectory
        Dim SR As StreamReader
        Dim strBaris1, strBaris2, strBaris3 As String
 
        'mengosongkan variabel
        strBaris1 = ""
        strBaris2 = ""
        strBaris3 = ""
 
        'mengosongkan stream reader
        sr = Nothing
 
        'mengecek apakah file myData.txt ada
        If File.Exists(myPath & "myData.txt") Then
            'SR = New StreamReader(myPath & "myData.txt")
            'Dim sr As StreamReader = File.OpenText(myPath & "myData.txt")
            SR = File.OpenText(myPath & "myData.txt")
            strBaris1 = SR.ReadLine
            strBaris2 = SR.ReadLine
            strBaris3 = SR.ReadLine
 
        Else
            MessageBox.Show("File yang Anda maksud tidak ditemukan", _
                        "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        'SR.Close()
 
        'Jika SR/StreamReader berisi atau tidak kosong,
        'maka SR harus ditutup
        If Not SR Is Nothing = True Then
            SR.Close()
        End If
 
        'Jika semua variabel strBaris mempunyai isi
        If strBaris1 <> "" And strBaris2 <> "" And strBaris3 <> "" Then
            'sebelum mengisi data pada TextBox, ComboBox, ListBox
            'kosongkan data terlebih dahulu
            txtData.Text = ""
            cboData.Items.Clear()
            lstData.Items.Clear()
 
            'mengisi txtData
            'vbCrLf berfungsi untuk memberikan ENTER / baris baru
            txtData.Text = strBaris1 & vbCrLf & _
                             strBaris2 & vbCrLf & _
                             strBaris3
            'vbCrlF ==> visual basic Carriage return Line feed
 
            'mengisi cboData
            cboData.Items.Add(strBaris1)
            cboData.Items.Add(strBaris2)
            cboData.Items.Add(strBaris3)
 
            'mengisi lstData
            lstData.Items.Add(strBaris1)
            lstData.Items.Add(strBaris2)
            lstData.Items.Add(strBaris3)
        End If
    End Sub
 
End Class

Download Source Lengkap : DOWNLOAD

Membuat Form Koneksi Database dengan VB 2005

Untuk membuat suatu aplikasi agar bisa dijalankan pada sebuah jaringan diperlukan sebuah form koneksi agar programmer tidak perlu lagi membuka source code aplikasi untuk merubah Connection String-nya. Hal ini sangat berguna jika aplikasi dijalankan pada komputer yang berlainan dengan database. Berikut ini saya membuat form untuk melakukan koneksi ke-database SQL Server 2000 dengan menggunakan Visual Studio 2005 dan koneksi yang dibuat disimpan pada file notepad dengan extention ".ini". Dalam hal ini saya juga menggunakan module agar bisa diakses disemua form nantinya.







Berikut source code-nya :



Source pada module


Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Module mdlConnection
Public sqlConn As New SqlConnection
Public sqlCmd As New SqlCommand
Public DA As New SqlDataAdapter
Public DS As New DataSet
Public SR As StreamReader
Public SW As StreamWriter
Private dbServer, dbUserName, _
dbPassword, dbDatabase As String
Public myPath As String = _
AppDomain.CurrentDomain.BaseDirectory
Private Sub winConnString()
sqlConn.ConnectionString = _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=" & dbDatabase & _
";Data Source=" & dbServer
End Sub
Private Sub ConnString()
sqlConn.ConnectionString = _
"Persist Security Info=False;User ID=" & _
dbUserName & ";Pwd=" & dbPassword & _
";Initial Catalog=" & dbDatabase & _
";Data Source=" & dbServer
End Sub
Public Sub OpenConn()
sqlConn.Close()

dbServer = ""
dbUserName = ""
dbPassword = ""
dbDatabase = ""

Try
SR = File.OpenText(myPath & "Connection.ini")
Do While SR.Peek <> -1
dbServer = SR.ReadLine.Trim
dbUserName = SR.ReadLine.Trim
dbPassword = SR.ReadLine.Trim
dbDatabase = SR.ReadLine.Trim
Loop
SR.Close()
If dbUserName = "" And dbPassword = "" Then
winConnString()
Else
ConnString()
End If
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sqlConn.Open()
Catch ex As Exception
MessageBox.Show("Connection Failed", _
"Connection Error", MessageBoxButtons.OK, _
MessageBoxIcon.Error)
frmConnection.ShowDialog()
End Try
End Sub
Public Function tryConnection(ByVal chxWin As CheckBox, _
ByVal dServer As String, ByVal dUserName As String, _
ByVal dPassword As String, ByVal dDatabase As String) _
As Boolean

dbServer = dServer
dbUserName = dUserName
dbPassword = dPassword
dbDatabase = dDatabase

If chxWin.Checked = True Then
winConnString()
Else
ConnString()
End If
Try
sqlConn.Open()
sqlConn.Close()
Return True
Catch ex As Exception
sqlConn.Close()
Return False
End Try
End Function
Public Function saveConn(ByVal dServer As String, _
ByVal dUserName As String, ByVal dPassword As String, _
ByVal dDatabase As String) As Boolean
Try
File.Delete(myPath & "Connection.ini")
SW = File.AppendText(myPath & "Connection.ini")
With SW
.WriteLine(dServer)
.WriteLine(dUserName)
.WriteLine(dPassword)
.WriteLine(dDatabase)
End With
SW.Close()
Return True
Catch ex As Exception
Return False
End Try
End Function
End Module


Pada form koneksi

Public Class frmConnection

Private Sub btnCancel_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCancel.Click
Me.Close()
End Sub

Private Sub chxWindows_CheckedChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles chxWindows.CheckedChanged
txtUserName.Clear()
txtPassword.Clear()
If chxWindows.Checked = True Then
txtUserName.ReadOnly = True
txtPassword.ReadOnly = True
Else
txtUserName.ReadOnly = False
txtPassword.ReadOnly = False
End If

End Sub

Private Sub btnTest_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnTest.Click
If tryConnection(chxWindows, _
txtServer.Text.Trim, _
txtUserName.Text.Trim, _
txtPassword.Text.Trim, _
txtDatabase.Text.Trim) = True Then
MessageBox.Show("Connection Success", "Connection Success", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Connection Failed", "Connection Failed", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSave.Click
If saveConn(txtServer.Text.Trim, txtUserName.Text.Trim, _
txtPassword.Text.Trim, txtDatabase.Text.Trim) = True Then
OpenConn()
MessageBox.Show("Connection Saved Successful", "Success", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.Close()
End If
End Sub
End Class

Pembuatan form koneksi ini tidak menggunakan enkripsi untuk lebih aman dapat menggunakan enkripsi dan juga dapat disimpan pada registry.