Problem with Expression Column when deleting all child rows.

[moondaddy]

I have a dataset which has a parent table and a child table. The parent
table has some expression columns that do things like sum the ExtPrice
column in the child table for a subtotal, calculate tax (tax rate *
subtotal), etc. My problem is when I delete the last row in the child
table:

Dim oCartItem As dsSessionMngr.CartDetailsRow =
GetCartDetailRowByCustRef(sku)
oCartItem.Delete()

Then the expression columns in the parent table don't have a value of zero
or dbnull. Instead, they have some sort of error which looks like this in
the watch window:

SubTotal <error: an exception of type: {System.Data.StrongTypingException}
occurred> Single

and here's how the SubTotal column is defined in the dataset:

<xs:attribute name="Total" type="xs:float" default="0" />

and here's how I setup the expression when I first setup (create a new
instance of...) the dataset

Note:
dsSM is the module variable of the dataset
Cart is the parent table
CartDetails is the child table
CartToCartDetails is the relationship between the 2 tables
ExtPrice is the column in the child table being summed.

dsSM.Cart.SubTotalColumn.Expression =
"Sum(Child(CartToCartDetails).ExtPrice)"

Everything works OK until I remove the last row of data in the child table.
Is there anyway to make the expression columns in the parent table (the ones
dependant on the child table...) have a value of zero when there are no rows
in the child table (similar to a sum function in sql server)?

Thanks.

 

[moondaddy]

Actually I was able to test for dbnull. I didn't think I could because I
didn't see a value of dbnull in the watch window (which threw me off because
I did see another (non-exression) column have a value of dbnull in the watch
window).

Anyway, I successfully tested for dbnull like this:

If CartRow.IsSubTotalNull = True Then
cmd.Parameters("@SO_SubTotal").Value = 0 'a parameter going to a stored
procedure in sql server
Else
cmd.Parameters("@SO_SubTotal").Value = CartRow.SubTotal
End If

BUT, I would still like to know if its possible to make these expression
columns have a value of zero and not dbnull when there's no child data.

Thanks.

 

[Steven Cheng[MSFT]]

Hi Moondaddy,

Thank you for the response. Regarding on the issue, I am
finding proper resource to assist you and we will update as soon as posible.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security(This posting is provided "AS IS",
with no warranties, and confers no rights.)

 

[Kevin Yu [MSFT]]

Hi moondaddy,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to set the value of a column
to 0 if it has no child rows exist. If there is any misunderstanding,
please feel free to let me know.

Based on my research, this can be achieved using the nullvalue attribute in
the DataSet schema. Here are the steps:

1. Open the schema design of the typed DataSet.
2. Find Total element and click on it.
3. In the property window, set 0 to NullValue attribute.
You can also set this in the schema like the following:
<xs:attribute name="Total" type="xs:float" msprop:nullValue="0" />
4. Save and rebuild project.

Now you can check the value of the column in watch windows, it will be 0
instead of null in watch window.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."