SQLite Database Creation not being Created

Question

I was storing my SQLite DB name in App.config and using System.Configuration Reference to retrieve the DB name
I decided this was of no value so I removed the readConfig code
Now I can not create the DB and its two tables
Would someone be kind enough to review the code below and explain what I am doing wrong?

Setting in Module

Public gv_dbName As String = "Notes.db"

Declarations top level on frmStart

Public connStr As String = "Data Source={0};Version=3;"
Public conn As SqliteConnection
Dim cmd As SqliteCommand

Other Relevant Code

    Private Sub frmStart_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Icon = Icon.ExtractAssociatedIcon(Application.ExecutablePath)
    connStr = String.Format(connStr, gv_dbName)

    If My.Computer.FileSystem.FileExists(gv_dbName) Then
        btnCreate.Visible = False
        btnToEnterData.Visible = True
        btnToViewParentTable.Visible = True
    ElseIf Not My.Computer.FileSystem.FileExists(gv_dbName) Then
        conn = New SqliteConnection($"Data Source = '{gv_dbName}';Version=3;")
        tbMessage.Text = "Created Database & Tables"
    End If

End Sub

Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
    makeDB()
End Sub

Public Sub makeDB()

    If Not My.Computer.FileSystem.FileExists(gv_dbName) Then
        Try
            conn = New SqliteConnection($"Data Source = '{gv_dbName}';Version=3;")
            conn.Open()
            makeParentTable()
            makeChildTable()
            conn.Close()
        Catch ex As Exception
            tbMessage.Text = "Database NOT Created"
        End Try
    End If

End Sub

I even tried this top level of frmStart

'Friend gv_dbName As String = "Notes.db"

Here is what I based this code off of
Link to Code

Why are you not using conn = New SqliteConnection(connStr)? Does the "($"Data Source = '{gv_dbName}';Version=3;)" even work the way you expect it to, honestly first time seeing something like that. — CruleD, Aug 15 '20 at 19:13
@CruleD You are correct that line of code conn = New SqliteConnection($"Data Source = '{gv_dbName}';Version=3;") Does not fire I commented it out still no results — Vector, Aug 15 '20 at 19:20
@G3nt_M3caj Where would I put that code BIG question If the DB is not created how would the path even be known? Sorry I am lost — Vector, Aug 15 '20 at 19:22

score 1 · Answer 1 · answered Aug 15 '20 at 19:24

1

Assuming that Notes.db is in your application directory (where the exe is), that's how you open a connection on sqlite. Just remove the options you don't need.

dim strConString As String = "Data Source=Notes.db;Version=3;Pooling=True;Synchronous=Off;journal mode=Memory;foreign keys=true;;"

conn = New SQLiteConnection(strConString)
conn.Open()

if it fails on Open() you can try to add New=True in the connection string

conn = New SQLiteConnection(strConString & "New=True;")

answered Aug 15 '20 at 19:24

Thomas G

9,886
7
28
41

I added your strConString code top level on frmStart and conn = New SqliteConnection(connStr & "New=True;") under makeDB Still no results This is driving me crazy I have used similar code in another project and it works fine – Vector Aug 15 '20 at 19:40
Does your Note.db exists? Do you catch an exception? if so on which instruction and what is the message? – Thomas G Aug 15 '20 at 20:53
NO the Notes.db is not being created the fail is at makeDB but the issue is VS for some reason decided to Import Microsoft.Data.Sqlite.Core I am suing System Data SQLite Core Will try to FIX code Thanks for the help – Vector Aug 15 '20 at 21:07
If your DB doesnt exists you can create it with `SQLiteConnection.CreateFile(path)`. See the answer [here](https://stackoverflow.com/questions/24178930/programmatically-create-sqlite-db-if-it-doesnt-exist) – Thomas G Aug 15 '20 at 21:12

G3nt_M3caj · Answer 2 · 2020-08-16T13:05:55.327

Using Microsoft.Data.Sqlite.Core or System.Data.SQLite (Db Version 3) it doesn’t matter to see if file exists or not. Just give a Phisical/Absolute path, if not exist there is created a new file named.db otherwise the existed database comes back:

Example:

Friend dbPath As String = IO.Path.Combine(Application.StartupPath, "Notes.db")

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    TestMySqlite()
End Sub


Private Sub TestMySqlite()
    Try

        'You can use this but it is not important for version 3 as is auto created the new file by itself
        'If Not IO.File.Exists(dbPath) Then SQLiteConnection.CreateFile(dbPath)

        Using conn As SQLiteConnection = New SQLiteConnection("Data Source=" & dbPath & "; Version=3;")

            conn.Open()
            Console.WriteLine(conn.FileName)

            Using command As SQLiteCommand = New SQLiteCommand("CREATE TABLE IF NOT EXISTS  Test (RowIndex INTEGER PRIMARY KEY AUTOINCREMENT COLLATE NOCASE, name varchar(20))", conn)
                command.ExecuteNonQuery()
            End Using

            Using cmdInsert = New SQLiteCommand("INSERT INTO Test (name) values ('this is a test')", conn)
                cmdInsert.ExecuteNonQuery()
            End Using

        End Using

    Catch ex As Exception
        Console.WriteLine(ex.ToString)
        Stop
    End Try

End Sub

In this example is created a file (if not exist) named Notes.db under the folder your app is running. The next call SQLiteConnection just return Notes.db

See the Update to the Question Issue is solved and the code I posted works the problem is VS imported MS Data SQLite Core I still do not understand how VS manages imports in Netbeans Imports are auto generated and ask if you want to import — Vector, Aug 15 '20 at 21:29

SQLite Database Creation not being Created

2 Answers2