Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post

    Updating an MS record

    Hello

    What is the best way to layout your form if you wish to update a record (MS Access)? I already have a 'Add Record' form with several fields
    - is it best just to use that same form and then, in the script, use

    Code:
    sSQL="Update tableName SET fieldName WHERE .....
    This would mean completing all fields when only one field may need updating, such as an email address.

    I am proposing to use something like this:

    Code:
    'update record
    sSQL="UPDATE tblWolf " &_
     "(fullName, wolfID, telNo, address, email, description, received, [action], dispatched)" &_
     " SET ('" & my_fullName & "', " & my_wolfID & ", " & my_telNo & ", '" & address & "', '" & my_email & "', '" & my_description & "', #" & my_received & "#, '" & my_action & "', '" & my_dispatched & "')"
    
    
    connection.Execute sSQL
    ' write on screen
    Response.write "<div align='center'><p class='font'>The record has been updated.</p></div>"
    Thank you.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Believe it or not, that *is* the best way.

    It's not the only way, since you are using Access (or SQL Server), but it is the universal way that works with any DB.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    SteveH (11-13-2013)

  • #3
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    I'll try it, OP.

  • #4
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    I am getting the following error:

    Microsoft JET Database Engine error '80040e14'

    Syntax error in UPDATE statement.

    /updateRecords.asp, line 135
    The line in question is this:
    Code:
    connection.Execute sSQL
    A few sites, such as http://www.adopenstatic.com/faq/80040e14.asp#scenario2b tell me that I am using a reserved word for a field name, but looking at Microsoft's list of reserved words here:

    http://support.microsoft.com/kb/321266

    I am not using any of those words.

    I have inserted a debug statement over the culprit:

    Response.Write "<hr/>DEBUG SQL:<br/>" & sSQL & "<hr/>" & vbNewLine

    and get the following 'read out' on my Web page:

    DEBUG SQL:
    UPDATE tblWolf (fullName, wolfID, telNo, address, email, description, received, [action], dispatched) SET ('', 0, 0, '', '', '', #12:00:00 AM#, '', '')
    Does any of that make sense?

    Thanks!

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    That kind of UPDATE *ONLY* works with MySQL. (And even then you have the syntax slightly wrong.)

    For all other DBs--including Access--you must do
    Code:
    UPDATE tblWolf 
    SET fullName = '', wolfID = 0, telNo = 0, address = '', email = '', 
        description = '', received = #12:00:00 AM#, [action] = '', dispatched = ''
    WHERE primaryKeyFieldName = someParticularValue
    If you omit the WHERE, then *ALL* records in the table will be updated to the SAME VALUES for every record. DO NOT OMIT THE WHERE!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    I get the following error using that, OP:

    Microsoft VBScript compilation error '800a03ea'

    Syntax error

    /updateRecords.asp, line 133
    SET fullName = '', wolfID = 0, telNo = 0, address = '', email = '',
    ---------------^
    In any case, where you have 'someParticularValue', that value would change every time I wanted to update the database, wouldn't it, if, for example, that value represented an email address?

    I am attaching a screenshot of my database.

    Shall I post what I have for my 'Add Records', because apart from one field that file seems to be working OK.

    Steve
    Attached Thumbnails Attached Thumbnails Updating an MS record-update_codforum.jpg  


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •