Using a recordset to update when the source is more than once table

[ainsley]

Hi,

I am trying to use ODBC and ASP with a recordset to update a record.

The sql statement I used to create the recordset spans across two tables, that have a one to one relationship (this is created by the foreign key having a unique constraint). They are connected through an inner join in my sql statement.

So I have a record set built from the two tables, and I update all the fields that I want to and it fails when it gets to the recordset.update statement - it says that:
"'Position' is an invalid field" Position is the first field from the second table in the database.

I am using an optimistic lock and adOpenKeyset.

If you have any suggestions on how I can do this (without chucking out ODBC, using a stored proc or combining the tables) I would really appriciate it. I know it sounds daft, but I am using an architecture someone else built.

Thanks,

Ainsley.

 

[bobmm]

Ainsley..

What database are you wroking against?

 

[culshaja]

You might have a problem with permissions. You might only have SELECT access on one or both of the tables.

You also might be missing a required value in one of the tables because you are not retrieving it in the query and so the AddNew/Update will not work bacause that field will not be populated.

If the query has a calculated value and/or GROUP BY then the update will also fail.

If you are using a Stored Procedure then your setting of the Cursor Type will also be overwritten, so you need to check what the run-time conditions are.

Hope this helps as a starting point.

James
James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[ainsley]

I am using SQL Server 7.0.

I'm using inline sql to create the recordset and it doesn't have a group by clause, it is as follows:

select sp.number, p.subCategory_Number, p.reference_Code, p.price, sp.graphic, sp.position, sp.page, sp.date_last_modified from tblSpecials sp inner join tblProducts p on p.number = sp.product_number where sp.number = 1

It fails to add as well as update.
It always says:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'position'.

Thanks again,

Ainsley.

 

[palbano]

Uh, excuse me...

> I am using SQL Server 7.0

Does the query run in the SQL Server Query Analyzer? If not your problem has nothing to do with ASP and you should go to the SQL Server forum here at Tek-Tips to solve your problem. There are some very sharp SQL people in that forum.

Good luck
-pete

 

[ainsley]

Pete,

The query runs and returns data in Query Analyzer. In the case above, it returns 1 record.

Thanks for your suggestion though.

The error is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

When I looked this up under microsoft's help, it says that the solution is to use a simple table and not one joined. But I heard that you can use a recordset to update this way if it is a one to one relationship.

Maybe he is mistaken.

Ainsley.

 

[uncleroydee]

I had similar problems recently, an update worked correctly in Query Analyzer, but not in my web page and not in Enterprise Manager. The only solution I could make work was to build a stored procedure that updated both tables. It worrked well.

Good luck.