updateable view with a derived column no luck : allthough the Northwin

[mahaMutant]

Hi;

I am trying to create something like the Order Details Extended view in the Northwind example db on SQL server7. This view clearly has a derived column in it.
I thought [was told] that a view which contains a derived column is not updateable, but how come looking at the Northwind.adp frontend project file, this view IS updateable throught the orders FORM???

For some reason my OrderForm, which contains the ProductsSubForm, keeps telling me "field cannot be updated"?

help pleaz
] always a sucker to the greatest lie of all: will only take a minute [

 

[bperry]

Hi again:

When I mentioned the issue re: derived column, i was quoting directly from BOL. But on reflection, I can imagine that you may be allowed to have the derived column in the view, as long as you do not try to update it.
Because, clearly, SQL can't update that column if it doesn't really exist.

So two things to double-check or rule out before we go further:

(1) You are not trying to update that derived column (I realize you are prob not, but just making sure), and

(2) You are not trying to update columns in the view that belong to two different base tables. i.e. only one base table can be updated at a time.
i.e. if the view was
Select tablea.col1, tableb.col2
from tablea join tableb.....

Either col1 or col2 would be updateable at one time, but not both.


(Other forum readers: this is a continuation of thread183-265723)

 

[bperry]

P.S.
Another restriction is that the Select in the view cannot have aggregate functions in it like TOP, GROUP, UNION, DISTINCT. But I don't think you have any of those, right?

If you continue to draw a blank, then the next thing is to cut&paste into here both the view itself, and the Update statement that you are using against the view.

bperry

 

[mahaMutant]

NOTE: Northwind database on SQL Server 7
----------------------------------------

this is the view "Order Details Extended"

SELECT [Order Details].OrderID, [Order Details].ProductID,
Products.ProductName, [Order Details].UnitPrice,
[Order Details].Quantity, [Order Details].Discount,
CONVERT(money,
[Order Details].UnitPrice * [Order Details].Quantity * (1 - [Order Details].Discount)
/ 100) * 100 AS ExtendedPrice
FROM Products INNER JOIN
[Order Details] ON
Products.ProductID = [Order Details].ProductID

The SubForm "Orders Subform" has this view as a record source, and this subform is contained within a form "Orders" which has the table "Orders" as its record source. The fields "OrderId" link these 2 tables.

The view "Order Details Extended" manages to update the table "OrderDetails".

How is this possible??? ] always a sucker to the greatest lie of all: will only take a minute [

 

[bperry]

I am not familiar with Forms/SubForms in VB (don't work in that area at all), but is it possible to extract the Update statement and post it?

i.e. the idea is that if we see the View together with the Update statement, it hopefully should be straight-forward to eyeball them and see what the problem might be.

It's a shame that when it gives you the error cannot update field, it doesn't mention to you which field is causing the problem.

 

[bperry]

By the way, when you are saying 'Update', you are not really meaning 'Insert', are you? Like Update, Insert can only modify only one base table at a time in a multi-table View. This means that values can be supplied for only one table; the columns in the other table(s) must have DEFAULTS, allow NULLs, or otherwise be optional.

 

[mahaMutant]

Yep, you're right,
it's acutally INSERT I am trying to do, not update [sorry about the confusion] but still...

With regards to the update / insert statement, as this functionality is performed by an access form, I'm not sure how I can 'extract' the statement. Any ideas how to do this?

I suppose the insert functionality happens when you click on the "new record" button on a typical access form? ] always a sucker to the greatest lie of all: will only take a minute [

 

[bperry]

Yeah, I'm not sure where to go next with this Form stuff. I know at the end of the process, there has to be an SQL Insert statement in there somewhere. And probably that statement contains values for more than one table, which as we know will fail.

If we could just see the statement, I'm sure the answer will be clear. But how to see it?

Hopefully, another forum reader will jump in here with a suggestion. I'm starting to wonder if someone in the VB forum could make a suggestion.

 

[mahaMutant]

hmmm,

thanks anyway, you have helped.
cheers! ] always a sucker to the greatest lie of all: will only take a minute [

 

[mahaMutant]

AAAAAAAAAARRRGGGHHH!!!
just found it!!!

it was as you said, i tried to write to 2 tables in the subform, but it wasn't very obvious to me, as it was happening through a aggregate function. this is how i had it:
{note the 2 lookups}:

strFilter = "[products_Id] = '" & theCurrentProd & "'"
products_ShortDescription = DLookup("[products_ShortDescription]", "tbl_Products", strFilter)
orderDetails_UnitPrice = DLookup("[products_Price]", "tbl_Products", strFilter)
orderDetails_Quantity = 1

the fix:
--------
strFilter = "[products_Id] = '" & theCurrentProd & "'"
orderDetails_UnitPrice = DLookup("[products_Price]", "tbl_Products", strFilter)
orderDetails_Quantity = 1

because of relationships it was not neccessary to lookup both fields.

thanks again for all your help, hope someone else benefits from this. ] always a sucker to the greatest lie of all: will only take a minute [

 

[bperry]

That's great.
I find we only learn when things go wrong.
When things are going right, we ain't learnin' much.