Different behavior under SQL 2K and SQL7

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ben (BenNOSPAM_at_NOSPAMknieff2.com)
Date: 03/06/04 

Date: Sat, 06 Mar 2004 15:23:33 GMT

Hi group -
I'm at my wits end, and I can't find anything that seems to reference my
problem.

I have an app that uses ADO to add a record to two tables. I have two
client side recordsets and I do .AddNew within a transaction.

Both recordsets are opened on the same connection using the MSDASQL.1
provider (Based off a DSN).

The operation works flawlessly when connected to SQL 2K at our office.
But at the client site it bombs with the error "Multiple step
operation..." when attempting to assign a value to the first field of
the first recordset.

Now, the kicker might be that this first field is the primary key field
and someone (not me) decided to generate random numbers for use as PK
values rather than use an identity field. So the random PK value is
produced by a function that verifies that the value is unique, returning
a VB Long, the field on the table is an SQL Int.

So, the question is, why does this thing work just fine against SQL 2K
and not against SQL 7? Did the size of Int change (I can't find anything
to this effect)? Is there some limitation in the MSDASQL.1 provider when
connecting to SQL 7?

Any thoughts and ideas are much appreciated.

Thank you,
BK

Relevant Pages

  • Re: Timeout
    ... Dim Rst as ADODB.Recordset ... rst.Open sql, cn, adOpenKeyset, adLockReadOnly <- hier kommt der timeout ... Eigenschaften Deines Recordsets schon vor dem .Open mit den richtigen Werten ... Set .Active Connection = Cnn ...
    (microsoft.public.de.vb.datenbank)
  • Re: Databases - DAO and ADO
    ... the readme files (except the one I think is in German;) ... I was very impressed with the speed comparisions between the ADO and SQLite. ... The Example I've posted does work with Recordsets. ... SQL has a lot more to offer - so why not use that builtin ...
    (microsoft.public.vb.general.discussion)
  • RE: ado connection to access vs. sql server?
    ... > data in multiple files, which needs quite careful tracking, several ADO ... > connections and many recordsets. ... > I need to perform quite tricky calculations (too tricky to be done using SQL ... > be kept practically in Excel workbooks). ...
    (microsoft.public.excel.programming)
  • Re: Operation is not allowed...
    ... Your original problem stemmed from using an ADODB.Recordset object to commit ... Recordsets are for *retrieving* data, ... backed by some of the worst SQL 2000 database design I've ever ... Well, instead of going back, fixing the typo and fixing ...
    (microsoft.public.inetserver.asp.general)
  • Re: Bizarre error causes update of one record to update ALL records
    ... > though only ID=1 was specified in the SQL where clause. ... cursor type. ... You should always include the primary key field when using ... Go back to doing it for your readonly recordsets (which are the only type ...
    (microsoft.public.inetserver.asp.db)