Error:Too many rows were affected by update 
Author Message
 Error:Too many rows were affected by update

Hi,
    I have a table named 'ordlist' ,with a trigger

CREATE TRIGGER dbo.UpdateOrdList
ON dbo.OrdList
FOR UPDATE AS
if update(item) or update(num) or update(mat)
    or update(qty) or update([description]) or update([size]) or
update(plate)
begin
    update orderoplist set orderoplist.item=ordlist.item
    from orderoplist inner join ordlist on
orderoplist.order_no=ordlist.order_no and
    orderoplist.num=ordlist.num
end

I don't use Batch (ADO.Rescordset) ,It's right

when I use batch ,the error occur "Key column information is insufficient or
incorrect. Too many rows were affected by update."

Thanks for any help.
Lou



Fri, 16 Apr 2004 14:59:41 GMT  
 Error:Too many rows were affected by update
I don't see any referances in your trigger to the "Inserted" temporary
table.

The way you are doing it now is to update EVERY record in the orderoptlist
table where those two fields are equal to the corresponding fields in the
ordlist table. You are not filtering the update to only look for those
records in the orderoptlist which related records where updated in the
ordlist table...

You need to implement it like this..

As you probably know, during an update, the updated records are in a
temporary "inserted" table and the old records are in the temporary
"deleted" table which you can access in a trigger...
=================================
CREATE TRIGGER dbo.UpdateOrdList
ON dbo.OrdList
FOR UPDATE AS

SET NOCOUNT ON

if update(item)
or update(num)
or update(mat)
or update(qty)
or update([description])
or update([size])
or update(plate)
BEGIN
    update orderoplist
        set orderoplist.item=ordlist.item
    from orderoplist inner join inserted on
(orderoplist.order_no=inserted.order_no and orderoplist.num=inserted.num)
END
==================================

HTH,
Evert

--
=======================================
My email address has been altered to
avoid unwanted email.

Replies to the newsgroup only, please.

Thanks,
Evert Timmer:
Transworld Software
VC++, VB6, SQL Server Database Solutions
http://www.transworld-software.com (under construction)
=======================================


Quote:
> Hi,
>     I have a table named 'ordlist' ,with a trigger

> CREATE TRIGGER dbo.UpdateOrdList
> ON dbo.OrdList
> FOR UPDATE AS
> if update(item) or update(num) or update(mat)
>     or update(qty) or update([description]) or update([size]) or
> update(plate)
> begin
>     update orderoplist set orderoplist.item=ordlist.item
>     from orderoplist inner join ordlist on
> orderoplist.order_no=ordlist.order_no and
>     orderoplist.num=ordlist.num
> end

> I don't use Batch (ADO.Rescordset) ,It's right

> when I use batch ,the error occur "Key column information is insufficient
or
> incorrect. Too many rows were affected by update."

> Thanks for any help.
> Lou



Fri, 16 Apr 2004 17:20:12 GMT  
 Error:Too many rows were affected by update
Thank you!

Quote:
> I don't see any referances in your trigger to the "Inserted" temporary
> table.

> The way you are doing it now is to update EVERY record in the orderoptlist
> table where those two fields are equal to the corresponding fields in the
> ordlist table. You are not filtering the update to only look for those
> records in the orderoptlist which related records where updated in the
> ordlist table...

> You need to implement it like this..

> As you probably know, during an update, the updated records are in a
> temporary "inserted" table and the old records are in the temporary
> "deleted" table which you can access in a trigger...
> =================================
> CREATE TRIGGER dbo.UpdateOrdList
> ON dbo.OrdList
> FOR UPDATE AS

> SET NOCOUNT ON

> if update(item)
> or update(num)
> or update(mat)
> or update(qty)
> or update([description])
> or update([size])
> or update(plate)
> BEGIN
>     update orderoplist
>         set orderoplist.item=ordlist.item
>     from orderoplist inner join inserted on
> (orderoplist.order_no=inserted.order_no and orderoplist.num=inserted.num)
> END
> ==================================

> HTH,
> Evert

> --
> =======================================
> My email address has been altered to
> avoid unwanted email.

> Replies to the newsgroup only, please.

> Thanks,
> Evert Timmer:
> Transworld Software
> VC++, VB6, SQL Server Database Solutions
> http://www.transworld-software.com (under construction)
> =======================================



> > Hi,
> >     I have a table named 'ordlist' ,with a trigger

> > CREATE TRIGGER dbo.UpdateOrdList
> > ON dbo.OrdList
> > FOR UPDATE AS
> > if update(item) or update(num) or update(mat)
> >     or update(qty) or update([description]) or update([size]) or
> > update(plate)
> > begin
> >     update orderoplist set orderoplist.item=ordlist.item
> >     from orderoplist inner join ordlist on
> > orderoplist.order_no=ordlist.order_no and
> >     orderoplist.num=ordlist.num
> > end

> > I don't use Batch (ADO.Rescordset) ,It's right

> > when I use batch ,the error occur "Key column information is
insufficient
> or
> > incorrect. Too many rows were affected by update."

> > Thanks for any help.
> > Lou



Sat, 17 Apr 2004 13:24:09 GMT  
 Error:Too many rows were affected by update
I am having the same error being thrown while deleting records from a
disconnected recordset. Can you PLEASE!!!! help me!

I have a recordset which is opened and then disconnected from the database

like this:

m_rec.Open "SELECT * FROM table_name", m_cnnADO, adOpenKeyset,
adLockBatchOptimistic

m_rec.ActiveConnection = Nothing

The table has two fields in it with no keys, one of which is the Primary
Key.

The recordset is opened at this point and can be navigated just fine.

The user is then able to add or delete records from this recordset. When the

data needs to be saved, I reconnect the recordset and call the UpdateBatch

method on the recordset. This works fine if the user ADDs records but it

fails if the user deletes any records. Here is how the add and delete are

performed:

Add:

With m_rec

.AddNew

!field1 = somevalue

!field2 = somevalue

.Update

End with

Delete:

With m_rec

.MoveFirst

.Find "field1 = '" & strValue & "'"

If Not .EOF Then

.Delete adAffectCurrent

End If

End with

Save Recordset:

With m_rec

.ActiveConnection = m_cnnADO

.UpdateBatch

End With

There error that is thrown is "Key column information is insufficient or

incorrect. Too many rows were affected by update.". I've done this with many

combinations of cursor types and lock types.


Quote:
> Thank you!


> > I don't see any referances in your trigger to the "Inserted" temporary
> > table.

> > The way you are doing it now is to update EVERY record in the
orderoptlist
> > table where those two fields are equal to the corresponding fields in
the
> > ordlist table. You are not filtering the update to only look for those
> > records in the orderoptlist which related records where updated in the
> > ordlist table...

> > You need to implement it like this..

> > As you probably know, during an update, the updated records are in a
> > temporary "inserted" table and the old records are in the temporary
> > "deleted" table which you can access in a trigger...
> > =================================
> > CREATE TRIGGER dbo.UpdateOrdList
> > ON dbo.OrdList
> > FOR UPDATE AS

> > SET NOCOUNT ON

> > if update(item)
> > or update(num)
> > or update(mat)
> > or update(qty)
> > or update([description])
> > or update([size])
> > or update(plate)
> > BEGIN
> >     update orderoplist
> >         set orderoplist.item=ordlist.item
> >     from orderoplist inner join inserted on
> > (orderoplist.order_no=inserted.order_no and

orderoplist.num=inserted.num)

- Show quoted text -

Quote:
> > END
> > ==================================

> > HTH,
> > Evert

> > --
> > =======================================
> > My email address has been altered to
> > avoid unwanted email.

> > Replies to the newsgroup only, please.

> > Thanks,
> > Evert Timmer:
> > Transworld Software
> > VC++, VB6, SQL Server Database Solutions
> > http://www.transworld-software.com (under construction)
> > =======================================



> > > Hi,
> > >     I have a table named 'ordlist' ,with a trigger

> > > CREATE TRIGGER dbo.UpdateOrdList
> > > ON dbo.OrdList
> > > FOR UPDATE AS
> > > if update(item) or update(num) or update(mat)
> > >     or update(qty) or update([description]) or update([size]) or
> > > update(plate)
> > > begin
> > >     update orderoplist set orderoplist.item=ordlist.item
> > >     from orderoplist inner join ordlist on
> > > orderoplist.order_no=ordlist.order_no and
> > >     orderoplist.num=ordlist.num
> > > end

> > > I don't use Batch (ADO.Rescordset) ,It's right

> > > when I use batch ,the error occur "Key column information is
> insufficient
> > or
> > > incorrect. Too many rows were affected by update."

> > > Thanks for any help.
> > > Lou



Sun, 18 Apr 2004 05:02:47 GMT  
 Error:Too many rows were affected by update
I am having the same error being thrown while deleting records from a
disconnected recordset. Can you PLEASE!!!! help me!

