ADO.NET->Update Problem

[tonyroach1971]

Hi Guys/Girls,
Has anyone come across this problem before EVERYTHING
works fine until I add an expression column to the parent
datatable,
here is the structure of both parent and child tables:


===========================================================
===========================================================
======
First table:

tblInvestments:
---------------
InvestmentID
AccountCode
CompID
InvTypeID


Second Table:

tblInvestmentTypes
------------------
InvTypeID
Description


So, basically the second table(tblInvestmentTypes) is a
lookup table. I have created a relationship with the
following code:

-----------------------------------------------------------
-

dim rel as DataRelation

rel = New DataRelation("GetDescription",ds.Tables
("tblInvestmentTypes").Columns("InvTypeID"),ds.Tables
("tblInvestments").Columns("InvTypeID"))

ds.Relations.Add(rel)

-----------------------------------------------------------
-

Heres the code where I add an expression column to the
datatable tblInvestments

-----------------------------------------------------------
-
Dim dc As New DataColumn

dc.ColumnName = "Description"
dc.MaxLength = 24
dc.Expression = "Parent(GetDescription).Description"
dc.DataType = System.Type.GetType("System.String")

add the column to datatable
ds.Tables(sTableName).Columns.Add(dc)

-----------------------------------------------------------
-

This code all works fine, the expression column displays
the correct descrription.

Now, I can add/remove/modify the table parent
tblInvestments no problem, but its the next stage I get a
problem, I want to
submit back to the database any ADDED rows, heres the
code:

-----------------------------------------------------------
-
Dim tblChanges As DataTable


.........database connection code goes here...........

tblChanges = tbl.GetChanges
(DataRowState.Added) -->exception "Object
reference not set to an instance of an object."

(The tbl is a passed in parameter)
-----------------------------------------------------------
-

BUT THIS IS THE WEIRD PART, if I dont have my expression
column NO EXCEPTIONS occur AND the database gets updated
no problem?

It seems there is a problem with the expression column
being attached ?


This forum is really useful, MS are doing a great job!
David Scheppa book ADO.NET is a great
read and has helped me loads! Next revision would like to
see chapter on Writing Database Agonstic Code!



Any help would be appreciated



Tony( C,VB6,ADO )

.

 

[wyrd]

Your calculated column is referencing another table and thats not what a calculated column is for. Matter of fact I dont even think thats valid, it should be giving you an error sooner. Calculated columns are to calculate other columns within the same table on the fly. Example;

Table: Products
Column: Bought
Column: Sold
Column: InStock (calculated column; (Bought - Sold))

In any case, solving your problem is simple. Instead of using a calculated column to get the Description, use a JOIN syntax on your SELECT query;

SELECT * FROM tblInvestments i JOIN tblInvestmentTypes t ON (i.InvTypeID = t.InvTypeID)