Re: Help needed with ADO Update

Hi Daniel,

Thanks for your thoughts.

Recently, Daniel Crichton <msnews@xxxxxxxxxxxxxxxx> posted:

Bob wrote on Wed, 2 Aug 2006 07:19:33 -0400:

Daniel Crichton wrote:
<snip>
sql="UPDATE MyDbTbl SET f1 = ?, f2 = ? WHERE Usr_ID =?"
<snip>

Operation must use an updateable query.
----------

Any ideas of why this isn't considered an "updateable query"?

No primary key on the table(s) referenced.

Good thought, but I don't think so in this case. If he was using a
recordset to perform the update then that would definitely be a
possibility (although there might be a different error message).

I've only ever seen that message when trying to update a recordset
without the table having a primary key. Without Neil posting the
actual code he's using, we can only guess at what he's doing - and
I'm guessing he's still using recordset.

Neil: please post your actual code, not snippets, because otherwise
we're all just taking stabs in the dark.

Dan

My "actual code" followed the form suggested by Bob, as in the first post
that you responded to. IOW, for this error I was not using recordset, but
the SQL update statement for the "oCmd" object. I also verified that the
SQL statement was valid via the response.write test prior to executing the
command.

As a bit of background, while I'm fairly new to using ASP, I'm not new to
SQL database dev. I've had a few commercial SQL-based RDBMS apps in the
market for over 15 years (not using Access as a db engine), so I'm
generally comfortable with such code, as well as with best practices for
validating data prior to executing SQL commands.

For this project, I'm working with a non-profit club to set up their
website, and am at the point where I need to set up online interaction
with the club's database (Access was their choice, and I also set that up
for them at the beginning of the year). All of the read-only functions
have been working well for about 4 months, and this is the first of the
write functions that I have attempted. Hopefully, once I have a working
"update" function, the same structure will be useful for add & delete
operations. So, this is largely an educational undertaking for me!

FWIW, I read the reference materials from Bob's pointer re: "...updateable
query" error, and enabled the read/write permissions on the host for the
directory containing the database. It's interesting that the read-only db
operations are unaffected by not having read permission set for the
directory, but that's just one more in an increasing list of
counter-intuitive events! I still got the same error, but it may be that
the change hasn't been registered by the host, so I'll try again in a
little while.

Thanks to both of you for help on this!

Neil


.

Relevant Pages

  • Re: REPOST: One Web Service updates SQL, the other cant
    ... insert is executed a reference to rs.eof is invalid and the program bombs. ... > get recordset back and ADO could generate error here. ... Another instance of the same Web Service code, ... >> Watching both the debugger and the trace, SQL is receiving what I send ...
    (microsoft.public.vb.database.ado)
  • Re: Help needed with ADO Update
    ... No primary key on the tablereferenced. ... I've only ever seen that message when trying to update a recordset ... what Bob's code was intended to do, as his explanation and references were ... the SQL statement is valid, ...
    (microsoft.public.data.ado)
  • Re: Help needed with ADO Update
    ... No primary key on the tablereferenced. ... I've only ever seen that message when trying to update a recordset ... explanation and references were more than adequate. ... specific SQL statements instead of using the substitution parameters ...
    (microsoft.public.data.ado)
  • How to create a temporary Primary Index for an ADO recordset
    ... My requirement is to base the updates issued from an ADO RecordSet, on SQL ... server to use the Primary Key, rather than using all the fields in the WHERE ... But is there any way without creating a Primary Key in the SQL DB to issue ...
    (microsoft.public.data.ado)
  • Re: Insufficent key column on Delete
    ... The only time i get this error is when i do not include the primary key ... field or ID field in the SQL. ... > I have and ADO recordset constructed from a join of three tables. ...
    (microsoft.public.data.ado)