locked
Problem connecting to SQLite database RRS feed

  • Question

  • HI I have a probelm trying to open a connection to a sqlite database from Visual Basic.net - I am using Visual Studio Community 2019

    I have added a reference to Microsoft.Data.SQLite in my vb project via Nuget

    I have these 2 lines of code

    Dim MyDataConnection = New SqliteConnection("Data Source=" & PathToLocalData) 
    MyDataConnection.Open()

    The .Open() generates a nullreferenceexception

    PathToLOcalData is a variable containing the full path to the sqlite database and the file exists. I have tried creating the database as both a sqlite3 and a system.data.sqlite

    Error:
    System.NullReferenceException
      HResult=0x80004003
      Message=Object reference not set to an instance of an object.
      Source=SQLitePCLRaw.core
      StackTrace:
       at SQLitePCL.raw.sqlite3_open_v2(String filename, sqlite3& db, Int32 flags, String vfs)
       at Microsoft.Data.Sqlite.SqliteConnection.Open()
       at AspectData.Data.GetDatabaseConnection() in D:\Projects\AspectOH\AspectOH\AspectData\Data.vb:line 60

    Can anyone offer any assistance? I've searched around the coding forum examples and can't see I'm doing anything wrong.

    Friday, August 7, 2020 1:08 PM

Answers

  • Thanks to everyone for their input. After much googling I've found a solution.

    First step is to remove Microsoft.Data.Sqlite and add Microsoft.EntityFrameworkCore.Sqlite

    This does introduce a whole host of other dependencies, including Microsoft.Data.Sqlite. I then had to apply updates to 6 of the dependencies.

    Then, I added 1 line of code

    Dim MyDataConnection = New SqliteConnection("Data Source=" & PathToLocalData) 

    SQLitePCL.Batteries_v2.Init()
    MyDataConnection.Open()

    My connection now opens without error

    • Marked as answer by Joneboy Saturday, August 8, 2020 9:08 AM
    Saturday, August 8, 2020 9:08 AM

All replies

  • Hello,

    We can rule out the connection object being null being the New key word has been used to new-up the connection which leave PathToLocalData being suspect.

    Try checking first if PathToLoalData is not an empty string e.g. If Not String.IsEmptyOrWhitespace(PathToLocalData) first before newing up the connection object.

    If Not String.IsNullOrWhiteSpace(PathToLocalData) Then
        Using connection As New SqliteConnection(PathToLocalData)
            connection.Open()
        End Using
    End If
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, August 7, 2020 1:14 PM
  • Hi Karen

    Thanks for the response.

    I am checking that the db file exists before passing it in to variable, and the variable definately is populated. I've even tried specifying an absolute path

    Dim AspectLocalDataConnection = New SqliteConnection("Data Source=D:\Data\MaindataFile.db")

    and it still throws the same error.

    Jon.

    Friday, August 7, 2020 1:47 PM
  • Next thing to do is check the windows logs for errors that may provide a clear insight to the issue. If you search the web there are a lot of people with this same issue with no definitive answer that solves all of them.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, August 7, 2020 2:46 PM
  • Hi Jone,
    please check installed version. I think you include wrong version.

    try following console demo:

    Imports Microsoft.Data.Sqlite
    
    Module Module57
      Sub Main()
        Try
          Call (New Demo).Execute()
        Catch ex As Exception
          Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Continue enter key")
        Console.ReadKey()
      End Sub
      Friend Class Demo
    
        Friend Sub Execute()
          CreataTable()
          InsertRows()
          ShowResult()
        End Sub
    
        Private Sub CreataTable()
          Using cn As New SqliteConnection(My.Settings.cnLite)
            cn.Open()
            Using cmd As New SqliteCommand With {.Connection = cn}
              ' delete previous version
              cmd.CommandText = "DROP Table [Table1]"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              ' Create Tables
              cmd.CommandText = "CREATE Table [Table1]([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [ColumnA] Integer);"
              cmd.ExecuteNonQuery()
            End Using
          End Using
        End Sub
    
        Private Sub InsertRows()
          Dim parFolderName As SqliteParameter
          Using cn As New SqliteConnection(My.Settings.cnLite)
            cn.Open()
            Using cmd As New SqliteCommand("", cn)
              With cmd
                .CommandText = "INSERT INTO Table1 (ColumnA) VALUES (@ColumnA);"
                ' define parameters once
                parFolderName = .Parameters.Add("@ColumnA", SqliteType.Integer)
              End With
              For i = 1 To 50
                parFolderName.Value = i
                cmd.ExecuteNonQuery()
              Next
            End Using
          End Using
        End Sub
    
        Private Sub ShowResult()
          Using cn As New SqliteConnection(My.Settings.cnLite)
            cn.Open()
            Using cmd As New SqliteCommand("SELECT * FROM Table1 WHERE ((ColumnA & 8) = 8)", cn)
              Dim rdr = cmd.ExecuteReader
              While rdr.Read
                Dim columnA = rdr.GetInt32(1)
                Console.WriteLine($"{columnA,3} - {Hex(columnA),2}")
              End While
            End Using
          End Using
        End Sub
      End Class
    
    End Module



    --
    Best Regards / Viele Grüße

    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    Friday, August 7, 2020 3:38 PM
  • I am not familiar with VB.Net syntax (I could look) to use the VB.Net equivalent of try and catch in C# but you could do that in your code and then you could get an error message and related information. This should be done normally in your programs. You can use one try/catch for both the creation of the SqliteConnection and for the open method. You might get a SqliteException and if so then I assume you will want to see the inner exception.

    It is best practice however to determine there is a problem before getting an exception. You know that the file exists but sometimes we overlook simple details. You can use System.IO.File.Exists to ensure that the file can be found.



    Sam Hobbs
    SimpleSamples.Info


    Friday, August 7, 2020 5:58 PM
  • Using VS 2017 I created a .Net Core console project and the following are the only Nuget packages in it.

    Using that, I used the following (converted from C#) to successfully open a SQLite database.

    Dim MyDataConnection As New SqliteConnection("Data Source=C:\Sam\Documents\Miscelaneous.db")
    If MyDataConnection Is Nothing Then
    	Console.WriteLine("Null")
    	Return
    End If
    Console.WriteLine("Not null")
    Try
    	MyDataConnection.Open()
    Catch ex As Exception
    	Console.WriteLine("Error: " & ex.Message)
    	Return
    End Try
    Console.WriteLine("Opened")



    Sam Hobbs
    SimpleSamples.Info



    Friday, August 7, 2020 6:35 PM
  • Thanks to everyone for their input. After much googling I've found a solution.

    First step is to remove Microsoft.Data.Sqlite and add Microsoft.EntityFrameworkCore.Sqlite

    This does introduce a whole host of other dependencies, including Microsoft.Data.Sqlite. I then had to apply updates to 6 of the dependencies.

    Then, I added 1 line of code

    Dim MyDataConnection = New SqliteConnection("Data Source=" & PathToLocalData) 

    SQLitePCL.Batteries_v2.Init()
    MyDataConnection.Open()

    My connection now opens without error

    • Marked as answer by Joneboy Saturday, August 8, 2020 9:08 AM
    Saturday, August 8, 2020 9:08 AM
  • First step is to remove Microsoft.Data.Sqlite and add Microsoft.EntityFrameworkCore.Sqlite

    I suppose you included these wrong dll. They make the error "NullRecferenceException":


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Saturday, August 8, 2020 10:14 AM