CODEWITHSUNDEEP
vb.netsqlite
MichaelZ
MichaelZ

Reputation: 11

System.Data.SQLite.SQLiteException: 'SQLite error no such column: aa'

Im trying to insert data into my sqlite database which I have previously created, in vb.net:

    Using objConn As New SQLiteConnection(/my path to file is here/)

        Using objCommand As SQLiteCommand = objConn.CreateCommand()

            objConn.Open()

            objCommand.CommandText = "INSERT INTO users (username , password ) VALUES ( " + usernamevar + ",  " + passwordvar + " )"

            objCommand.ExecuteNonQuery()

            MessageBox.Show("User created successfully")

        End Using

    End Using

Usernamevar and passwordvar are values which I pass into the sub above.

However, I'm receiving an error message:

System.Data.SQLite.SQLiteException: 'SQLite error no such column: aa'

With "aa" being string passed under variable Usernamevar.

I realise I must have a syntax error somewhere in the insert phrase because I can successfully insert strings or numbers into the database, however I want to insert variables. Does anyone know how to fix it? Thanks.

Upvotes: 0

Views: 3243

Answers (1)

Mary
Mary

Reputation: 15091

You can add single quotes around the 2 string variables or you can learn to use parameters which will save you from these details and protect your databse.

objCommand.CommandText = "INSERT INTO users (username , password ) VALUES ( '" + UserNameVar + "',  '" + Passwordvar + "' )"

The easier and better way

Private Sub InsertUser(UserNameVar As String, Passwordvar As String)
    Using objConn As New SQLiteConnection("My path To file Is here")
        Using objCommand As SQLiteCommand = objConn.CreateCommand()
            objCommand.CommandText = "INSERT INTO users (username , password ) VALUES (@UserName @Password);"
            objCommand.Parameters.Add("@UserName", DbType.String).Value = UserNameVar
            objCommand.Parameters.Add("@Password", DbType.String).Value = Passwordvar
            objConn.Open()
            objCommand.ExecuteNonQuery()
            MessageBox.Show("User created successfully")
        End Using
    End Using
End Sub

I can't believe I am providing and answer with .AddWithValue but I can't seem to find an alternative where data type can be added to a method. I guess the broad implementation of types in SQLite makes it somewhat irrelevant.

EDIT Changed to .Add method which allows me to give the data type. Thank you to @ WelcomeOverflow for showing me the way.

Upvotes: 2

Related Questions