Problem with UPDATE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Seemed like the WHERE clause flaked as if it wasn’t' there at all; every
record in the table was updated.
Does any one know what went wrong? Here is the code:
Dim rst As New adodb.Recordset
rst.Open ("UPDATE Comments SET Comments.Notes = '" &
Trim(Replace(Me.txtNotes.Value, "'", "''"))"' WHERE (((Comments.ID)= " &
Me.lstNotes.Column(0, Me.lstNotes.ListIndex + 1) & "));"),
CurrentProject.Connection, adOpenStatic, adLockOptimistic

Explanation:
Columns in Comments table are: ID, Date, UserID, Notes.
lstNotes is a list box that show list of user's existing notes. User select
a note to edit. Selected note is edited in a text box named txtNotes. Save
button runs above code.

As a safety precaution, I modified the code to as follows but still don't
know what caused the disaster:
Dim myId As Long, myNote As String
myId = Val(Me.lstNotes.Column(0, Me.lstNotes.ListIndex + 1))
myNote = Trim(Replace(Me.txtNotes.Value, "'", "''"))
If myId <> 0 Then
DoCmd.RunSQL "UPDATE Comments SET Comments.Notes = '" & myNote & "' WHERE
Comments.ID= " & myId & ";"
end if
 
There's a problem with a missing ampersand, so this probably isn't the exact
code that was executed that caused the disaster. Try:

Private Sub SaveBtn_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "UPDATE Comments " & _
"SET Comments.Notes = '" & _
Trim(Replace(Me.txtNotes.Value, "'", "''")) & _
"' WHERE (((Comments.ID)= " & _
Me.lstNotes.Column(0, Me.lstNotes.ListIndex) & "));", _
dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in SaveBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' SaveBtn_Click( )

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Does any one know what went wrong? Here is the code:

Dim rst As New adodb.Recordset
rst.Open ("UPDATE Comments SET Comments.Notes = '" &
Trim(Replace(Me.txtNotes.Value, "'", "''"))"' WHERE (((Comments.ID)= "
& Me.lstNotes.Column(0, Me.lstNotes.ListIndex + 1) & "));"),
CurrentProject.Connection, adOpenStatic, adLockOptimistic
Click to expand...


Why _do_ people think it's clever or even sensible to assume that they
can build up complex queries with embedded quotes, substrings and
contcatenations all that stuff and pass it off to a database engine
without even giving themselves a chance to see if they are creating what
they are imagining they would like to create?

Just to make the point, this line is not even legal VBA and would not get
past the IDE editor... if you are going to post code, please post real
code cut-and-pasted from the editor rather than retyped from memory.
Nobody round here has time to chase down bugs introduced by typos from
long-hand copying.

How about this:

' shell out the dodgy bits first. What happens if the user
' selects the last item in the list?
With lstNotes
dwCurrentID = .Column(0, .ListIndex + 1)
End With

'
' remember that SQL is a primarily human-readable code, so
' write it like that too!
'
adoSQL = "UPDATE Comments " & vbNewLine & _
"SET Notes = " & SQLString(txtNotes.Value) & vbNewLine & _
"WHERE ID = " & dwCurrentID

'
' this is actually the most important line in the
' procedure: DONT miss it out!!
'
MsgBox adoSQL

' okay, now get the data
rst.Open adoSQL, _
CurrentProject().Connection, _
adOpenStatic, adLockOptimistic


Be kind to yourself, even if not to us. Use comments. Use spacing. Use
variables. Think what this stuff is going to look like when you (or,
even worse, your successor) try to read it in six months' time.

Best of luck



Tim F
 
Tim
Would it be possible see the code for your SQLString function?

I did copy and pasted the code but some how by accident, two Underscores
were missing. Sorry about that. I guess, 'Gocha' is the price for
inexperience (in my case).

Thanks,
Afzal
 
Would it be possible see the code for your SQLString function?
Click to expand...

try googling in these groups for my name for a tested version (with dates
too), but it's roughly this from memory:

public function sqlstring(SomeText as string) as string

' this is a double quote. Change it to 39 for a
' single quote char if you use ADO or SQL Server
const quoteChar as integer = 34

sqlstring = chr$(quoteChar) & _
replace(SomeText, Chr$(quoteChar), String(2,quoteChar)) & _
chr$(quoteChar)

end function


Hope that helps


Tim F
 
Back
Top