[VB.NET] CRUD using database SQLite

Description
       SQLite is a lightweight portable database. mainly to store small - medium data. if  you have large data you must use a proper database like mysql , sqlserver or oracle. in this article will explain how to CRUD SQLite in VB.NET and display into datagridview. i will make this tutorial simply , very easy to understand.

HowTo
1. Download SQLite library for .NET, you can find HERE (your version must same with your visual studio)
2. After download and install you must add reference that library, open your visual studio there's 2 refference you must include.
    -go Project > Add Refference > go tab NET > double click System.Configuration
 

 System Configuration

 - go Project > add Refference > go Tab Browse > find file system.data.sqlite.dll , default installation :
    "C:\Program Files\System.Data.SQLite\[VERSION]\bin"

SQLite Library

3. Download SQLitebrowser , you can find HERE
4. After download and install , now we must create database and table from SQLitebrowser. now open the SQLitebrowser application.
    - Click "New Database", and save to your visual studio either DEBUG or RELEASE (as you wish). it will create new file mydatabase.db (That's your portable database file). if you open that file using notepad you will see the data cannot be readable.
     - now, create your table by click "Create Table" button  in "Database Structure Tab"and field structure, you can explore this feature, i won't explain this.
     - after finish you can add record by click "Browse Data Tab" and click "New Record" Button, you can add data by fill the datagrid.
     - if you finish add data , now CLOSE DATABASE AND SAVE. if you forgot close you can't connect the table  (don't worry it's first time only).

5. back to visual studio. now mydatabase.db has one record, next step we just using SQL language to manipulation or edit the database.
   - add 1 button and 1 datagridview to form.
   - double click the button and you will trigger event click. add this code below :

'-------start here----------------------------------------------------------------------------------
Imports System.Data.SQLite

Dim mydata As New DataTable
Dim myconnection As New SQLiteConnection("Data Source=PATH_TO_DB\mydatabase.db")
Dim mycommand, reader
myconnection.Open()
mycommand = New SQLiteCommand(myconnection)
mycommand.CommandText = "select * from mytable"
reader = mycommand.ExecuteReader()
mydata.Load(reader)
reader.Close()
myconnection.Close()
datagridview.DataSource = mydata

'try show one data
 'MsgBox(mydata.Rows(0)("YOUR FIELD").ToString)

simple right ? hehe :)

Read more

[VB.NET] Dialog

Description
     Add dialog component to form and write this code :

savedialog1.InitialDirectory = "D:\" 'setup initial directory
savedialog1.Filter = "Text Files (*.txt)|*.txt" 'Create type extention file

If savePathPasuruan.ShowDialog = DialogResult.OK Then
   'if result ok 
Else
   'if result false
End If

Read more

[VB.NET] Styling Nominal Currency in textbox

Description
     if you make an application for finance but you not styling the text box, it will horrible for user. a mistake cannot be tolerated in finance , Because it is always associated with the company's money. who will be responsible ? definite user of the financial department. IT departments do not want to be responsible as well as the finance department because of lack of applications. to prevent this i will give a function to styling nominal currency like in finance. for example :

2500000 => horrible format , now compare with this 2,500,000 => readable

now this function :

Public Function currencyFormat(ByVal money As String) As String
    Dim formattedString As String = ""
    For i = 1 To money.Length
        If i >= 3 Then
            If i Mod 3 = 1 Then
                formattedString = money.Substring(money.Length - i, 1) & "," & formattedString
            Else
                formattedString = money.Substring(money.Length - i, 1) & formattedString
            End If
        Else
            formattedString = money.Substring(money.Length - i, 1) & formattedString
        End If
    Next i
    Return formattedString
End Function

To Use :
textbox1 => got_focus event :
textbox1 .Text = Replace(textbox1.Text, ",", "")

textbox1 => lostfocus_focus event :
textbox1.Text = currencyFormat(Replace(textbox1.Text, ",", ""))

if you want this value entered in database, don't forget always to sanitize character ',' (comma) to prevent error in query.

work's great :D

Read more

[VB.NET] dataGridView to Excel Class

Description
     In this article i will share excel class which can convert the data in the DataGrid to excel, very usefull better than code from scratch. the limitation is the data appear in datagridview same data in excel later, you cannot costumize the block field.


How To

here's the exportExcel Class :

Imports System.Windows.Forms
Imports System.Windows.Forms
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Threading
Imports System.Globalization
Imports System.Net

Public Class exportExcel
    Dim oexcel As Excel.Application = New Excel.Application()
    Dim obook As Excel.Workbook
    Dim osheet As Object
    Dim customFormatDate As String = "dd/MM/yyyy"

    Public Sub exportExcel(ByVal dataGrid As DataGridView, ByVal savePath As String, ByRef messageOut As Label)
        Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
        oexcel = CreateObject("Excel.Application")
        obook = oexcel.Workbooks.Add
        osheet = obook.Worksheets(1)

        'format Header, row index always start at 1
        For columnIndex = 1 To dataGrid.Columns.Count
            osheet.Range(intToAlphabet(columnIndex) & 1).NumberFormat = "@"
            osheet.Range(intToAlphabet(columnIndex) & 1).Value = dataGrid.Columns(columnIndex - 1).HeaderText
            Application.DoEvents()
            messageOut.Text = "Write at : Col:" & intToAlphabet(columnIndex) & " " & "Row:1"
        Next columnIndex

        'format Row cell
        For rowIndex = 0 To dataGrid.Rows.Count - 1 'row start from 2 , because row 1 is header
            For columnIndex = 0 To dataGrid.Columns.Count - 1
                If dataGrid.Columns(columnIndex).ValueType.ToString = GetType(Date).ToString Then
                    'Jika column type = DATE
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).Value = Format(dataGrid.Rows(rowIndex).Cells(columnIndex).Value(), customFormatDate)
                Else
                    'Jika column type = STRING
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).NumberFormat = "@"
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).Value = dataGrid.Rows(rowIndex).Cells(columnIndex).Value()
                End If

                Application.DoEvents()
                messageOut.Text = "Write at : Col:" & intToAlphabet(columnIndex + 1) & " " & "Row:" & rowIndex + 2
            Next columnIndex
        Next rowIndex

        oexcel.DisplayAlerts = False

        Try
            obook.SaveAs(savePath, Excel.XlFileFormat.xlOpenXMLWorkbook)
        Catch e As Exception
            MsgBox(e.Message & vbCrLf & "Process cancelled !!", vbCritical, "Abort")
        End Try

        oexcel.Quit()
        killCurrentExcel(oexcel) ' you can find this function in different article
        oexcel = Nothing
    End Sub

    Private Sub killCurrentExcel(ByVal oexcel As Object)
        Dim proc As System.Diagnostics.Process
        Dim intPID As Integer
        Dim intResult As Integer
        Dim iHandle As IntPtr
        Dim strVer As String

        strVer = oexcel.Version
        iHandle = IntPtr.Zero
        If CInt(strVer) > 9 Then
            iHandle = New IntPtr(CType(oexcel.Parent.Hwnd, Integer))
        Else
            iHandle = FindWindow(Nothing, oexcel.Caption)
        End If
        oexcel.Workbooks.Close()
        oexcel.Quit()
        Marshal.ReleaseComObject(oexcel)
        oexcel = Nothing
        intResult = GetWindowThreadProcessId(iHandle, intPID)
        proc = System.Diagnostics.Process.GetProcessById(intPID)
        proc.Kill()
    End Sub

    Private Function intToAlphabet(ByVal iCol As Integer) As String
        Dim iAlpha As Integer
        Dim iRemainder As Integer
        intToAlphabet = Nothing 'init value 

        iAlpha = Int(iCol / 27)
        iRemainder = iCol - (iAlpha * 26)
        If iAlpha > 0 Then
            intToAlphabet = Chr(iAlpha + 64)
        End If
        If iRemainder > 0 Then
            intToAlphabet = intToAlphabet & Chr(iRemainder + 64)
        End If
    End Function
End Class

To Use this class :

1. Add reference excel Microsoft Excel 12.0 Object Library.
2. Use this code :
     Dim objExportExcel As New exportExcel
     objExportExcel.exportExcel(dataGridView, "D:\pathSave", yourlabelobject)
3. and wait until file excel has been created

Read more

[VB.NET] Add button icon / image beside text

How To :

1.  Add 1 button on form
2.  Prepare an image (.png, size 16 x 16), you get the image at iconarchive.com (my favourite).
3.  Right click on button > properties , find Image and import your image (png) to resources.
4.  After the image appear on button, set imageAlign to Middle Center.
5.  Now set textAlign to Middle Right and set textImageRelation to ImageBeforeText.
6.  here's the result :

 

     
however the size of the width is changed, the text and the image stays in the middle.

ENJOY :D

Read more