Reading SQLite using vb .net

Question

_{Monday, December 29, 2014 12:27 PM}

All,
I am new to VB .net and working with SQLite.  I am not able to acces Test1.db3, the code below simply creates a new file Test1.db3, therefore when I go to read the file I recieve a response saying table 'Users' doesnt exist.  I am using VS Express 2013.

    Sub Main()

        Dim Path As String = "J:\Location\SQLITE\
        Dim dataBase As String = "test1.db"
        Dim cs As String = Path & dataBase
        Dim myConnection As New SQLiteConnection()
        Dim cadeomSQL As String = "Data Source=" & cs & ";"

        myConnection.ConnectionString = cadeomSQL
        myConnection.Open()

        Dim sSQL As String = "SELECT * FROM Users"
        Using cmd As New SQLiteCommand(sSQL, myConnection)
           
            Dim rdr As SQLiteDataReader = cmd.ExecuteReader()

            Using rdr
                While (rdr.Read())
                    Console.WriteLine(rdr.GetString(0) & rdr.GetString(1) & rdr.GetString(2))
                End While
            End Using
        End Using
        myConnection.Close()
    End Sub

Any advice will be much appreciated

Thanks

All replies (11)

_{Monday, December 29, 2014 3:22 PM ✅Answered | 1 vote}

Cor,

SQL CE has been deprecated and SQL Express requires a server install. SQLite is a good alternative.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Monday, December 29, 2014 3:39 PM ✅Answered}

I don't see an issue with your code. I'm assuming the exception occurs on the ExecuteReader line of code? I would double check the database to make sure that the "Users" table does in fact exist.

Also, if test1.db has been added to your VS project make sure the Copy to Output Directory property is set to Do not copy.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Monday, December 29, 2014 5:51 PM ✅Answered}

One other suggestion, while testing your code I noticed another possible cause for the exception is a bad path to the database. I would verify that the mapped path exists and it's accessible from the application.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Tuesday, December 30, 2014 9:29 PM ✅Answered}

Paul,

No problem, firstly I created a Windows Form Application as opposed to a console application.  Not sure if this would have any affect to be honest.  Please find updated code below

DimSQLLocation AsString= "J:\VS2013\databases\test.db3"

        SQLconnect.ConnectionString = "Data Source=" & SQLLocation & ";"
        SQLconnect.Open()

       
        SQLcommand = SQLconnect.CreateCommand

        SQLcommand.CommandText = "SELECT * FROM Users"
        Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()

        While SQLreader.Read()
           
            If (String.Format(SQLreader(1))) = "Andy West" Then
                checkLicAvial((String.Format(SQLreader(2))), (String.Format(SQLreader(3))), (String.Format(SQLreader(0))))

            End If

        End While

        SQLcommand.Dispose()
        SQLconnect.Close()

Not much difference from first post but allows me to access the .db3

Thanks

Jerome 

JJ

_{Sunday, January 4, 2015 11:03 AM ✅Answered}

All,

Thought I would add to this post with a few recommendations which I have gained from my experiences.

- To load the correct SQLite dll's I would recommend using Nugets 'System.Data.SQLite Core (x86/x64)'

- This nugget creates a x64 and x86 folder in the \bin\debug\ folder and places the 'SQLite.Interop.dll' in them both.  Before Publishing you need to copy the x86 .dll to the same folder as exe.  This will enable successful creation.

Hope this is useful, please correct me if anything isn't correct or there is a better way.

Thanks

JJ

_{Monday, December 29, 2014 1:09 PM}

Firs question is why you use SQLite on a windows OS while Microsoft has complete freeware SQLCE en SQLExpress and you get tons of help for that.

Second thing is that as far as I've seen SQLite has its own dialects and uses as few databases still only ODBC. 

However start with the begin at the connectionstring.

http://www.connectionstrings.com/sqlite/

Success
Cor

_{Monday, December 29, 2014 5:37 PM}

Thanks for the information Paul

We don't have holidays wishes from you, this is an international forum so you can do that until around the fifth of January.

:-)

Success
Cor

_{Tuesday, December 30, 2014 11:49 AM}

Thanks Paul,

Thanks for your response.  You are correct the issue occurs on the ExecuteReader Line.    What I am finding is that at the 'myConnection.Open()' statement instead of opening the .db file a new database file is created in the same folder.  Therefore the table 'Users' does not exist in the new .db file.

RE:Copy to Output Directory

I don't quite understand what you mean?

Thanks

JJ

_{Tuesday, December 30, 2014 11:51 AM}

I  have checked this and the path is accessible as the application is able to create a new .db file in the designated folder.

Thanks

_{Tuesday, December 30, 2014 1:53 PM}

Paul,

I have found a very useful website which has helped me figure out a solution. 

http://www.kirupa.com/net/sqllite_vb_pg2.htm

Thanks for your assistance.

Andy

_{Tuesday, December 30, 2014 4:44 PM}

What did you do to resolve the exception? Thanks!

Paul ~~~~ Microsoft MVP (Visual Basic)