CODEWITHSUNDEEP
vb.netsqlite
Vector
Vector

Reputation: 3235

SQLite Database Creation not being Created

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

Upvotes: 1

Views: 1423

Answers (2)

G3nt_M3caj
G3nt_M3caj

Reputation: 2685

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

Upvotes: 1

Thomas G
Thomas G

Reputation: 10206

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;")

Upvotes: 1

Related Questions