Hi,

I've recently started working  with SQL Server 2000, accessing it from D6
using TADODataset.

My understanding was that you can update views: however, I'm having
difficulty with the first one and I'm not quite sure what I've missed.

The view consists of columns from two tables. One column is present in both
tables, is the key to one table, is an integer auto-generated by the
database when a record is added to that table and is a foreign key on the
corresponding column in the other table.

When I try and update the view from a new row entered in the attached
DBGrid, I get the error message "Field 'X' must have a value" - it would
appear that either the database is trying to add a row to one table before
the auto-incremented key value has been generated in the other table, or
that the auto-incremented value is being generated, but is not passed to the
other table.

What have I missed? Any help, pointers, etc. much appreciated.

Regards,

Stuart

I believe that the standard Delphi components will only update one table of
a view.

You can specify the table to update by using the 'Unique Table' dynamic
property of the recordset .

Alternatively, do not modify views. Use separate tables and windows for edit
and insert of individual rows.

--
Mike
AdoAnywhere: $10.00 Tools
http://www.adoanywhere.com

maybe you can share with us your tables structure and the view sql statment
??

Regards

    David B.E  supp...@agrosoft.co.il

news:3c837d43_2@dnews...
| I believe that the standard Delphi components will only update one table
of
| a view.

Data modification statements (INSERT and UPDATE only) are allowed on
multiple-table views if the data modification statement affects only one
base table.  DELETE statements are never allowed on multiple-table views,
because you can't use data modification statements on more than one
underlying table in a single statement.  You can never modify through a view
that's based on a UNION.

INSERT statements and NOT NULL columns INSERT statements aren't accepted
unless all the NOT NULL columns without defaults in the underlying table or
view are included in the view through which you're inserting new rows, and
values for those columns are included in the INSERT statement.  (SQL Server
has no way to supply values for NOT NULL columns in the underlying table or
view if no default value has been defined.)

--

Quidquid latine dictum sit, altum viditur.
#319

Hi again,

Thanks for your interest.

SQL creating the view:-

SELECT PolicyPrdVeh.PolicyID, PolicyPrdVeh.StartDateTime,
       PolicyPrdVeh.VehCode, PolicyPrdVeh.EndDateTime,
       PolicyPrdVeh.ValTypeCode, PolicyPrdVeh.ValAgreed,
       PolicyPrdVeh.UseMT, PolicyPrdVeh.UseSDH, PolicyPrdVeh.UsePubH,
       PolicyPrdVeh.UsePrivH, PolicyPrdVeh.UseSDP, PolicyPrdVeh.UseC1,
       PolicyPrdVeh.UseC2, PolicyPrdVeh.UseC3, PolicyPrdVeh.UseOGds,
       PolicyPrdVeh.UseHaul, PolicyPrdVeh.TPExcess,
       PolicyPrdVeh.FTDFlExcess, PolicyPrdVeh.AgeMin,
       PolicyPrdVeh.Premium,
       Vehicle.StartDate, Vehicle.RegMark,
       Vehicle.MakeYear, Vehicle.Make, Vehicle.Model, Vehicle.Deriv,
       Vehicle.MAM, Vehicle.ObjectCode
FROM   PolicyPrdVeh INNER JOIN
              Vehicle ON PolicyPrdVeh.VehCode = Vehicle.Code
WHERE (Vehicle.MAM > 3.5)

I should point out that the above is essentially what was generated by SQL
Server. My understanding is that it runs on the server and, as far as Delphi
is concerned, the TADODataSet is seeing a table - certainly .CommandType is
set to cmdTableDirect.

The original intention was that this was going to be a q&d solution to a
small temporary requirement - thus connecting a DBGrid to a table meant that
things like changing rows in the grid invoked the standard automatic Post,
without any extra work from me.

Where I *think* I've come unstuck is misunderstanding SQL Server's behaviour
when it tries to update the actual tables underlying the view. The key to
Vehicle rows is an auto-incremented integer (doesn't appear in the view) and
that value is also held in a column in PolicyPrdVeh - the colums in the two
tables are joined by a foreign key constraint.

I've assumed that I'd done enough that the value generated in the Vehicle
table (when the new row was inserted) was passed to the column in
PolicyPrdVeh when the new row was inserted in that table. However, as I said
in my first post, that this latter column doesn't have a value *seems* to be
what's generating the error.

As always, any help, pointers, etc. much appreciated.

Regards,

Stuart

Stuart
1) make sure the 2 tables has primary keys defined.
2) add the Vehicle.Code column to the view select statment.
3) before you post a new record you have to make sure there is a value in
the
    Vehicle.Code column,   auto-increment field is not a good solusion here
as you
     noticed.  you should define the new value to the integer key yourself
    you can make the assignment in the before post event.

Regards

     David  B.E        supp...@agrosoft.co.il

Hi David,

Thanks for your help: unfortunately, I think it may be in vain. I've spent
literally hours trying every possible combination of keywords I could think
of and following absolutely every link in the M$ Online Books, and I've
found ... even though a view allows selecting from multiple tables, you can
only update *one* of those tables through the view - aaaargh! :(((

Bl***y computers. They'll never catch on. Mutter, mumble, hate, kill ...

Regards,

Stuart

The issue with views has been around for ages.

1) Ensure primary key fields are in original view.
2) Create Master-Detail relations between your View and the 1/2/3 Tables
that need updating. Only active when needed.
3) Edit and insert on the detail tables. If you have standard entry screens
for an underlying table then just fire that up.

--
Mike
AdoAnywhere: $10.00 Tools
http://www.adoanywhere.com

Hi Mike,

news:3c84df5c_2@dnews...

... ready to catch out every poor newbie that comes along. :(  If it's been
an issue for so long, why don't M$ make the advice about the restriction a
little more prominent? I knew there was a good reason for using Interbase on
this project ... :((

Ok.

Where do you create the m-d's - app. level or in SQL Server?

Ok.

that up.

Nah - telling the user they've *got* to do something a certain way because
the *database* can't handle it in a more user-friendly way is too
embarassing for words. :(

Thanks for your help.

Regards,

Stuart

Create your detail table relations at application level.

The concept of using Forms to edit-Insert your database entities doesn't
have much to do with views.

Take for example an order processing system. There may be numerous occasions
where an Order number is visible to the user. By having en edit window from
which Order details can be modified allows the user to easily modify order
details when all the information isn't currently on display and centralises
the checking,security and training. Don't tell users that you do this
because of Views, tell them it is for greater flexibility and ease of use -
that should keep everyone happy (and it's true). But if you don't want to,
then do it any way that works for you.

Mike.

Hi Mike,

Tvm. :)

Stuart