CODEWITHSUNDEEP
vb.netsqlitepasswords
Aaron
Aaron

Reputation: 3649

vb.net SQLite Password

I am using VB.net and I am creating a database. The part I can't seem to get working is how to make it have a password.

The code I am using is:

Imports Finisar.SQLite


Public Class Form1
    Const CONNECTION_STR As String = "Data Source=customers.db;Version=3;"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CreateDatabase()
    End Sub

    Private Sub CreateDatabase()
        '===================================================
        ' Create a new database and populate it with some data
        '===================================================

        'Declare the main SQLite data access objects
        Dim objConn As SQLiteConnection
        Dim objCommand As SQLiteCommand

        Try
            'Create a new database connection
            'Note - use New=True to create a new database
            objConn = New SQLiteConnection(CONNECTION_STR & "New=True;")

            'Open the connection
            objConn.Open()

            'Create a new SQL command
            objCommand = objConn.CreateCommand()

            'Setup and execute the command SQL to create a new table
            objCommand.CommandText = "CREATE TABLE customer (id integer primary key, name varchar(100));"
            objCommand.ExecuteNonQuery()

            'Insert a couple of records into the table
            objCommand.CommandText = "INSERT INTO customer (id, name) VALUES (1, 'John Smith');"
            objCommand.ExecuteNonQuery()
            objCommand.CommandText = "INSERT INTO customer (id, name) VALUES (2, 'Jane Jones');"
            objCommand.ExecuteNonQuery()

        Finally
            'Cleanup and close the connection
            If Not IsNothing(objConn) Then
                objConn.Close()
            End If
        End Try

    End Sub
End Class

The above works and creates the database but I can't get it to have a password when it creates it.

I have tried changing this line:

objConn = New SQLiteConnection(CONNECTION_STR & "New=True;")

with:

objConn = New SQLiteConnection(CONNECTION_STR & "New=True;Password=myPassword;")

Except it fails to work. I noticed that it comes up saying password is not a valid parameter.

The question I like to know how can I add a password to the database so it requires a password to open it?

EDIT: I am doing this in VB.net and NOT C#.

In C# you can use:

SQLiteConnection conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
conn.SetPassword("password");
conn.open();

But in VB.net I doesn't let me and can't work out how to do it.

Upvotes: 0

Views: 2475

Answers (1)

Hüseyin Sekmenoğlu
Hüseyin Sekmenoğlu

Reputation: 501

i tried a lot of ways and only this one worked for me:

''create db
SQLiteConnection.CreateFile(databaseName)
''sett conn details
Dim sqlConn As SQLiteConnection = New SQLiteConnection()
sqlConn.ConnectionString = "DataSource=" & databaseName & ";Version=3;New=False;Compress=True;"

''open and change password
sqlConn.Open()
sqlConn.ChangePassword("hello")
sqlConn.Close()

''next time you open db use this code:
Dim sqlConn As SQLiteConnection = New SQLiteConnection()
Using Query As New SQLiteCommand()
    sqlConn.ConnectionString = "DataSource=" & databaseName & ";Version=3;New=False;Compress=True;Password=hello;"
    sqlConn.Open()
    With Query
        .Connection = sqlConn
        .CommandText = sql
    End With
    Query.ExecuteNonQuery()
    sqlConn.Close()
End Using

Upvotes: 1

Related Questions