VB 2010 : Menghilangkan bunyi Beep setelah menekan tombol ENTER

Bagi programmer VB 6, pasti familiar dengan kode berikut ini :

Private Sub txtfields_KeyPress(Index As Integer, KeyAscii As Integer)
  If mbEditFlag = True Or mbAddNewFlag = True Then
      If KeyAscii = 13 Then
       If Index <= 2 Then
          Me.txtFields(Index + 1).SetFocus
       ElseIf Index = 3 Then Me.cmbTitle.SetFocus
           ElseIf Index = 4 Then Me.cmbCompany.SetFocus
                  Else: Me.cmdUpdate.SetFocus
         
       End If
       KeyAscii = 0
      End If
  Else
    If KeyAscii = 13 Then
       If Index <= 2 Then
          Me.txtFields(Index + 1).SetFocus
       ElseIf Index = 3 Then Me.cmbTitle.SetFocus
           ElseIf Index = 4 Then Me.cmbDepart.SetFocus
               Else: Me.cmdClose.SetFocus
         
       End If
       KeyAscii = 0
    End If
  End If
End Sub

Statement KeyAscii=0 berfungsi untuk menghilangkan bunyi BEEP setelah menekan tombol ENTER. Nah bagaimana cara melakukan hal yang sama di VB 2010 ?

Di VB 2010, Statement KeyAscii=0 diganti dengan Statement e.Handled=True seperti terlihat pada contoh procedure berikut ini :

Private Sub txtHP_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtHP.KeyPress

        If e.KeyChar = Chr(13) Then
            Me.txtEmail.Focus()
            e.Handled = True
        Else
            If (Not Char.IsNumber(e.KeyChar) AndAlso Not e.KeyChar = Microsoft.VisualBasic.Chr(Keys.Back)) Then
                MsgBox("Enter a valid number", vbCritical)
                e.Handled = True
            End If
        End If
    End Sub

Statement If (Not Char.IsNumber(e.KeyChar) AndAlso Not e.KeyChar = Microsoft.VisualBasic.Chr(Keys.Back)) pada contoh di atas berfungsi untuk memvalidasi inputan pada control txtHP hanya untuk numeric saja. Sehingga jika user memasukkan selain numeric pada control txtHP akan menampilkan pesan "Enter a Valid Number".

Semoga bermanfaat.

Read more »

DataGridView : Changing Row Color based on Row Value

Berikut ini adalah contoh coding di VB 2010 untuk memberi warna Row pada DataGridView sesuai dengan Value nya. Dalam contoh ini saya memberi warna yang berbeda untuk Kodesekolah SD, SMP dan SMA.

Coding ini saya ambil dari Project yang sedang saya kerjakan yaitu Software PPDB (Penerimaan Peserta Didik Baru) atau yang sebelumnya dikenal sebagai PSB (Penerimaan Siswa Baru) pada salah satu sekolah di Bogor Jawa Barat. Semoga bermanfaat.

Private Sub dgSysAdm_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgSysAdm.CellFormatting

        Dim currentRow As DataRowView
        If e.RowIndex >= 0 Then
            If e.RowIndex <= clsSysAdm.DSSysAdm.Tables(0).Rows.Count - 1 Then
                currentRow = clsSysAdm.DSSysAdm.Tables(0).DefaultView.Item(e.RowIndex)
                Dim cellColor As Color
                If currentRow.Item("KODESEKOLAH").ToString = "SD" Then
                    cellColor = Color.BlanchedAlmond  ' LightBlue
                ElseIf currentRow.Item("KODESEKOLAH").ToString = "SMP" Then
                    cellColor = Color.GreenYellow
                ElseIf currentRow.Item("KODESEKOLAH").ToString = "SMA" Then
                    cellColor = Color.PaleTurquoise
                End If
                e.CellStyle.BackColor = cellColor
            End If
        End If
    End Sub

Read more »

Membuat Chart di VB 2010

Sekitar 1 bulan lalu saya diminta oleh seorang sahabat untuk mengajari beliau cara membuat Chart di VB 2010.  Karena waktu beliau juga sangat padat akhirnya saya buatkan coding yang disesuaikan dengan kebutuhan beliau sebagai contoh untuk pelajari.

