Updates are made but I still get an update error with recordset.asp 1

[bryant89]

I am using Interdev developer and MSSQL Server 2000. I have written a trigger where when a change is made in a table(tblFirm) the trigger catches that and updates another table(tblExternalTeam). So when the firm name is updated in the firm table the trigger updates any of the same firm names that exist in the tblExternalTeam table.

Now the trigger works fine when entering in the data directly to the table.

The problem exists when I use a recordset in my interdev application to make the changes. The db is independant of the application so the trigger shouldnt matter. So if I make changes to the firm name using a recordset in the application developed with interdev I can go look in the tables and the changes are made but I get a recordset update error when using the web application even though the changes are made. The error is this:
Error Type:
Microsoft Cursor Engine (0x80004005)
Key column information is insufficient or incorrect. Too many rows were affected by update.
/networkforms/_ScriptLibrary/Recordset.ASP, line 311

This is very confusing because the update is made on the table and the trigger fires and the updates are made in the other table which should be completely independant from the application. So I know this is an interdev app problem and not a db problem. Anyone have any suggestions as to why I would be getting this recordset error? All the recordset does is update the record in the tblFirm from there the application shouldn't even know what the db(trigger) does.

Thanks in advance
Bryant

 

[foxbox]

I think there is one or more duplicate record in your tblFirm. . . br
Gerard

 

[bryant89]

nope there isnt
I checked that.
Thanks for the tip.

I think it might have something to do with referential integrity but I am a junior programmer. Not much experience yet for this guy

 

[foxbox]

In cases like this i copy/paste the ASP SQL-source into the Query Analyzer (remove the " &_ etc) and run it.
Try to change your UPDATE into a select first, to see wich records will be affected. br
Gerard

 

[bryant89]

I am not sure I know what you mean
The select statement works and the firm name is being changed. The sql works fine.

 

[foxbox]

When my ASP SQL does not work i move into Query Analyzing because that is presenting me with better/faster error output.

>but I get a recordset update error
I presume the trigger SQL is updateing the tblExternalTeam oke? Is there one key field in tblFirm?
It looks like your SQL can not select 1 single unique record in the database.... br
Gerard

 

[bryant89]

But it works fine without the trigger there so it is selecting the 1 single unique record.
The updates are all being made even though I get the recordset error.
It is really weird.

 

[MerlinB]

Make sure your recordset includes the primary key of the table - and that this column is NOT being updated.

If you look at the RECORDSET.ASP code under the bookmark methods, you will notice that the recordset tries to determine the minimum amount of information in the recordset to uniquely identify a row - if you do not include the primary key, then ALL columns may be needed to define a 'bookmark'.

Try this: Do the update on a separate command - create an update clause in your data environment, then in the ASP page write:

sub DoUpdate()
thisPage.CreateDE
DE.myUpdateClause txtNewText.value, strKey
rsMyRecordset.Requery
end sub

where 'myUpdateClause' is the command in the DataEnvironment, which accepts 2 parameters - here supplied as 'strKey', and the value of a text box 'txtNewText'.
To see the results of the update in your recordset, you will need to refresh it - here performed using a 'Requery'.

A suitable update clause may be:

UPDATE tblFirm
SET Name = ?
WHERE tblFirm.KEY = ?

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)