VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations ADO.NET

VB.NET SQLite Tutorials for beginner : how to create SQLite connections string and CRUD (Create, Read, Update, Delete) operations example using ADO.NET connections.

VB.Net & SQLite Tutorials : How to create connection using ADO.NET and SQLite Embedded database? and how to create CRUD Examples in SQLite database? SQLite Embedded database can handle CRUD operations with ADO.NET connection, OdBc Connection as a provider to connect with SQLite database.

Please Read
  1. How to Install SQLite DataBases?
  2. How to Create Connection to SQLite Database?
  3. How to CRUD opeartions using SQL Server

CRUD Example using SQLite

How it's work?

First, we will create new database using SQLite Administration Tools and save to our project (/bin/debug), then we will make an connection using ADO.NET as a provider an create CRUD (add, Insert,Update,Delete) operations to our SQLite database.

Create New Project (CRUD SQLite)

Open your visual studio and create new project "CRUDSqlite", and at the Form1.vb add one TextBoxt, 4 Button and one DataGridView then  please design it look like this images :

Form1.Vb
VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations

 Add New form (FormADDnewData.Vb) and insert 5 TextBox and 2 Button, then design it look like this images :

FormADDNewData.Vb
 VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations

ADD SQLite .DLL Library from NuGet PackAges

we have already create all UI for our project, now we will import the SQLite library to our project before create database and connection using SQLite. Right click on you project solutions > Manage NuGet Packages for Solutions > and search for the "SQLite", read full tutorial here http://www.hc-kr.com/2016/08/vbnet-tutorials-create-connections-to.html

Create database in SQLite Embedded Database

Create new database using this query :

CREATE TABLE [tbl_biodata] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[name] VARCHAR(30)  NULL,
[nis] VARCHAR(20)  NULL,
[kelas] NVARCHAR(15)  NULL,
[alamat] VARCHAR(15)  NULL
)

Create new database like "sqlite_db.s3db"  and save it on the our project (/bin/debug), To create SQLite database, you can see full tutorial on the video below leter.

FULL Source code CRUD Operations using SQLite Database

Module Connections & Data Update Functions

Create new module from our project, and create name "ModuleConnections.Vb", then write all source code below :

Imports System.Data.SQLite
Module Moduleconnections
    ' import SQLite Library using Nuget Packages
    Public connection As SQLiteConnection
    Sub connect()
        Try
            connection = New SQLiteConnection("Data Source=crud.s3db")
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
        Catch ex As Exception
            MsgBox("Connection Failed!")
        End Try
    End Sub
    ' function for save or update and delete
    Public Sub RunSQL(ByVal sql As String)
        Try
            connect()
            Dim cmd As New SQLiteCommand
            cmd.Connection = connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            connection.Close()
            MsgBox("Data Hasbeen Saved !")
        Catch ex As Exception
            MsgBox("Failed when saving data")
        End Try
    End Sub
End Module

Source Code CRUD Operations (Form1.Vb)


Imports System.Data.SQLite
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        showData()
    End Sub

    Sub showData()
        connect()
        Dim da As New SQLiteDataAdapter("select * from tbl_biodata", connection)
        'Dim dt As New DataTable
        Dim ds As New DataSet
        da.Fill(ds, "tbl_biodata")

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "tbl_biodata"
        connection.Clone()
        da.Dispose()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' we will create save functions in our module
        Dim row, id As Integer
        Dim nama, nis, kelas, alamat As String
        ' Declare the variable to get value event click on datagridview
        row = DataGridView1.CurrentRow.Index
        id = DataGridView1(0, row).Value
        nama = DataGridView1(1, row).Value
        nis = DataGridView1(2, row).Value
        kelas = DataGridView1(3, row).Value
        alamat = DataGridView1(4, row).Value
        ' query to Update data into biodata tables
        Dim UpdateData As String = "UPDATE tbl_biodata SET name='" & nama & "',nis='" & nis & "',kelas='" & kelas & "',alamat='" & alamat & "' WHERE id=" & id & ""
        ' call function to update data
        RunSQL(UpdateData)
        ' fill new data into datagridview1
        showData()
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim id, row As Integer
        row = DataGridView1.CurrentRow.Index
        id = DataGridView1(0, row).Value
        Dim message As String
        message = MsgBox("Are you sure to delete this data? ", vbYesNo + vbInformation, "Warning")
        If message = vbNo Then
            Exit Sub
        End If
        ' query to delete data from biodata tables
        Dim DeleteData As String = "DELETE FROM tbl_biodata where id=" & id & ""
        ' call function to update data
        RunSQL(DeleteData)
        ' fill new data into datagridview1
        showData()
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        FormADDnewData.Show()
    End Sub
    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        ' search functions
        connect()
        Dim da As New SQLiteDataAdapter("SELECT * FROM tbl_biodata WHERE name like '" & TextBox1.Text & "%'", connection)
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt
        connection.Clone()
        da.Dispose()
    End Sub
End Class

Source Code Form Add New Data (FormAddNewData.Vb)


Public Class FormADDnewData
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim saveBiodata As String = "INSERT INTO tbl_biodata(id,name,nis,kelas,alamat)VALUES(" & TextBox1.Text & ",'" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "')"
        RunSQL(saveBiodata)
        With Form1
            ' refresh the gridview
            ' load new data
            .DataGridView1.Refresh()
            .showData()
        End With
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Me.Close()
    End Sub
End Class

Press "F5" keys to start debugging your simple crud applications.
CRUD (add,update,delete) Operations ADO.NET

Watch video tutorials below :

Video tutorial How to CRUD example in SQLite Database



Download Full Source code CRUD SQLite https://www.dropbox.com/s/pc27zceta27yp81/hc-kr-com-SQLiteCrudOperation.zip?dl=0
See you Next Lessons.

COMMENTS

Next Recomended


Feel free to code it up and send us a pull request.

Hi everyone, let's me know how much this lesson can help your work. Please Subscribe and Follow Our Social Media 'kodeajaib[dot]com' to get Latest tutorials and will be send to your email everyday for free!, Just hit a comment if you have confused. Nice to meet you and Happy coding :) all ^^



Follow by E-Mail


Name

ADO.NET,3,Ajax,6,Android,9,AngularJS,4,ASP.NET,4,Blogger Tutorials,7,Bootstrap,7,C++,1,Codeigniter,2,Cplusplus,6,Crystal Report,6,CSharp,25,Ebook Java,2,FlyExam,1,FSharp,3,Game Development,2,Java,35,JDBC,2,Laravel,84,Lumen,2,MariaDB,2,Ms Access,3,MySQL,31,ODBC,6,OleDB,1,PHP,14,PHP Framework,5,PHP MYSQLI,9,PHP OOP,5,Python,8,Python 3,4,SQL Server,4,SQLite,4,Uncategorized,5,Vb 6,2,Vb.Net,89,Video,48,Vue Js,4,WPF,2,Yii,3,
ltr
item
KODE AJAIB: VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations ADO.NET
VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations ADO.NET
VB.NET SQLite Tutorials for beginner : how to create SQLite connections string and CRUD (Create, Read, Update, Delete) operations example using ADO.NET connections.
https://4.bp.blogspot.com/-NCDsUYdbE38/V6ZEl6o8FSI/AAAAAAAACOc/GLVgZvDcyMMoBv6a4dOgzX_NBFLYLi8pQCLcB/s320/CRUD-create-sqlite-database-1.png
https://4.bp.blogspot.com/-NCDsUYdbE38/V6ZEl6o8FSI/AAAAAAAACOc/GLVgZvDcyMMoBv6a4dOgzX_NBFLYLi8pQCLcB/s72-c/CRUD-create-sqlite-database-1.png
KODE AJAIB
https://www.kodeajaib.com/2016/08/vbnet-crud-operations-sqlite-database.html
https://www.kodeajaib.com/
https://www.kodeajaib.com/
https://www.kodeajaib.com/2016/08/vbnet-crud-operations-sqlite-database.html
true
3214704946184383982
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS CONTENT IS PREMIUM Please share to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy