1

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

Improve this question
3
  • 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. Commented Aug 15, 2020 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 Commented Aug 15, 2020 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 Commented Aug 15, 2020 at 19:22

2 Answers 2

Reset to default
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;")
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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
Does your Note.db exists? Do you catch an exception? if so on which instruction and what is the message?
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
If your DB doesnt exists you can create it with SQLiteConnection.CreateFile(path). See the answer here
1

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

Improve this answer

1 Comment

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.