How to update the account table?

Question

I need help with my update query. I need to finish this by tomorrow. I think my codes are wrong.

My form: MyForm

My database: MyDatabase

My code:

'user wants to change password
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim connection As New OleDbConnection
    connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\db1.accdb;Persist Security Info=True")

    Try
        connection.Open()

        Dim command As OleDbCommand = connection.CreateCommand
        command.CommandType = CommandType.Text
        command.CommandText = "Update UserAccount SET Password ='" + TextBox2.Text + "' WHERE Username = User"
        command.ExecuteNonQuery()

        MsgBox("Updated")z

        TextBox2.Text = ""
        TextBox3.Text = ""

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

_"i think my codes are wrong"_ is a useless problem description. Why do you think they're wrong? What are you trying to do? What did you want to happen? What happened instead? — underscore_d, Nov 12 '17 at 11:05
@codexer -- `Password` is a reserved word in VBA but not on the [list of SQL Reserved Words](https://technet.microsoft.com/en-us/library/ms189822(v=sql.110).aspx). I was able to test with it, without brackets, without issue. — ashleedawg, Nov 12 '17 at 12:58
@ashleedawg https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-E33EB3A9-8BAA-4335-9F57-DA237C63EABE please read the section **Access database engine reserved words** and let me know if Microsoft is wrong... `Password` is reserved for Access DB Engine. His first is is that being wrong, he had more, but he first needs to understand the first isse... Also hes using a `accdb` file not sql database. — Trevor, Nov 12 '17 at 15:53
@Codexer -- Hmm, yet **not** on this list from Microsoft: https://support.microsoft.com/en-us/help/286335/list-of-reserved-words-in-access-2002-and-in-later-versions-of-access and stranger that it works fine for me when querying Access from outside sources. (oh and of course, Microsoft is _never_ wrong.) I'm not saying it's a terrible idea to steer away from common words like that (even if not in use now, a word might be "reserved for later".) I'm just passing along my experience (which I just confirmed). — ashleedawg, Nov 12 '17 at 20:50
@ashleedawg - I have found that the behaviour w.r.t. reserved words can differ between, for example, OLEDB connections and ODBC connections. IIRC, OLEDB connections do indeed choke on columns named "Password" if the column name is not enclosed in square brackets (presumably because of the conflict with the `PASSWORD` keyword in the CREATE/ALTER USER statements. — Gord Thompson, Nov 12 '17 at 23:05
@ashleedawg - Further to my previous comment: I just tested and OLEDB does indeed need square brackets around `Password`, but ODBC does not. — Gord Thompson, Nov 13 '17 at 03:01
@Codexer - I made sure to connect via [ACE.OLEDB.12.0] in my test. Obviously there must be another affecting factor responsible for the different behavior - which only reinforces your point about steering away from reserved words. Personally I don't encounter that problem since I tend to prefixi variable names and such with a single letter (which doubles for classifying projects etc), and usingTheCapitalization whosName I eludesMeAt theMoment, both making any conflicts obvious. (Come to think of it, being self-taught, I have no idea how standard those practices are, or if it's just a quirk. :) — ashleedawg, Nov 13 '17 at 04:03

score -1 · Answer 1 · answered Nov 12 '17 at 12:54

-1

Common mistake. The problem is here:

...  WHERE Username = User"

It's looking for a field named User.

Is the username actually User? If so, wrap it in quotes:

...  WHERE Username = 'User'"

Is User actually a variable holding the username? If so:

...  WHERE Username = '" & User & "'"

answered Nov 12 '17 at 12:54

ashleedawg

20,365
9
72
105

re: *"User actually a variable holding the username"* - What happens when `User = "O'Malley"` ...? – Gord Thompson Nov 12 '17 at 23:01
There are a few ways to handle quotes, depending on your needs. Check out (escape strings](https://stackoverflow.com/questions/46298985/chr34-equivalent) `User = ""O'Malley""`, or you could [parameterize](https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i) your query, or a simple [replace function](https://stackoverflow.com/questions/11796973/excel-vba-using-an-apostrophe-in-an-sql-string), substituting another symbol temporarily or even removing the quotes althogether. – ashleedawg Nov 13 '17 at 03:48
An afterthought re: quotes & SQL-Injection.... yes, this is a related though [light-hearted link](https://image.ibb.co/hkmgvw/comic_db_injection_humour.png), but let it slide today... **Microsoft Access's 25th Birthday is today**! [Version 1.0](https://image.ibb.co/gnms2b/access1_0_package.jpg) was released November 13, 1992! – ashleedawg Nov 13 '17 at 08:59

How to update the account table?

1 Answers1