Berikut ini saya share codingnya, semoga bermanfaat.






Imports System.Windows.Forms.DataVisualization.Charting
Public Class frmExample6
    Private dtTest As New DataTable
   

    Private Sub cmdProses_Click(sender As System.Object, e As System.EventArgs) Handles cmdProses.Click

        Dim i As Integer
        Dim j As Integer
        Dim jumlahData As Integer


        jumlahData = 1 + ((Val(Me.txtMaximum.Text) - Val(Me.txtMinimum.Text)) / Val(txtSelang.Text))
        Dim aLi(jumlahData) As Double
        Dim aWi(jumlahData) As Double
        Dim arrData(jumlahData, jumlahData) As Double

        aLi(0) = Val(Me.txtMinimum.Text)
        aWi(0) = Val(Me.txtA.Text) * aLi(0) ^ Val(Me.txtB.Text)
        For i = 1 To jumlahData - 1
            aLi(i) = aLi(i - 1) + Val(Me.txtSelang.Text)
            aWi(i) = Val(Me.txtA.Text) * aLi(i) ^ Val(Me.txtB.Text)
        Next


        dtTest.Columns.Add("aLi", GetType(Double))
        dtTest.Columns.Add("aWi", GetType(Double))

        For i = 0 To jumlahData - 1
            dtTest.Rows.Add(aLi(i), aWi(i))
        Next

        DataGridView1.DataSource = dtTest.DefaultView
        DataGridView1.Columns(0).Width = 50

      
    End Sub

    Private Sub cmdChart_Click(sender As System.Object, e As System.EventArgs) Handles cmdChart.Click
        Chart1.Dock = DockStyle.Bottom
        Chart1.Anchor = AnchorStyles.Bottom Or AnchorStyles.Top

        'Chart1.Series.Add("aWi")
        With Chart1.Series(0)
            '  .Name = "aLi"
            .Font = New Font("Arial", 8, FontStyle.Italic)
            .BackGradientStyle = GradientStyle.TopBottom
            .Color = Color.Magenta
            .BackSecondaryColor = Color.Purple
            .IsValueShownAsLabel = False
            .LabelBackColor = Color.Transparent
            .LabelForeColor = Color.Purple
            .Points.DataBind(dtTest.DefaultView, "aLi", "aWi", Nothing)
            .CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
            .ChartType = SeriesChartType.Line
        End With

    End Sub

   
End Class

Read more »

VB 2010 - PAD A STRING WITH CHARACTERS

