Visual Basic 2015 SQLite Update Query not working

Question

I used NuGet Package Manager to install SQLite package in my Visual Basic 2015 Project so that i can use it in my application. The problem i am facing is that i have a sub in my application whose code is indicated below that i am using to update a table in SQLite but it does not update the values.

AddErrors is another sub that adds the errors that are captured by the try catch to the same SQLite database but different table and works fine. Can you tell me what i am doing wrong.

I have tried both the commented and the uncommitted code with both not updating the database and not throwing any errors.

Public Sub PostedLink(ByVal id As String, ByVal link As String)
    Dim query As String = "UPDATE Table SET torf = 1 , link = '" & link & "' WHERE id = '" & id & "';"
    Dim affectedRows As Integer = 0
    Try
        Using con As New SQLiteConnection(connectionString)
            con.Open()
            Using cmd As New SQLiteCommand(con)
                cmd.CommandTimeout = 20
                cmd.CommandText = query
                'Dim dr As SQLiteDataReader
                'dr = cmd.ExecuteReader()
                affectedRows = cmd.ExecuteNonQuery()
            End Using
            con.Close()
        End Using
    Catch ex As Exception
        AddErrors("Updating table", ex.ToString)
    End Try
End Sub

The table i am trying to update has the following structure.

CREATE TABLE Table
(
id VARCHAR(100) PRIMARY KEY NOT NULL,
text VARCHAR(100),
link VARCHAR(254) DEFAULT "",
torf BOOLEAN NOT NULL DEFAULT 0
);

You should be using a parameterized query, currently you are vulnerable to SQL Injection, and a `'` character in your varibles will break things. See http://stackoverflow.com/questions/809246/adding-parameters-in-sqlite-with-c-sharp — Alex K., Jan 21 '17 at 19:15
If there is no exception and the update does not occur then most likely there WHERE clause matches no rows. (Disable exception catching to be 100% sure) — Alex K., Jan 21 '17 at 19:16
Your table is not named `Table`, it is `PostsList`. You have a typo. — Crowcoder, Jan 21 '17 at 19:19
And a bug in AddErrors() if the SqliteException that would raise is ignored. — Alex K., Jan 21 '17 at 20:01
I have changed the table names and variables for the question. The table name was not the problem. I changed the code as suggested by Alex and it worked. — Andinet, Jan 25 '17 at 20:16

score 0 · Answer 1 · answered May 02 '18 at 16:46

Solution by OP.

I changed the code to use parameterized query as follows and it worked flawlessly.

Public Sub PostedLink(ByVal id As String, ByVal link As String)
    Dim query As String = "UPDATE Table SET torf = 1, link = @link WHERE id = @id;"
    Dim affectedRows As Integer = 0
    Try
        Using con As New SQLiteConnection(connectionString)
            con.Open()
            Using cmd As New SQLiteCommand(con)
                cmd.CommandTimeout = 20
                cmd.CommandText = query
                With cmd.Parameters
                    .Add(New SQLiteParameter("@link", link))
                    .Add(New SQLiteParameter("@id", id))
                End With
                'Dim dr As SQLiteDataReader
                'dr = cmd.ExecuteReader()
                affectedRows = cmd.ExecuteNonQuery()
            End Using
            con.Close()
        End Using
    Catch ex As Exception
        AddErrors("Updating table", ex.ToString)
    End Try
End Sub

Visual Basic 2015 SQLite Update Query not working

1 Answers1