Error when updating SQLite database using VB.Net

Question

Trying to get this code to work so that it will update my SQLite database. Keep getting an error saying that an end of statement is expected error BC30205. I cannot see what i am missing! This is my first ever attempt at an update statement in SQL so i may have missed something obvious! I have marked the line of code i am having the error with with an arrow!

Public Partial Class Change_Password
Public Sub New()
    ' The Me.InitializeComponent call is required for Windows Forms designer support.
    Me.InitializeComponent()

    '
    ' TODO : Add constructor code after InitializeComponents
    '
End Sub

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

Dim Password1 As String = ""
Dim Password2 As String = ""

Public Class Password
    Public shared usernamechange As String = ""
End Class

Sub Cmd_NextClick(sender As Object, e As EventArgs)
    If Trim(txt_Password_Box.Text) = "" Then
        MsgBox("Please enter a password")
    Else
        Password1 = txt_Password_Box.Text
        txt_Password_Box.Text = ""
        txt_Password_Box.Focus
        lbl_Instruction.Text = "Please re-enter the exact same password!"
        cmd_Submit.Visible = True
        cmd_Next.Visible = False
        Me.AcceptButton = cmd_Submit
    End If

End Sub

Sub Change_PasswordLoad(sender As Object, e As EventArgs)
    cmd_Submit.Visible = False  
    Me.AcceptButton = cmd_Next
    SQLconnect.ConnectionString = "Data Source=KCD.s3db;"
    SQLconnect.Open()
End Sub

Sub Cmd_SubmitClick(sender As Object, e As EventArgs)
    If Trim(txt_Password_Box.Text) = "" Then
        MsgBox("Please enter the password again")
        Exit Sub
    Else
        Password2 = txt_Password_Box.Text
        txt_Password_Box.Text = ""
    End If



    If Password1 = Password2 Then

        SQLcommand = SQLconnect.CreateCommand

------> SQLcommand.CommandText = "UPDATE Staff SET Password = '" & password1 & "' WHERE '" Username = "' & password.usernamechange & '"""



        SQLcommand.Dispose()

        MsgBox("Your password has been changed",vbInformation,"Password Changed")
        Me.Close

    Else

        MsgBox("Passwords do not match. Please try again.")
        txt_Password_Box.Focus
        cmd_Submit.Visible = False
        cmd_Next.Visible = True
        Password1 = ""
        Password2 = ""
        lbl_Instruction.Text = "Please enter a new password!"
        Me.AcceptButton = cmd_Next
    End If

End Sub
End Class

Hope someone can help me! Thanks

score 0 · Accepted Answer · answered Dec 12 '13 at 23:10

0

This line doesn't seem right. Change

SQLcommand.CommandText = "UPDATE Staff SET Password = '" & password1 & "' WHERE '" Username = "' & password.usernamechange & '"""

to

SQLcommand.CommandText = "UPDATE Staff SET Password = '" & password1 & "' WHERE Username = '" & password.usernamechange & "'"

BTW, concatenating strings like that leads to being vulnerable to SQL Injection.

answered Dec 12 '13 at 23:10

Szymon

42,577
16
96
114

Thank you! It got rid of the error but the statement doesn't actually do anything to the database! Any ideas why this might be? – Zac Evans Dec 13 '13 at 18:05
Run the select statement with the same where clause and see if it returns anything. – Szymon Dec 13 '13 at 19:22
Yeah i get data when i run a select statement on the same form! I ran this statement SQLcommand.CommandText = "SELECT Username FROM Staff WHERE Username = '" & username & "'" 'Extract data SQLreader = SQLcommand.ExecuteReader() Dim test As String = SQLreader("Username") – Zac Evans Dec 13 '13 at 20:24
Ah, ok. You were previously saying that the statement didn't return anything. But it's an update statement, it should not return anything. – Szymon Dec 13 '13 at 20:35
1

Oh okay! Apologies for that! It's just not updating a pre existing record in the database! – Zac Evans Dec 13 '13 at 20:36
It working in the database client i am working, just not when i send it from VB.Net! Is there an execute code i am missing or something? – Zac Evans Dec 13 '13 at 20:43
I added that in and still no luck! :/ If Password1 = Password2 Then SQLcommand = SQLconnect.CreateCommand SQLcommand.CommandText = "UPDATE Staff SET Password = '" & Password1 & "' WHERE Username = '" & username & "'" SQLcommand.ExecuteNonQuery() SQLcommand.Dispose() MsgBox("Your password has been changed",vbInformation,"Password Changed") Me.Close – Zac Evans Dec 13 '13 at 20:52
1

Just an update: I gave in working on it last night. Came back to it today and the code was working perfectly! Thank you very much for your help! :) Really appreciate it! – Zac Evans Dec 14 '13 at 15:58

Error when updating SQLite database using VB.Net

1 Answers1