Syntax error in UPDATE statement

[Bile]

-->>Hi everyone,I'm having this problem with updating records in my application my code is:

SQL = "UPDATE Ministers SET User_Name='" & txtUser_Name.Text & "',Password='" & txtRetype_Password.Text & "',Prefix='" & cmbPrefix.Text & "',Passport='" & NewPicture_Name & "';"

Call dbConnect
Conn.Execute SQL
Conn.Close
Set Conn = Nothing
-->>The SQL and Conn variables are declared alredy in module level
-->>Note:My Ministers table consists of Five Colums though,I just want to update only the one I stated.
-->>Iam receiving the "Syntax error in UPDATE statement" whenever I run my application
-->>Anything missing there?

[Bile]

-->>Sorry that I forgot to finish tha line of the SQL but its like this:
SQL = "UPDATE Ministers SET User_Name='" & txtUser_Name.Text & "',Password='" & txtRetype_Password.Text & "',Prefix='" & cmbPrefix.Text & "',Passport='" & NewPicture_Name & "'" & _ "WHERE ((Ministers.User_Name) LIKE '" & txtUser_Name.Text & "');"

[Nutster]

You need some whitespace before the WHERE clause.

Do you need to use LIKE in your WHERE clause? This could be a lot faster if you used an equality test. Especially if you have an index (or even better, primary key) on Minsters.User_Name.

Your comparison is against users who already match the contents of the txtUser_Name text box. You do not need to assign the value with a value that is already there.

What type of thing is NewPicture_Name? You are using its default property to assign a string to Passport. Maybe use the actual property that contains the data.

Do any of your text boxes have single quotes in them? That would cause this assembly to fail. You can add it to the validation of your text boxes.

My revision would be:

strSQL = _
    "UPDATE Ministers" & vbCr & _
    "SET Password='" & txtRetype_Password.Text & "',Prefix='" & cmbPrefix.Text & _
        "',Passport='" & NewPicture_Name & "'" & vbCr & _
    "WHERE Ministers.User_Name = '" & txtUser_Name.Text & "'"

I like using vbCr (carriage returns) as a line separator, as you can see in the above example, but any whitespace will do, such as spaces.

strSQL = _
    "UPDATE Ministers SET Password='" & txtRetype_Password.Text & "',Prefix='" & _
    cmbPrefix.Text & "',Passport='" & NewPicture_Name & _
    "' WHERE Ministers.User_Name = '" & txtUser_Name.Text & "'"

You might want to consider using a temporary QueryDef with parameters, which would get around some of that validation issue I mentioned, especially with the passport value, depending on its exact format. This method would also be faster if you are doing this call many times. I am assuming your Database object is named dbsMainDB in this example.

'At the beginning of the module
Private qdfMinister as QueryDef

'At the begninning of the Sub
If Not qdfMinister Is Nothing Then
    Set qdfMinister = dbsMainDB.CreateQueryDef("")
    qdfMinister.SQL = _
        "UPDATE Ministers" & vbCr & _
        "SET Password=[Password Text], Prefix=[Prefix Text], Passport=[Passport Value]" & vbCr & _
        "WHERE Ministers.User_Name = [User Text]"
End If

' Later on when you actually want to execute the UPDATE.
qdfMinister.Parameters("Password Text") = txtRetype_Password.Text
qdfMinister.Parameters("Prefix Text") = cmbPrefix.Text
qdfMinister.Parameters("Passport Value") = NewPicture_Name
qdfMinister.Parameters("User Text") = txtUser_Name.Text
qdfMinister.Execute dbFailOnError

[AndreRet]

SQL = "UPDATE Ministers SET Password='" & txtRetype_Password.Text & "', Prefix='" & cmbPrefix.Text & "', Passport='" & NewPicture_Name & "'" & _ " WHERE User_Name ='" & txtUser_Name.Text & "'"

Should do it nicely. tried it in your app, it worked fine....

[BitBlt]

One other little item. If you wanted to use "LIKE" to apply your selection criteria, you'd need to use a wildcard character such as an asterisk for MSAccess or a percent-sign for SQL Server to properly get all the User_Names which contain the text from the text-box.
So, for example, if you wanted to change every minister where the user name was like "Smith" (assuming the text "Smith" was in your text-box) you would have:

"... WHERE User_Name like '%" & txtUser_Name.Text & "%'" ''' this is a SQL Server example '''

or something like that. So it would change rows "TSmith", "ArthurSmithson", "SmithersJoseph" and such.

Hope this little added information is useful. Good luck.

[Bile]

