SQLite connection in sharpdevelop 4

Question

I am struggling with some SQLite to VB.net code. I am using sharpdevelop 4.0 to try and connect to a SQLite database using SQLiteadmin. I have set up both and just need help in getting them to connect to each other when they mainform loads. I have put the code in mainform (see below) The database is called "KCB.db3". When i click the button i get an error message saying the connection is closed. What am i doing incorrectly?

Dim SQLconnect As New System.Data.SqlClient.SqlConnection
Dim SQLcommand As System.Data.SqlClient.SqlCommand
Dim SQLreader As System.Data.SqlClient.SqlDataReader

Sub Button1Click(sender As Object, e As EventArgs)
    'Procedure to extract records from People table in Contacts SQLite database file
    'Create an SQL command
    SQLcommand = SQLconnect.CreateCommand
    'Create SQL statement
    SQLcommand.CommandText = "SELECT * FROM Staff"
    'Extract data
    SQLreader = SQLcommand.ExecuteReader()

    While SQLreader.Read()
        'Add record to listbox
        msgbox(SQLreader("Staff_ID"))
        msgbox(SQLreader("Staff_Surname"))
        msgbox(SQLreader("Staff_First_Name"))
    End While
    'Clear SQL command buffer
    SQLcommand.Dispose()
End Sub

Sub MainFormLoad(sender As Object, e As EventArgs)
    SQLconnect.ConnectionString = "data source = KCD.db3"
    SQLconnect.Open()
End Sub

I hope someone out there can help! Thanks

When i click the button the i get an error message saying that the connection is closed. Need to get it to connect — Zac Evans, Nov 22 '13 at 10:09
Sorry my mistake, just see in SQLcommand if the connection is open. If closed just open it. — kostas ch., Nov 22 '13 at 10:55

score 0 · Answer 1 · answered Nov 24 '13 at 14:22

0

You seem to be trying to use the Microsoft Sql Server client to connect to SQLite which is not going to work.

If you install SQLite using NuGet you can then change your variables to be the following types:

Dim SQLconnect As New System.Data.SQLite.SQLiteConnection
Dim SQLcommand As System.Data.SQLite.SQLiteCommand
Dim SQLreader As System.Data.SQLite.SQLiteDataReader

Then the rest of your code should work.

The above SQLiteConnection, SQLiteCommand and SQLiteDataReader classes are in the System.Data.SQLite assembly which will be referenced after you install the SQLite NuGet package.

answered Nov 24 '13 at 14:22

Matt Ward

47,057
5
93
94

Yeah we have that already in the code above! I managed to get it working. Apparantly it is a problem with the latest version of Sharpdevelop so downgraded and it works fine! Thank you for the solution though! – Zac Evans Dec 10 '13 at 14:21
The code in the original question uses SqlClient and not the SQLite classes. If this is changed then the code should work in all versions of SharpDevelop. – Matt Ward Dec 10 '13 at 14:54

SQLite connection in sharpdevelop 4

1 Answers1