Question when i import data from excel and dump to sqlite database Database locked error occured

Deva

Member
Joined
Feb 10, 2021
Messages
11
Programming Experience
1-3
VB.NET:
Imports System.Data.OleDb
Imports System.Data.SQLite

Public Class Form1

    Private Function saveData(sql As String)
        Dim Sqliteconn As SQLiteConnection = New SQLiteConnection("Data Source=C:\Users\deva\source\repos\Sqlite\Sqlite\bin\Debug\Config.db;version=3;")
        Dim cmd As SQLiteCommand
        Dim resul As Boolean

        Try
            Sqliteconn.Open()
            cmd = New SQLiteCommand

            With cmd
                .Connection = Sqliteconn
                .CommandText = sql

                'MsgBox(sql)
                resul = .ExecuteNonQuery()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Sqliteconn.Close()
        End Try

        Return resul
    End Function

    Dim filename As String

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        OpenFileDialog1.Filter = "All Files (*.*)|*.*| Excel |*.xlsx | Xls file |*.xls |Csv |*.csv"

        If OpenFileDialog1.ShowDialog = DialogResult.OK Then

            filename = OpenFileDialog1.FileName
            TextBox1.Text = filename

        End If
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim conn As OleDbConnection
        Dim dat As OleDbDataAdapter
        Dim dts As DataTable

        Dim sql As String
        Dim resul As Boolean

        dat = New OleDbDataAdapter
        conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0;")

        dat = New OleDbDataAdapter("select * from [sheet1$]", conn)
        dts = New DataTable

        dat.Fill(dts)

        ' DataGridView1.DataSource = dts

        For Each r As DataRow In dts.Rows
            sql = "INSERT INTO tblperson (FNAME,LNAME,ADDRESS) VALUES ('" & r(0).ToString & "','" & r(1).ToString & "','" & r(2).ToString & "')"

            resul = saveData(sql)
        Next
    End Sub
    
End Class
 
Last edited by a moderator:

Deva

Member
Joined
Feb 10, 2021
Messages
11
Programming Experience
1-3
this error is occured how to fix it
 

Attachments

  • error in dump data.png
    error in dump data.png
    55.9 KB · Views: 7

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,538
Location
Norway
Programming Experience
10+
Don't do processing that may take more than a split second in UI thread, use a secondary thread, for example use Task.Run.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,538
Location
Norway
Programming Experience
10+
There's code example in that page, you can select VB in top menu. You can also find many other examples if you search web.
 

Deva

Member
Joined
Feb 10, 2021
Messages
11
Programming Experience
1-3
There's code example in that page, you can select VB in top menu. You can also find many other examples if you search web.
i doesnot understand task.run in that page , can you help me out the above error how to simplifies to load all data

the below function only constructed sqlite query it only push a data to database

Private Function saveData(sql As String)
Dim Sqliteconn As SQLiteConnection = New SQLiteConnection("Data Source=C:\Users\deva\source\repos\Sqlite\Sqlite\bin\Debug\Config.db;version=3;")
Dim cmd As SQLiteCommand
Dim resul As Boolean

Try
Sqliteconn.Open()
cmd = New SQLiteCommand

With cmd
.Connection = Sqliteconn
.CommandText = sql

'MsgBox(sql)
resul = .ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
Sqliteconn.Close()
End Try

Return resul
End Function
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,538
Location
Norway
Programming Experience
10+
VB.NET:
Task.Run(Sub() ShowThreadInfo("Task") )
What do you need to change when:
  • method is a Function and not a Sub
  • method is named saveData and not ShowThreadInfo
  • the argument is a string variable sql and not "Task"
?
 

Deva

Member
Joined
Feb 10, 2021
Messages
11
Programming Experience
1-3
VB.NET:
Task.Run(Sub() ShowThreadInfo("Task") )
What do you need to change when:
  • method is a Function and not a Sub
  • method is named saveData and not ShowThreadInfo
  • the argument is a string variable sql and not "Task"
?
Imports System.Data.OleDb
Imports System.Data.SQLite

Public Class Form1

Private Function saveData(sql As String)
Dim Sqliteconn As SQLiteConnection = New SQLiteConnection("Data Source=C:\Users\deva\source\repos\Sqlite\Sqlite\bin\Debug\Config.db;version=3;")
Dim cmd As SQLiteCommand
Dim resul As Boolean

Try
Sqliteconn.Open()
cmd = New SQLiteCommand

With cmd
.Connection = Sqliteconn
.CommandText = sql

'MsgBox(sql)
resul = .ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
Sqliteconn.Close()
End Try

Return resul
End Function

Dim filename As String

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
OpenFileDialog1.Filter = "All Files (*.*)|*.*| Excel |*.xlsx | Xls file |*.xls |Csv |*.csv"

If OpenFileDialog1.ShowDialog = DialogResult.OK Then

filename = OpenFileDialog1.FileName
TextBox1.Text = filename

End If
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim conn As OleDbConnection
Dim dat As OleDbDataAdapter
Dim dts As DataTable

Dim sql As String
Dim resul As Boolean

dat = New OleDbDataAdapter
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0;")

dat = New OleDbDataAdapter("select * from [sheet1$]", conn)
dts = New DataTable

dat.Fill(dts)

' DataGridView1.DataSource = dts

For Each r As DataRow In dts.Rows
sql = "INSERT INTO tblperson (FNAME,LNAME,ADDRESS) VALUES ('" & r(0).ToString & "','" & r(1).ToString & "','" & r(2).ToString & "')"

resul = saveData(sql)
Next
End Sub

End Class





this is my full code , you told task.run i promise dont know were to change ? help me out where to change in my code
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,538
Location
Norway
Programming Experience
10+
This is how you post code:
insertcode.png



You should restructure code in Button2_Click by moving everything to its own method for example DBWork. In event handler just call Task.Run(AddressOf DBWork)
Within that method you could also do Task.Run for the saveData calls if you want them to run in parallel.

If you need to wait for completion/result use Async/Await, see Asynchronous Programming with Async and Await - Visual Basic
Basically you add Async keyword to Button2_Click and use Await before the Task.Run calls.
VB.NET:
Private Async Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
   Await Task.Run(AddressOf DBWork)
   'db work completed
End Sub

Sub DBWork()
   'code for db work here
End Sub
 
Top Bottom