-->>Thanks for the advice BitBit and Nutster the information where more useful to me,but about your code Nutster of Using Temporary QuerDef I'm afraid that I'll have to restrucure my Codes though I'm not so sure as I dont get it very Well.
-->>Andre at the 1st place I thought that may be its the way that you all guyz are trying to update,you dont start to update the User_Name='" & txtUser_Name.Text & "' as I did but after even modified the my code by starting with Password='" & txtRetype_Password.Text & "' but still have the same error.
-->>Then I got another interesting thing in your code Andre and Nutster my clouse terminates with ; but all your codes didn't I removed the ; in my code Still the same error and my I ask is it not necessary to include the ;? as most of the places I've included it in my app and it worked perfectly only here!!! I'm busting here...

[Nutster]

The semi-colon is a SQL statement seperator. It is optional at the end of the last statement.

Andre's or my code examples should work. What may cause a problem if there is a quote (') in the contents of one of the fields. You could double-up the quote to avoid this problem. This can be done by calling a function to double-up the quotes.

Somewhere outside your function, maybe in a module, create the DoubleUp function. This function is being more general then it strictly needs to be (the quote (') could be hard-coded at this level), but I like creating more general code if it does not make the code that must more complicated.

Public Function DoubleUp(strText As String, strChar As String)
    REM Double up all occurances of strChar in strText.
    REM This can be used to escape either single or double quotes.
    REM DoubleUp(strText, "'")
    REM DoubleUp(strText, """")
    Dim intPos As Integer, intOldPos As Integer
    Dim strResult As String

    REM Make sure that strChar is only one character long.
    Debug.Assert(Len(strChar) = 1)
    REM Set the variables we need, even though these are the defaults.
    intOldPos = 0
    strResult = ""
    Do
        REM Find the next occurance of strChar in strText
        intPos = Instr(intOldPos + 1, strText, strChar)
        REM If not found, get out of the loop
        If intPos < 1 Then Exit Do
        REM Include everything up to, and including, the found character.  Then add the doubled-up character.
        strResult = strResult & Mid$(strText, intOldPos + 1, intPos - intOldPos) & strChar
        REM Set intOldPos to the current position, ready for the next pass.
        intOldPos = intPos
    Loop
    strResult = strResult & Mid$(strText, intOldPos + 1)
    DoubleUp = strResult
End Function

In your function where you are creating the UPDATE string, call the DoubleUp function to protect the quotes from being misunderstood by the SQL Engine.

strSQL = _
    "UPDATE Ministers" & vbCr & _
    "SET Password='" & DoubleUp(txtRetype_Password.Text, "'") & _
    "',Prefix='" & DoubleUp(cmbPrefix.Text, "'") & _
    "',Passport='" & DoubleUp(NewPicture_Name, "'") & "'" & vbCr & _
    "WHERE Ministers.User_Name = '" & DoubleUp(txtUser_Name.Text, "'") & "'"

[Bile]

-->>I've been out for a while just going trhough some Books but I kept modifying and seeng the same error...
-->>May be this attached file will help me explain my self more...
-->>Any one with the working sugestion will be appriciated...
--And Andre I wonder why the statement didnt work on my application,how is it possible?

[AndreRet]

I'll check it in a few minutes...

[Bile]

-->>My appreciation...

[BitBlt]

Here is the actual SQL statement that executed (and I got this by setting a breakpoint in event procedure cmdUpdate in form fmUpdate_Account and doing a debug.print on the SQL variable):

UPDATE Ministers_AccountTB SET Passport='',User_Name='Angelo',Prefix='Mr.',Password='angelo1';

Please note that the name of the table does not match the name of the table in your "PICTURE SAVING.mdb" database file. Fix that, and I'd be willing to bet you have much better luck.

Please also note that if you actually tried to execute this statement, it would update EVERY ROW IN YOUR TABLE. You have to put in a "WHERE" clause to specify which row to update. Better fix that too before you trash your data.

[Bile]

-->>Yeah I saw it BitBit,sorry that I just uploaded the old version of the Application as I said that I've go through a lot of Changes but now I have to upload the New which is the one attached.
-->>But for sure I'm telling you the Error is still there just please try to go through it and whatever I'm wrong with please I'll be glad to know.

[BitBlt]

Your column name "Password" is a reserved word, so the OLEDB layer doesn't like it, and never sends it to the Jet engine. You can get around it by surrounding the name in square brackets like so:

    SQL = "UPDATE Ministers SET User_Name='" & txtUser_Name.Text & "', [Password] = '" & txtRetype_Password.Text & "', Prefix = '" & cmbPrefix.Text & "', Passport = '" & NewPicture_Name & "' WHERE User_Name = '" & txtUserName.Text & "';"

This will get it to work, but here's the thing...any time you use reserved words as column names, you're asking for trouble, especially if you're using the OLEDB engine. My advice to you is (going forward) either make it a habit to surround all column names in square brackets, or avoid using reserved words as data objects.

And, yes I did test this and it worked just fine.

[Bile]

-->>Sure I should invite you on my ceremony so that out of my dinner will yield your programming speaches!!!
-->>Thanks a lot BitBit it worked perfectly.
-->>Thak you all AndreRet,Nutster as well for your advices.