Example : PADL("1",4,"0") = "0001"
                 PADR("1,4,"0") = "1000"

Public Class Form1
    Private clsStr As clsStringFunction
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Me.Close()
        Application.Exit()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        clsStr = New clsStringFunction
        Me.TextBox1.Text = "CT." & clsStr.PADL("1", 7, "0")
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.TextBox1.Enabled = False
        Me.TextBox2.Focus()
    End Sub
End Class


Public Class clsStringFunction
    Public Function PADL(ByVal cWord As String, ByVal nLen As Integer, ByVal cInsert As String) As String

        Dim cResult As String
        Dim cTemp As String

        cTemp = ""
        cResult = ""

        If nLen > Len(cWord) Then
            For i = 0 To nLen - Len(cWord) - 1
                cTemp = cTemp & cInsert
            Next
            cResult = cTemp & cWord
        Else
            cResult = cWord
        End If
        Return cResult
    End Function

    Public Function PADR(ByVal cWord As String, ByVal nLen As Integer, ByVal cInsert As String) As String

        Dim cResult As String
        Dim cTemp As String

        cTemp = ""
        cResult = ""
        If nLen > Len(cWord) Then
            For i = 0 To nLen - Len(cWord) - 1
                cTemp = cTemp & cInsert
            Next
            cResult = cWord & cTemp
        Else
            cResult = cWord
        End If
        Return cResult
    End Function
End Class
 

Read more »

VB 2010 : Membaca Data Excel dan Menyimpannya ke Database MDB

Setelah agak lama tidak update blog karena kesibukan, alhamdulillah hari ini saya bisa mengunjungi dan mengupdate kembali blog saya.

Setelah sebelumnya saya share coding untuk menyimpan data ke Excel dengan VB 2010, kali ini saya akan share coding tentang bagaimana membaca data dari file Excel, mengolah datanya dan  kemudian menyimpannya ke dalam database. Dalam hal ini saya menggunakan Microsoft Access Database (MDB).

File Excel yang akan saya simpan adalah file tsjob.xlsx seperti tampak di bawah ini.

Sedangkan Tabel yang akan saya update adalah table tsjob yang ada pada database PM.MDB. Struktur tabel tsjob adalah sebagai beikut :

Field Name       Field Type            Field Size
brandnm              text                       35
projectcat            text                       50
jobgroupno          text                       11
jobgroupnm         text                       50
jobno                   text                      50
oldjobno              text                      50
oldjobgroupno     text                      11
oldjobgroupnm    text                      50
jobid                   text                      10
jobnm                 text                     150
customernm        text                       40
jobmgr               text                       40
begin                  date/time
status                 text                       30
billable               Yes/No
description         memo
projectcode       text                        61
company           text                        25 


Selanjutnya lakukan langkah-langkah berikut :
1. Buka VB 2010 kemudian create project baru.
2. Tambahkan form dan beri name frmExcel2mdb.
3. Tambahkan 2 buah datagrid pada form yang telah dibuat.
4. Beri nama salah satu Datagrid yang baru saja ditambahkan dengan grdData. Datagrid ini digunakan untuk menampilkan data hasil import dari file excel.
5. Beri nama datagrid yang lainnya dengan grdJob. Datagrid ini digunakan untuk menampilkan data dari file mdb.
6. Tambahkan 2 button pada form. Satu button diberi nama cmdReadExcel. Dan yang satunya diberi nama cmdSavetoMDB.
7. Selanjutnya copy code di bawah ini.
8. Semoga bermanfaat.

 

Imports System.Data
Imports System.Data.OleDb

Public Class frmExcel2mdb
    Dim ds As DataSet
    Dim da As OleDbDataAdapter
    Dim ds2 As DataSet
    Dim da2 As OleDbDataAdapter

    Private Sub cmdReadExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReadExcel.Click
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\tsjob.xlsx;Extended Properties=Excel 12.0;"
        Dim myConnection As OleDbConnection = New OleDbConnection
       
        Dim strSQL As String

        Try
          
            myConnection.ConnectionString = connString
            strSQL = "Select * from [Sheet1$]"
            da = New OleDbDataAdapter(strSQL, myConnection)
            ds = New DataSet
            da.Fill(ds, "dataTable")
            grdData.DataSource = ds.Tables(0)
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub cmdSavetoMDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSavetoMDB.Click
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\PM.mdb;Jet OLEDB:Database Password=;"
        Dim MyCon = New OleDbConnection()
        Dim strSql As String
        Dim dgRow As DataGridViewRow
        Dim strSQLi As String
        Dim strCmd As OleDbCommand

        Try
            MyCon.ConnectionString = strConn

            MyCon = New OleDbConnection(strConn)
            If MyCon.State <> ConnectionState.Closed Then MyCon.Close()
            MyCon.Open()

            For Each dgRow In Me.grdData.Rows

                Dim cmdJob As New OleDbCommand
                Dim daJob As New OleDbDataAdapter
                Dim dsJob As New DataSet
                Dim dtJob As New DataTable

                strSql = "Select count(*) from tbJob"
                cmdJob = MyCon.CreateCommand
                cmdJob.CommandText = "SELECT * FROM tsJob where Trim(JobNo) = '" & Trim(dgRow.Cells(3).Value) & "'"
                daJob.SelectCommand = cmdJob
                daJob.Fill(dsJob, "Job")
                dtJob = dsJob.Tables("Job")
                               

                If dtJob.Rows.Count > 0 Then

                    'TO DO : NOTHING
                Else
                    strSQLi = "Insert into tsjob (BRANDNM,PROJECTCAT,JOBGROUPNO,JOBGROUPNM,JOBNO,OLDJOBNO,OLDJOBGROUPNO,OLDJOBGROUPNM," & _
                              "JOBID,JOBNM,CUSTOMERNM,STATUS,DESCRIPTION,COMPANY) " & _
                              "VALUES('" & RTrim(dgRow.Cells(0).Value) & _
                                      "','" & RTrim(dgRow.Cells(2).Value) & "2012" & _
                                      "','" & RTrim(dgRow.Cells(1).Value) & _
                                      "','" & RTrim(dgRow.Cells(2).Value) & _
                                      "','" & RTrim(dgRow.Cells(3).Value) & _
                                      "','" & RTrim(dgRow.Cells(3).Value) & _
                                      "','" & RTrim(dgRow.Cells(1).Value) & _
                                      "','" & RTrim(dgRow.Cells(2).Value) & _
                                      "','" & RTrim(Mid(dgRow.Cells(3).Value, 4)) & _
                                      "','" & RTrim(dgRow.Cells(4).Value) & _
                                      "','" & RTrim(dgRow.Cells(5).Value) & _
                                      "','" & RTrim(dgRow.Cells(6).Value) & _
                                      "','" & "" & _
                                      "','" & "PUBLICIS INDONESIA" & "')"
                    strCmd = New OleDbCommand(strSQLi, MyCon)
                    strCmd.ExecuteNonQuery()
                    strSQLi = ""

                End If
            Next

            Call DisplayJob()
            MyCon.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

    Private Sub DisplayJob()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\PM.mdb;Jet OLEDB:Database Password=;"
        Dim MyCon = New OleDbConnection()

        Try
            MyCon.ConnectionString = strConn

            MyCon = New OleDbConnection(strConn)
            If MyCon.State <> ConnectionState.Closed Then MyCon.Close()
            MyCon.Open()

            Dim cmdJob As New OleDbCommand
            Dim daJob As New OleDbDataAdapter
            Dim dsJob As New DataSet
            Dim dtJob As New DataTable

            cmdJob = MyCon.CreateCommand
            cmdJob.CommandText = "SELECT * FROM tsJob"
            daJob.SelectCommand = cmdJob
            daJob.Fill(dsJob, "Job")
            dtJob = dsJob.Tables("Job")

            Me.grdJob.DataSource = dsJob.Tables(0)

            MyCon.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmExcel2mdb_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.grdData.AllowUserToAddRows = False
        Me.grdJob.AllowUserToAddRows = False

        Call DisplayJob()
    End Sub
End Class

Read more »

UPDATE FILE EXCEL DARI VB .NET 2010

Berikut ini adalah contoh coding untuk mengedit/mengupdate file Excel dari VB  2010.

Option Explicit On

Imports Excel = Microsoft.Office.Interop.Excel

Imports Microsoft.VisualBasic

Public Class Form1

    Private aEmployee As New List(Of clsEmployee) From _

         {New clsEmployee With {.Name = "ANTO", .GajiPokok = 5000000, _ 
                                .TTransport =  1000000,.TJabatan = 2000000, _ 
                                .GajiTotal= "=D3+E3+F3", .AsuransiA = "=0.03*G3",_ 
                                .AsuransiB = "=0.02*G3", .Pinjaman = 200000, _ 
                                .TakeHomePay = "=G3-H3-I3-J3"},

          New clsEmployee With {.Name= "TOTO",.GajiPokok=5500000,.TTransport=1500000, _
                                .TJabatan = 3000000, .GajiTotal = "=D4+E4+F4", _

                                .AsuransiA = "=0.03*G4", .AsuransiB = "=0.02*G4", _ 
                                .Pinjaman = 300000, .TakeHomePay = "=G4-H4-I4-J4"},

          New clsEmployee With {.Name ="DINA",.GajiPokok=6000000,.TTransport=1000000, _
                                .TJabatan = 2000000, .GajiTotal = "=D5+E5+F5", _

                                .AsuransiA = "=0.03*G5", .AsuransiB = "=0.02*G5", _ 
                                .Pinjaman = 0, .TakeHomePay = "=G5+H5+I5+J5"},

          New clsEmployee With {.Name="WENY",.GajiPokok=3000000,.TTransport=500000, _
                                .TJabatan = 1000000, .GajiTotal = "=D6+E6+F6", _

                                .AsuransiA = "=0.03*G5", .AsuransiB = "=0.02*G5", _ 
                                .Pinjaman = 0, .TakeHomePay = "=G6+H6+I6+J6"}}

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application

        Dim xlWBook As Excel.Workbook

        Dim xlSheet As Excel.Worksheet

        Dim sheetRange As Excel.Range

        Dim cRow As String

        Dim i As Integer

        xlApp = New Excel.Application

        xlWBook = xlApp.Workbooks.Open("c:\MyProject\Salary.xlsx")

        xlSheet = xlWBook.Worksheets("sheet1")

        Try

            For i = 0 To 3

                If i > 1 Then

                    cRow = Convert.ToString(i + 3)

                    sheetRange = xlSheet.Range("b" + cRow, "k" + cRow)

                    sheetRange.EntireRow.EntireRow.Insert()

                End If

                With xlSheet

                    .Cells(i + 3, 2) = i + 1

                    .Cells(i + 3, 3) = aEmployee(i).Name

                    .Cells(i + 3, 4) = aEmployee(i).GajiPokok

                    .Cells(i + 3, 5) = aEmployee(i).TTransport

                    .Cells(i + 3, 6) = aEmployee(i).TJabatan

                    .Cells(i + 3, 7) = aEmployee(i).GajiTotal

                    .Cells(i + 3, 8) = aEmployee(i).AsuransiA

                    .Cells(i + 3, 9) = aEmployee(i).AsuransiB

                    .Cells(i + 3, 10) = aEmployee(i).Pinjaman

                    .Cells(i + 3, 11) = aEmployee(i).TakeHomePay

                    If i = 3 Then

                        cRow = Convert.ToString(i + 3)

                        .Cells(i + 4, 3) = "GRAND TOTAL"

                        .Cells(i + 4, 4) = "=Sum(D3:D" + cRow + ")"

                        .Cells(i + 4, 5) = "=Sum(E3:E" + cRow + ")"

                        .Cells(i + 4, 6) = "=Sum(F3:F" + cRow + ")"

                        .Cells(i + 4, 7) = "=Sum(G3:G" + cRow + ")"

                        .Cells(i + 4, 8) = "=Sum(H3:H" + cRow + ")"

                        .Cells(i + 4, 9) = "=Sum(I3:I" + cRow + ")"

                        .Cells(i + 4, 10) = "=Sum(J3:J" + cRow + ")"

                        .Cells(i + 4, 11) = "=Sum(K3:K" + cRow + ")"

                    End If

                End With

            Next

            cRow = Convert.ToString(i + 3)

            sheetRange = xlSheet.Range("D3", "k" + cRow)

            sheetRange.NumberFormat = "#,##0_);[Red](#,##0)"

        Catch ex As Exception

           

        End Try

        xlWBook.Save()

        xlWBook.Close()

        xlApp.Quit()

        DeleteObject(xlApp)

        DeleteObject(xlWBook)

        DeleteObject(xlSheet)

    End Sub

    Private Sub DeleteObject(ByVal obj As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing

        Catch ex As Exception

            obj = Nothing

        Finally

            GC.Collect()

        End Try

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        End

    End Sub

End Class

Public Class clsEmployee

    Public Property Name As String = ""

    Public Property GajiPokok As Double = 0

    Public Property TTransport As Double = 0

    Public Property TJabatan As Double = 0

    Public Property GajiTotal As String = ""

    Public Property AsuransiA As String = ""

    Public Property AsuransiB As String = ""

    Public Property Pinjaman As Double = 0

    Public Property TakeHomePay As String = ""

End Class

Tampilan File Excel sebelum diupdate melalui VB 2010

Tampilan File Excel setelah diupdate melalui VB 2010

Semoga bermanfaat!

Read more »