SQL UPDATE query problem

#1 05-28-2002, 08:02 AM

Can any one tell me what is wrong with the statement below. If I cut and paste the SQL into query analyser the update statement works fine.

Code:

If reply2 = vbOK Then
                sql = "update username_table set password = '" & NewPassword _
                    & "' where servername = '" & SelectedServerName _
                    & "' and username = '" & selectedusername & "'"
                rs.Open sql, dbs, adOpenDynamic, adLockReadOnly
                rs.Close
            End If

#2 05-28-2002, 08:06 AM

Here is the more complete code

Code:

Private Sub Command2_Click()

    Dim dbs As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim strPath As String
    Dim ReturnedCustomerName As String
    Dim ReturnedPassword As String
    Dim ReturnedUserName As String
    Dim sql As String
    Dim WrapCharacter$
    Dim NewPassword As String
        
    NewPassword = Text1.Text
    WrapCharacter$ = Chr(13) + Chr(10)
       
    strPath = "C:\"
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strPath & "\password.mdb"
    dbs.Open strConn
 
    If DataCombo1.BoundText = "" Then
        prompt$ = "You have to select a servername from the drop down list to proceed"
        reply = MsgBox(prompt$, , "Confirm Selection")
            If reply = vbOK Then Exit Sub
    End If

    If Combo1.Text = "" Then
        prompt$ = "You have to select a username from the drop down list to proceed"
        reply = MsgBox(prompt$, , "Confirm Selection")
            If reply = vbOK Then Exit Sub
    End If
    
    If NewPassword = "" Then
        prompt$ = "You must generate or type in a new password before proceeding."
        reply = MsgBox(prompt$, , "Confirm Selection")
            If reply = vbOK Then Exit Sub
    End If
     
    selectedusername = Combo1.Text
    SelectedServerName = DataCombo1.BoundText
    
    sql = "select server_table.customername, username_table.servername, username_table.username, username_table.password from server_table inner join username_table on server_table.servername = username_table.servername where username_table.servername = '" & SelectedServerName & "' and username_table.username = '" & selectedusername & "'"
    'sql = "select * from username_table where servername ='" & SelectedServerName & "'" & " and username ='" & selectedusername & "'"
    rs.Open sql, dbs, adOpenDynamic, adLockReadOnly
    ReturnedCustomerName = rs("customername")
    rs.Close

    prompt$ = "Please confirm the server and customer name below before proceeding:" & WrapCharacter$ & WrapCharacter$ _
        & SelectedServerName & "      " & selectedusername & "      " & ReturnedCustomerName _
        & WrapCharacter$ & WrapCharacter$ & "Click OK if correct, Cancel if incorrect!"
    reply = MsgBox(prompt$, vbOKCancel, "Confirm Selection")
    If reply = vbOK Then
        prompt2$ = "Please confirm the details below, then press 'OK' to change the password or 'Cancel' to quit." _
            & WrapCharacter$ & WrapCharacter$ _
            & "     " & ReturnedCustomerName & "       " & SelectedServerName & "       " _
            & selectedusername & "       " & ReturnedPassword _
            & WrapCharacter$ & WrapCharacter$ & "with " _
            & WrapCharacter$ & WrapCharacter$ _
            & "     " & ReturnedCustomerName & "       " & SelectedServerName & "       " _
            & selectedusername & "       " & NewPassword
        reply2 = MsgBox(prompt2$, vbOKCancel, "Confirm Selection")
            If reply2 = vbOK Then
                sql = "update username_table set password = '" & NewPassword _
                    & "' where servername = '" & SelectedServerName _
                    & "' and username = '" & selectedusername & "'"
                rs.Open sql, dbs, adOpenDynamic, adLockReadOnly
                rs.Close
            End If
        
    End If
    
End Sub

Road Runner · #3 05-28-2002, 08:11 AM

Did you try this method

dbs.Execute sql

#4 05-28-2002, 08:13 AM

I will try that method.

The actual SQL from the debug info is

Code:

"update username_table set password = ';5J3q+EB5H>U=^' where servername = 'servername1' and username = 'username1'"

#5 05-28-2002, 08:15 AM

Same issue with the:

Code:

dbs.Execute sql

Method.

Road Runner · #6 05-28-2002, 08:18 AM

is it just not updating correctly or is it giving you an error message

#7 05-28-2002, 08:50 AM

I get a:

Quote:

Run-time error '2147217900 (80040e14)':

Syntax error in UPDATE statement.

Thinker · #8 05-28-2002, 08:53 AM

Your SQL looks fine, so I would try putting some fields inside of []

Code:

"update username_table set [password] = ';5J3q+EB5H>U=^' where [servername] = 'servername1' and [username] = 'username1'"

Road Runner · #9 05-28-2002, 08:56 AM

Here is what Microsoft have to say on the matter.

#10 05-28-2002, 09:05 AM

Thanks a million guys. Thinkers idea payed off. I think it was the "password" field that threw it a little. adding the square brackets worked a treat.

I seem to remember having this problem a while back.

Thanks again everyone.