Hi,
I am working with an 'in session' ado dataset with an asp.net application.
My dataset is comprised of two tables, one of which maintains a few
calculated datacolumns. For some reason these datacolumns do not trigger
their expression when other columns from which the expressions are derived
are updated. Below is a basic example of what I am doing.
User enters values into an asp.net form and clicks a button.
Retrieve dataset from session object.
Dimension a procedure level array (A) of datarows.
Dimension a single datarow (S) as equal to datatable.newro w for the relevant
datatable (T)
Iterate through the values from the webform and add them to the appropriate
datarow.items for the single datarow (S)
(at this stage I believe the expression based columns should trigger their
value - but they are not showing when watched)
Add this new row (S) to the array of datarows (A).
(Depending on values in webform this process might be repeated with further
additions to the array)
Array (A) is passed off to another procedure.
New procedure retrieves dataset from session object.
Array (A) is iterated through with additional datarow.item values being
added.
As iterated, each datarow is added to the relevant datatable as referenced
through the retrieved dataset.
(at this stage when watching the datarows, their expression is not triggered
before or after addition to the datatable)
Procedure then calls acceptchanges on dataset.
(still no expression values - all calculated column items return
system.dbnull - all other values appear as required)
---
I've included the rather extensive code for this below if anyone wants to
wade through it for me :). I belive I am doing something simple
incorrectly, but would greatly appreciate any advice or help.
Cheers
CHris
(you'll notice from the quote that i am not really passing the array from
one procedure to another, but using a readonly property from one of the
forms to access the array)
This form is where the user clicks the Insert button, it then requests the
array of datarows from the second form (a user control), and attempts to add
them to the datatable.
Private Sub InsertNow_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles InsertNow.Click
Dim InsertRows() As DataRow
Dim insertrow As DataRow
Dim QuoteDS As New quotes(Session, context)
Dim QuoteTable As DataTable = QuoteDS.TablePr od
InsertRows = CType(Me.MainPa nel.Controls(1) , ins_ITQ).Produc tRows
CType(Me.MainPa nel.Controls(1) , ins_ITQ).ClearP anel()
If InsertRows.Leng th > 0 Then
Dim maintable As DataTable = dsQuote.TablePr od
Dim maxorder As Integer
If maintable.Selec t("Groupname = '" & InsertGroup.Sel ectedItem.Text &
"'").Length > 0 Then
MaxOrder = (maintable.Comp ute("max(order) ", "groupname = '" &
InsertGroup.Sel ectedItem.Text & "'")) + 1
Else
MaxOrder = 0
End If
Dim count As Integer
For count = 0 To InsertRows.Leng th - 1
insertrow = InsertRows(coun t)
insertrow.Item( "order") = maxorder
insertrow.Item( "groupname" ) = Me.InsertGroup. SelectedItem.Te xt
QuoteTable.Rows .Add(insertrow)
insertrow.Accep tChanges()
Next
QuoteDS.DS.Acce ptChanges()
Dim lc As New LiteralControl
lc.EnableViewSt ate = False
lc.Text = "<script>window .opener.ForcePo stBack(true);</script>"
Me.Page.Control s.Add(lc)
End If
End Sub
--== This next form is the user control - i've removed uneccessary code, to
primarily show the readonly property that returns the datarow array
Public Class ins_ITQ
Inherits System.Web.UI.U serControl
Private QuoteDS As quotes
Public ReadOnly Property ProductRows() As DataRow()
Get
If Page.IsValid Then
Dim CheckedRows() As Janus.Web.GridE X.GridEXRow
CheckedRows = gdTagged.GetChe ckedRows
If CheckedRows.Len gth > 0 Then
Dim count As Int16
Dim CheckedDataRw As DataRow
Dim TaggedTable As DataTable = RetrieveTagged( )
Dim IFcrm As New crmIF.Custom.cr mIF(Net.Credent ialCache.Defaul tCredentials)
Dim prodRows(Checke dRows.Length - 1) As DataRow
For count = 0 To CheckedRows.Len gth - 1
Dim prodRow As DataRow = CType(QuoteDS.T ableProd, DataTable).NewR ow
CheckedDataRw = TaggedTable.Row s.Find(CheckedR ows(count).Data KeyValue)
prodRow.Item("c ode") = CheckedDataRw.I tem("productCod e")
prodRow.Item("v endorcode") = "" 'CheckedDataRw. item("")
prodRow.Item("d escription") = CheckedDataRw.I tem("productNam e")
prodRow.Item("d etails") = "" 'CheckedDataRw. item("")
prodRow.Item("d etailsfull") = "" 'CheckedDataRw. item("")
prodRow.Item("s howspec") = True
prodRow.Item("i mage") = "" 'CheckedDataRw. item("")
prodRow.Item("t ier1") = "" 'CheckedDataRw. item("")
prodRow.Item("t ier2") = "" 'CheckedDataRw. item("")
prodRow.Item("t ier3") = "" 'CheckedDataRw. item("")
prodRow.Item("t ier4") = "" 'CheckedDataRw. item("")
prodRow.Item("v endor") = "" 'CheckedDataRw. item("")
Dim supID As String
supID = IFcrm.ReturnAcc ountID(CheckedD ataRw.Item("sup plierName"))
If supID <> "" Then
prodRow.Item("s upplier") = supID
Else
prodRow.Item("s upplier") = "Unrecognis ed"
End If
prodRow.Item("s tock") = 0 'CheckedDataRw. item("")
'prodRow.Item(" eta") =
prodRow.Item("o rigcostex") = CheckedDataRw.I tem("costEx")
prodRow.Item("c ostex") = CheckedDataRw.I tem("costEx")
prodRow.Item("c ostinc") = CheckedDataRw.I tem("costEx") * (1 +
System.Configur ation.Configura tionSettings.Ap pSettings("gstr ate"))
prodRow.Item("s ellex") = CheckedDataRw.I tem("sellEx")
prodRow.Item("s ellinc") = CheckedDataRw.I tem("sellEx") * (1 +
System.Configur ation.Configura tionSettings.Ap pSettings("gstr ate"))
prodRow.Item("r rpex") = CheckedDataRw.I tem("rrpEx")
prodRow.Item("r rpinc") = CheckedDataRw.I tem("rrpEx") * (1 +
System.Configur ation.Configura tionSettings.Ap pSettings("gstr ate"))
prodRow.Item("b rochure") = ""
CheckedDataRw.I tem("used") = True
prodRows(count) = prodRow
Next
Return prodRows
End If
End If
End Get
End Property
--== this is the underlying class used to manage the session object dataset
(the calculated columns are prof, grpformatted)
Public Class quotes
Private session As System.Web.Sess ionState.HttpSe ssionState
Private context As HttpContext
Private crmIF1 As crmIF.Custom.cr mIF
Public ReadOnly Property TableGrp() As DataTable
Get
Return RetrieveTable(" Group")
End Get
End Property
Public ReadOnly Property TableProd() As DataTable
Get
Return RetrieveTable(" Product")
End Get
End Property
Public ReadOnly Property DS() As DataSet
Get
If IsNothing(sessi on.Item(context .User.Identity. Name & "DS")) Then
CreateDS()
End If
Return CType(session.I tem(context.Use r.Identity.Name & "DS"), DataSet)
End Get
End Property
Protected WithEvents dsQuoteProducts As System.Data.Dat aSet
Protected WithEvents Groups As System.Data.Dat aTable
Protected WithEvents order As System.Data.Dat aColumn
Protected WithEvents name As System.Data.Dat aColumn
Protected WithEvents explanation As System.Data.Dat aColumn
Protected WithEvents shwnote As System.Data.Dat aColumn
Protected WithEvents shwtotal As System.Data.Dat aColumn
Protected WithEvents pid As System.Data.Dat aColumn
Protected WithEvents Products As System.Data.Dat aTable
Protected WithEvents code As System.Data.Dat aColumn
Protected WithEvents vendorcode As System.Data.Dat aColumn
Protected WithEvents description As System.Data.Dat aColumn
Protected WithEvents qty As System.Data.Dat aColumn
Protected WithEvents details As System.Data.Dat aColumn
Protected WithEvents detailsfull As System.Data.Dat aColumn
Protected WithEvents showspec As System.Data.Dat aColumn
Protected WithEvents image As System.Data.Dat aColumn
Protected WithEvents tier1 As System.Data.Dat aColumn
Protected WithEvents tier2 As System.Data.Dat aColumn
Protected WithEvents tier3 As System.Data.Dat aColumn
Protected WithEvents tier4 As System.Data.Dat aColumn
Protected WithEvents vendor As System.Data.Dat aColumn
Protected WithEvents supplier As System.Data.Dat aColumn
Protected WithEvents stock As System.Data.Dat aColumn
Protected WithEvents eta As System.Data.Dat aColumn
Protected WithEvents origcostex As System.Data.Dat aColumn
Protected WithEvents cstex As System.Data.Dat aColumn
Protected WithEvents cstinc As System.Data.Dat aColumn
Protected WithEvents sllex As System.Data.Dat aColumn
Protected WithEvents sllinc As System.Data.Dat aColumn
Protected WithEvents rrpx As System.Data.Dat aColumn
Protected WithEvents rrpi As System.Data.Dat aColumn
Protected WithEvents ordercol As System.Data.Dat aColumn
Protected WithEvents brochure As System.Data.Dat aColumn
Protected WithEvents grpnamecol As System.Data.Dat aColumn
Protected WithEvents cidcol As System.Data.Dat aColumn
Protected WithEvents colGroupID As System.Data.Dat aColumn
Protected WithEvents groupOrder As System.Data.Dat aColumn
Protected WithEvents colgrpFormatted As System.Data.Dat aColumn
Protected WithEvents DataColumn1 As System.Data.Dat aColumn 'Declare Dataset
Public Sub New(ByVal Sess As SessionState.Ht tpSessionState, ByVal Cont As
HttpContext)
session = Sess
context = Cont
If IsNothing(Sess( Cont.User.Ident ity.Name & "DS")) Then
InitDS()
CreateDS()
End If
End Sub
Public Sub InitDS()
Me.dsQuoteProdu cts = New System.Data.Dat aSet
Me.Groups = New System.Data.Dat aTable
Me.order = New System.Data.Dat aColumn
Me.name = New System.Data.Dat aColumn
Me.explanation = New System.Data.Dat aColumn
Me.shwnote = New System.Data.Dat aColumn
Me.shwtotal = New System.Data.Dat aColumn
Me.pid = New System.Data.Dat aColumn
Me.Products = New System.Data.Dat aTable
Me.code = New System.Data.Dat aColumn
Me.vendorcode = New System.Data.Dat aColumn
Me.description = New System.Data.Dat aColumn
Me.qty = New System.Data.Dat aColumn
Me.details = New System.Data.Dat aColumn
Me.detailsfull = New System.Data.Dat aColumn
Me.showspec = New System.Data.Dat aColumn
Me.image = New System.Data.Dat aColumn
Me.tier1 = New System.Data.Dat aColumn
Me.tier2 = New System.Data.Dat aColumn
Me.tier3 = New System.Data.Dat aColumn
Me.tier4 = New System.Data.Dat aColumn
Me.vendor = New System.Data.Dat aColumn
Me.supplier = New System.Data.Dat aColumn
Me.stock = New System.Data.Dat aColumn
Me.eta = New System.Data.Dat aColumn
Me.origcostex = New System.Data.Dat aColumn
Me.cstex = New System.Data.Dat aColumn
Me.cstinc = New System.Data.Dat aColumn
Me.sllex = New System.Data.Dat aColumn
Me.sllinc = New System.Data.Dat aColumn
Me.rrpx = New System.Data.Dat aColumn
Me.rrpi = New System.Data.Dat aColumn
Me.ordercol = New System.Data.Dat aColumn
Me.grpnamecol = New System.Data.Dat aColumn
Me.cidcol = New System.Data.Dat aColumn
Me.brochure = New System.Data.Dat aColumn
Me.colGroupID = New System.Data.Dat aColumn
Me.groupOrder = New System.Data.Dat aColumn
Me.colgrpFormat ted = New System.Data.Dat aColumn
Me.DataColumn1 = New System.Data.Dat aColumn
Me.brochure = New System.Data.Dat aColumn
CType(Me.dsQuot eProducts,
System.Componen tModel.ISupport Initialize).Beg inInit()
CType(Me.Groups , System.Componen tModel.ISupport Initialize).Beg inInit()
CType(Me.Produc ts, System.Componen tModel.ISupport Initialize).Beg inInit()
'
'dsQuoteProduct s
'
Me.dsQuoteProdu cts.DataSetName = "dsQuoteProduct s"
Me.dsQuoteProdu cts.Locale = New System.Globaliz ation.CultureIn fo("en-AU")
Me.dsQuoteProdu cts.Relations.A ddRange(New System.Data.Dat aRelation() {New
System.Data.Dat aRelation("Rela tion1", "Groups", "Products", New String()
{"name"}, New String() {"groupname" }, False), New
System.Data.Dat aRelation("Rela tion2", "Groups", "Products", New String()
{"id"}, New String() {"groupid"}, False), New
System.Data.Dat aRelation("Rela tion3", "Groups", "Products", New String()
{"order"}, New String() {"groupOrder "}, False)})
Me.dsQuoteProdu cts.Tables.AddR ange(New System.Data.Dat aTable() {Me.Groups,
Me.Products})
'
'Groups
'
Me.Groups.Colum ns.AddRange(New System.Data.Dat aColumn() {Me.order, Me.name,
Me.explanation, Me.shwnote, Me.shwtotal, Me.pid})
Me.Groups.Const raints.AddRange (New System.Data.Con straint() {New
System.Data.Uni queConstraint(" Constraint1", New String() {"order"}, False),
New System.Data.Uni queConstraint(" Constraint2", New String() {"id"}, True),
New System.Data.Uni queConstraint(" Constraint3", New String() {"name"},
False)})
Me.Groups.Prima ryKey = New System.Data.Dat aColumn() {Me.pid}
Me.Groups.Table Name = "Groups"
'
'order
'
Me.order.AllowD BNull = False
Me.order.Captio n = "order"
Me.order.Column Name = "order"
Me.order.DataTy pe = GetType(System. Int32)
'
'name
'
Me.name.AllowDB Null = False
Me.name.Caption = "name"
Me.name.ColumnN ame = "name"
'
'Brochure
Me.brochure.All owDBNull = True
Me.brochure.Cap tion = "brochure"
Me.brochure.Dat aType = GetType(System. String)
'explanation
'
Me.explanation. ColumnName = "explanatio n"
'
'shwnote
'
Me.shwnote.Allo wDBNull = False
Me.shwnote.Colu mnName = "shwnote"
Me.shwnote.Data Type = GetType(System. Boolean)
Me.shwnote.Defa ultValue = False
'
'shwtotal
'
Me.shwtotal.All owDBNull = False
Me.shwtotal.Col umnName = "shwtotal"
Me.shwtotal.Dat aType = GetType(System. Boolean)
Me.shwtotal.Def aultValue = False
'
'id
'
Me.pid.AllowDBN ull = False
Me.pid.AutoIncr ement = True
Me.pid.Caption = "id"
Me.pid.ColumnNa me = "id"
Me.pid.DataType = GetType(System. Int32)
'
'Products
'
Me.Products.Col umns.AddRange(N ew System.Data.Dat aColumn() {Me.code,
Me.vendorcode, Me.description, Me.qty, Me.details, Me.detailsfull,
Me.showspec, Me.image, Me.tier1, Me.tier2, Me.tier3, Me.tier4, Me.vendor,
Me.supplier, Me.stock, Me.eta, Me.origcostex, Me.cstex, Me.cstinc, Me.sllex,
Me.sllinc, Me.rrpx, Me.rrpi, Me.ordercol, Me.grpnamecol, Me.cidcol,
Me.colGroupID, Me.groupOrder, Me.colgrpFormat ted, Me.DataColumn1,
Me.brochure})
Me.Products.Con straints.AddRan ge(New System.Data.Con straint() {New
System.Data.Uni queConstraint(" Constraint1", New String() {"cid"}, True), New
System.Data.For eignKeyConstrai nt("Relation1" , "Groups", New String()
{"name"}, New String() {"groupname" }, System.Data.Acc eptRejectRule.N one,
System.Data.Rul e.Cascade, System.Data.Rul e.Cascade), New
System.Data.For eignKeyConstrai nt("Relation2" , "Groups", New String() {"id"},
New String() {"groupid"}, System.Data.Acc eptRejectRule.N one,
System.Data.Rul e.Cascade, System.Data.Rul e.Cascade), New
System.Data.For eignKeyConstrai nt("Relation3" , "Groups", New String()
{"order"}, New String() {"groupOrder "}, System.Data.Acc eptRejectRule.N one,
System.Data.Rul e.Cascade, System.Data.Rul e.Cascade)})
Me.Products.Pri maryKey = New System.Data.Dat aColumn() {Me.cidcol}
Me.Products.Tab leName = "Products"
'
'type
'
'
'code
Me.brochure.All owDBNull = True
Me.brochure.Col umnName = "brochure"
'
Me.code.AllowDB Null = False
Me.code.ColumnN ame = "code"
'
'vendorcode
'
Me.vendorcode.C olumnName = "vendorcode "
'
'description
'
Me.description. ColumnName = "descriptio n"
'
'qty
'
Me.qty.AllowDBN ull = False
Me.qty.ColumnNa me = "qty"
Me.qty.DataType = GetType(System. Int16)
Me.qty.DefaultV alue = CType(1, Short)
'
'details
'
Me.details.Colu mnName = "details"
'
'detailsfull
'
Me.detailsfull. ColumnName = "detailsful l"
'
'showspec
'
Me.showspec.Col umnName = "showspec"
Me.showspec.Dat aType = GetType(System. Boolean)
'
'image
'
Me.image.Column Name = "image"
'
'tier1
'
Me.tier1.Column Name = "tier1"
'
'tier2
'
Me.tier2.Column Name = "tier2"
'
'tier3
'
Me.tier3.Column Name = "tier3"
'
'tier4
'
Me.tier4.Column Name = "tier4"
'
'vendor
'
Me.vendor.Colum nName = "vendor"
'
'supplier
'
Me.supplier.Col umnName = "supplier"
'
'stock
'
Me.stock.Column Name = "stock"
Me.stock.DataTy pe = GetType(System. Int32)
'
'eta
'
Me.eta.ColumnNa me = "eta"
Me.eta.DataType = GetType(System. DateTime)
Me.eta.AllowDBN ull = True
'
'origcostex
'
Me.origcostex.C olumnName = "origcostex "
Me.origcostex.D ataType = GetType(System. Decimal)
'
'cstex
'
Me.cstex.Column Name = "costex"
Me.cstex.DataTy pe = GetType(System. Decimal)
'
'cstinc
'
Me.cstinc.Capti on = "costinc"
Me.cstinc.Colum nName = "costinc"
Me.cstinc.DataT ype = GetType(System. Decimal)
'
'sllex
'
Me.sllex.Column Name = "sellex"
Me.sllex.DataTy pe = GetType(System. Decimal)
'
'sllinc
'
Me.sllinc.Colum nName = "sellinc"
Me.sllinc.DataT ype = GetType(System. Decimal)
'
'rrpx
'
Me.rrpx.ColumnN ame = "rrpex"
Me.rrpx.DataTyp e = GetType(System. Decimal)
'
'rrpi
'
Me.rrpi.ColumnN ame = "rrpinc"
Me.rrpi.DataTyp e = GetType(System. Decimal)
'
'ordercol
'
Me.ordercol.All owDBNull = False
Me.ordercol.Col umnName = "order"
Me.ordercol.Dat aType = GetType(System. Int32)
'
'grpnamecol
'
Me.grpnamecol.A llowDBNull = False
Me.grpnamecol.C olumnName = "groupname"
'
'cidcol
'
Me.cidcol.Allow DBNull = False
Me.cidcol.AutoI ncrement = True
Me.cidcol.Capti on = "cid"
Me.cidcol.Colum nName = "cid"
Me.cidcol.DataT ype = GetType(System. Int32)
'
'colGroupID
'
Me.colGroupID.C olumnName = "groupid"
Me.colGroupID.D ataType = GetType(System. Int32)
'
'groupOrder
'
Me.groupOrder.C olumnName = "groupOrder "
Me.groupOrder.D ataType = GetType(System. Int32)
'
'colgrpFormatte d
'
Me.colgrpFormat ted.ColumnName = "grpFormatt ed"
Me.colgrpFormat ted.Expression = "'[' + iif(groupOrder > 9, groupOrder, '0' +
groupOrder) + '] ' + groupname"
Me.colgrpFormat ted.ReadOnly = True
'
'DataColumn1
'
Me.DataColumn1. ColumnName = "prof"
Me.DataColumn1. DataType = GetType(System. Decimal)
Me.DataColumn1. Expression = "qty*costex "
Me.DataColumn1. ReadOnly = True
CType(Me.dsQuot eProducts,
System.Componen tModel.ISupport Initialize).End Init()
CType(Me.Groups , System.Componen tModel.ISupport Initialize).End Init()
CType(Me.Produc ts, System.Componen tModel.ISupport Initialize).End Init()
End Sub
Private Sub CreateDS()
If IsNothing(sessi on.Item(context .User.Identity. Name & "DS")) Then
session.Add(con text.User.Ident ity.Name & "DS", Me.dsQuoteProdu cts)
End If
CreateGroupTabl e()
End Sub
Private Sub CreateGroupTabl e()
Dim GroupTable As DataTable
Dim GroupKey(0) As DataColumn
Dim drGroup As DataRow
GroupTable = RetrieveTable(" Group")
drGroup = GroupTable.NewR ow
drGroup.Item(0) = 0
drGroup.Item(1) = "Unassigned "
drGroup.Item(2) = ""
drGroup.Item(3) = False
drGroup.Item(4) = False
GroupTable.Rows .Add(drGroup)
End Sub
Private Function RetrieveTable(B yVal TableName As String) As DataTable
Select Case TableName
Case "Group"
If IsNothing(sessi on.Item(context .User.Identity. Name & "DS")) Then
CreateDS()
End If
Return CType(session.I tem(context.Use r.Identity.Name & "DS"),
DataSet).Tables ("Groups")
Case "Product"
If IsNothing(sessi on.Item(context .User.Identity. Name & "DS")) Then
CreateDS()
End If
Return CType(session.I tem(context.Use r.Identity.Name & "DS"),
DataSet).Tables ("Products")
End Select
End Function
Public Function RetrieveSupplie rs(ByVal UserCred As Net.NetworkCred ential,
ByVal Refresh As Boolean) As DataSet
If IsNothing(sessi on.Item("Suppli ers")) Or Refresh = True Then
crmIF1 = New crmIF.Custom.cr mIF(UserCred)
Dim qResults = crmIF1.ExecuteQ uery("<fetch mapping='logica l'><entity
name='account'> <all-attributes/><filter type='and'><con dition attribute =
'customertypeco de' operator='eq' value='10'/></filter></entity></fetch>")
Dim dsSuppliers As New DataSet
dsSuppliers.Rea dXml(New IO.StringReader (qResults))
session.Add("Su ppliers", dsSuppliers)
End If
Return session.Item("S uppliers")
End Function
Public Function RetrieveSupplie rNames(ByVal UserCred As
Net.NetworkCred ential, ByVal Refresh As Boolean) As DataSet
If IsNothing(sessi on.Item("Suppli ers")) Or Refresh = True Then
crmIF1 = New crmIF.Custom.cr mIF(UserCred)
Dim qResults = crmIF1.ExecuteQ uery("<fetch mapping='logica l'><entity
name='account'> <attribute name ='name'/><attribute name='accountid '
/><filter type='and'><con dition attribute = 'customertypeco de' operator='eq'
value='10'/></filter></entity></fetch>")
Dim dsSuppliers As New DataSet
dsSuppliers.Rea dXml(New IO.StringReader (qResults))
session.Add("Su ppliers", dsSuppliers)
End If
Return session.Item("S uppliers")
End Function
End Class