I have a recordset which is opened and then disconnected from the database

like this:

m_rec.Open "SELECT * FROM table_name", m_cnnADO, adOpenKeyset,
adLockBatchOptimistic

m_rec.ActiveConnection = Nothing

The table has two fields in it with no keys, one of which is the Primary
Key.

The recordset is opened at this point and can be navigated just fine.

The user is then able to add or delete records from this recordset. When the

data needs to be saved, I reconnect the recordset and call the UpdateBatch

method on the recordset. This works fine if the user ADDs records but it

fails if the user deletes any records. Here is how the add and delete are

performed:

Add:

With m_rec

.AddNew

!field1 = somevalue

!field2 = somevalue

.Update

End with

Delete:

With m_rec

.MoveFirst

.Find "field1 = '" & strValue & "'"

If Not .EOF Then

.Delete adAffectCurrent

End If

End with

Save Recordset:

With m_rec

.ActiveConnection = m_cnnADO

.UpdateBatch

End With

There error that is thrown is "Key column information is insufficient or

incorrect. Too many rows were affected by update.". I've done this with many

combinations of cursor types and lock types.


Quote:
> I don't see any referances in your trigger to the "Inserted" temporary
> table.

> The way you are doing it now is to update EVERY record in the orderoptlist
> table where those two fields are equal to the corresponding fields in the
> ordlist table. You are not filtering the update to only look for those
> records in the orderoptlist which related records where updated in the
> ordlist table...

> You need to implement it like this..

> As you probably know, during an update, the updated records are in a
> temporary "inserted" table and the old records are in the temporary
> "deleted" table which you can access in a trigger...
> =================================
> CREATE TRIGGER dbo.UpdateOrdList
> ON dbo.OrdList
> FOR UPDATE AS

> SET NOCOUNT ON

> if update(item)
> or update(num)
> or update(mat)
> or update(qty)
> or update([description])
> or update([size])
> or update(plate)
> BEGIN
>     update orderoplist
>         set orderoplist.item=ordlist.item
>     from orderoplist inner join inserted on
> (orderoplist.order_no=inserted.order_no and orderoplist.num=inserted.num)
> END
> ==================================

> HTH,
> Evert

> --
> =======================================
> My email address has been altered to
> avoid unwanted email.

> Replies to the newsgroup only, please.

> Thanks,
> Evert Timmer:
> Transworld Software
> VC++, VB6, SQL Server Database Solutions
> http://www.transworld-software.com (under construction)
> =======================================



> > Hi,
> >     I have a table named 'ordlist' ,with a trigger

> > CREATE TRIGGER dbo.UpdateOrdList
> > ON dbo.OrdList
> > FOR UPDATE AS
> > if update(item) or update(num) or update(mat)
> >     or update(qty) or update([description]) or update([size]) or
> > update(plate)
> > begin
> >     update orderoplist set orderoplist.item=ordlist.item
> >     from orderoplist inner join ordlist on
> > orderoplist.order_no=ordlist.order_no and
> >     orderoplist.num=ordlist.num
> > end

> > I don't use Batch (ADO.Rescordset) ,It's right

> > when I use batch ,the error occur "Key column information is
insufficient
> or
> > incorrect. Too many rows were affected by update."

> > Thanks for any help.
> > Lou



Sun, 18 Apr 2004 05:06:49 GMT  
 Error:Too many rows were affected by update
The error you are getting might be the same, the cause is different, I
think...

You said:

Quote:
> The table has two fields in it with no keys, one of which is the Primary
> Key.

? I don't understand this. Does the table have a primary key? (unique)

It is my belief that there is no primary key in that table and also that
there are duplicate rows in that table.

Adding a record is no problem because ADO does not have to use an index to
perform an addnew.

The situation changes as soon as the user deletes a record. When you
reconnect the recordset to the connection, and try to perform an
updatebatch, ADO will try to find the records in your database which were
deleted in the disconnected recordset. If there are multiple rows with
duplicate values, ADO is not able to determine which one to delete an then
throws an exception.

Solution:
Designate one of your fields in that table to be the primary key. Remember
that this field must be unique !! No duplicate values in that table allowed.

If none of your fields in the database are candidates to become a primary
key, you should add an extra field to that table, designate it to be the
primary key, and configure it to be an autonumber field.

This assure you, and ADO, of the fact that the rows in your table can be
uniquely identified and you're problems will be gone.

HTH,
Evert

=======================================
My email address has been altered to
avoid unwanted email.

Replies to the newsgroup only, please.

Thanks,
Evert Timmer:
Transworld Software
VC++, VB6, SQL Server Database Solutions
http://www.transworld-software.com (under construction)
=======================================


Quote:
> I am having the same error being thrown while deleting records from a
> disconnected recordset. Can you PLEASE!!!! help me!

> I have a recordset which is opened and then disconnected from the database

> like this:

> m_rec.Open "SELECT * FROM table_name", m_cnnADO, adOpenKeyset,
> adLockBatchOptimistic

> m_rec.ActiveConnection = Nothing

> The table has two fields in it with no keys, one of which is the Primary
> Key.

> The recordset is opened at this point and can be navigated just fine.

> The user is then able to add or delete records from this recordset. When
the

> data needs to be saved, I reconnect the recordset and call the UpdateBatch

> method on the recordset. This works fine if the user ADDs records but it

> fails if the user deletes any records. Here is how the add and delete are

> performed:

> Add:

> With m_rec

> .AddNew

> !field1 = somevalue

> !field2 = somevalue

> .Update

> End with

> Delete:

> With m_rec

> .MoveFirst

> .Find "field1 = '" & strValue & "'"

> If Not .EOF Then

> .Delete adAffectCurrent

> End If

> End with

> Save Recordset:

> With m_rec

> .ActiveConnection = m_cnnADO

> .UpdateBatch

> End With

> There error that is thrown is "Key column information is insufficient or

> incorrect. Too many rows were affected by update.". I've done this with
many

> combinations of cursor types and lock types.



> > I don't see any referances in your trigger to the "Inserted" temporary
> > table.

> > The way you are doing it now is to update EVERY record in the
orderoptlist
> > table where those two fields are equal to the corresponding fields in
the
> > ordlist table. You are not filtering the update to only look for those
> > records in the orderoptlist which related records where updated in the
> > ordlist table...

> > You need to implement it like this..

> > As you probably know, during an update, the updated records are in a
> > temporary "inserted" table and the old records are in the temporary
> > "deleted" table which you can access in a trigger...
> > =================================
> > CREATE TRIGGER dbo.UpdateOrdList
> > ON dbo.OrdList
> > FOR UPDATE AS

> > SET NOCOUNT ON

> > if update(item)
> > or update(num)
> > or update(mat)
> > or update(qty)
> > or update([description])
> > or update([size])
> > or update(plate)
> > BEGIN
> >     update orderoplist
> >         set orderoplist.item=ordlist.item
> >     from orderoplist inner join inserted on
> > (orderoplist.order_no=inserted.order_no and

orderoplist.num=inserted.num)

- Show quoted text -

Quote:
> > END
> > ==================================

> > HTH,
> > Evert

> > --
> > =======================================
> > My email address has been altered to
> > avoid unwanted email.

> > Replies to the newsgroup only, please.

> > Thanks,
> > Evert Timmer:
> > Transworld Software
> > VC++, VB6, SQL Server Database Solutions
> > http://www.transworld-software.com (under construction)
> > =======================================



> > > Hi,
> > >     I have a table named 'ordlist' ,with a trigger

> > > CREATE TRIGGER dbo.UpdateOrdList
> > > ON dbo.OrdList
> > > FOR UPDATE AS
> > > if update(item) or update(num) or update(mat)
> > >     or update(qty) or update([description]) or update([size]) or
> > > update(plate)
> > > begin
> > >     update orderoplist set orderoplist.item=ordlist.item
> > >     from orderoplist inner join ordlist on
> > > orderoplist.order_no=ordlist.order_no and
> > >     orderoplist.num=ordlist.num
> > > end

> > > I don't use Batch (ADO.Rescordset) ,It's right

> > > when I use batch ,the error occur "Key column information is
> insufficient
> > or
> > > incorrect. Too many rows were affected by update."

> > > Thanks for any help.
> > > Lou



Sun, 18 Apr 2004 07:16:54 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. When updating recordset I get error -2147467259 (to many rows affected by update)

2. I am trying to update a record, i am not using data control

3. I am trying to update a record, i am not using data control

4. OnCurrent affects all rows

5. Limit number of rows affected

6. Too many rows affected

7. Rows affected in the Recordset.

8. Error with DataGrid - "Too many rows affected by update"

9. Help! Updating Datagrid returns too many rows were affected by update

10. ADODC update multiple rows error

11. VB6 ADO error updating row in SQLServer7

12. ADO Error : The specified row could not be located for updating

 

 
Powered by phpBB® Forum Software