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.newrow 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.EventArgs) Handles InsertNow.Click
Dim InsertRows() As DataRow
Dim insertrow As DataRow
Dim QuoteDS As New quotes(Session, context)
Dim QuoteTable As DataTable = QuoteDS.TableProd
InsertRows = CType(Me.MainPanel.Controls(1), ins_ITQ).ProductRows
CType(Me.MainPanel.Controls(1), ins_ITQ).ClearPanel()
If InsertRows.Length > 0 Then
Dim maintable As DataTable = dsQuote.TableProd
Dim maxorder As Integer
If maintable.Select("Groupname = '" & InsertGroup.SelectedItem.Text &
"'").Length > 0 Then
MaxOrder = (maintable.Compute("max(order)", "groupname = '" &
InsertGroup.SelectedItem.Text & "'")) + 1
Else
MaxOrder = 0
End If
Dim count As Integer
For count = 0 To InsertRows.Length - 1
insertrow = InsertRows(count)
insertrow.Item("order") = maxorder
insertrow.Item("groupname") = Me.InsertGroup.SelectedItem.Text
QuoteTable.Rows.Add(insertrow)
insertrow.AcceptChanges()
Next
QuoteDS.DS.AcceptChanges()
Dim lc As New LiteralControl
lc.EnableViewState = False
lc.Text = "<script>window.opener.ForcePostBack(true);</script>"
Me.Page.Controls.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.UserControl
Private QuoteDS As quotes
Public ReadOnly Property ProductRows() As DataRow()
Get
If Page.IsValid Then
Dim CheckedRows() As Janus.Web.GridEX.GridEXRow
CheckedRows = gdTagged.GetCheckedRows
If CheckedRows.Length > 0 Then
Dim count As Int16
Dim CheckedDataRw As DataRow
Dim TaggedTable As DataTable = RetrieveTagged()
Dim IFcrm As New crmIF.Custom.crmIF(Net.CredentialCache.DefaultCred entials)
Dim prodRows(CheckedRows.Length - 1) As DataRow
For count = 0 To CheckedRows.Length - 1
Dim prodRow As DataRow = CType(QuoteDS.TableProd, DataTable).NewRow
CheckedDataRw = TaggedTable.Rows.Find(CheckedRows(count).DataKeyVa lue)
prodRow.Item("code") = CheckedDataRw.Item("productCode")
prodRow.Item("vendorcode") = "" 'CheckedDataRw.item("")
prodRow.Item("description") = CheckedDataRw.Item("productName")
prodRow.Item("details") = "" 'CheckedDataRw.item("")
prodRow.Item("detailsfull") = "" 'CheckedDataRw.item("")
prodRow.Item("showspec") = True
prodRow.Item("image") = "" 'CheckedDataRw.item("")
prodRow.Item("tier1") = "" 'CheckedDataRw.item("")
prodRow.Item("tier2") = "" 'CheckedDataRw.item("")
prodRow.Item("tier3") = "" 'CheckedDataRw.item("")
prodRow.Item("tier4") = "" 'CheckedDataRw.item("")
prodRow.Item("vendor") = "" 'CheckedDataRw.item("")
Dim supID As String
supID = IFcrm.ReturnAccountID(CheckedDataRw.Item("supplier Name"))
If supID <> "" Then
prodRow.Item("supplier") = supID
Else
prodRow.Item("supplier") = "Unrecognised"
End If
prodRow.Item("stock") = 0 'CheckedDataRw.item("")
'prodRow.Item("eta") =
prodRow.Item("origcostex") = CheckedDataRw.Item("costEx")
prodRow.Item("costex") = CheckedDataRw.Item("costEx")
prodRow.Item("costinc") = CheckedDataRw.Item("costEx") * (1 +
System.Configuration.ConfigurationSettings.AppSett ings("gstrate"))
prodRow.Item("sellex") = CheckedDataRw.Item("sellEx")
prodRow.Item("sellinc") = CheckedDataRw.Item("sellEx") * (1 +
System.Configuration.ConfigurationSettings.AppSett ings("gstrate"))
prodRow.Item("rrpex") = CheckedDataRw.Item("rrpEx")
prodRow.Item("rrpinc") = CheckedDataRw.Item("rrpEx") * (1 +
System.Configuration.ConfigurationSettings.AppSett ings("gstrate"))
prodRow.Item("brochure") = ""
CheckedDataRw.Item("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.SessionState.HttpSessionState
Private context As HttpContext
Private crmIF1 As crmIF.Custom.crmIF
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(session.Item(context.User.Identity.Name & "DS")) Then
CreateDS()
End If
Return CType(session.Item(context.User.Identity.Name & "DS"), DataSet)
End Get
End Property
Protected WithEvents dsQuoteProducts As System.Data.DataSet
Protected WithEvents Groups As System.Data.DataTable
Protected WithEvents order As System.Data.DataColumn
Protected WithEvents name As System.Data.DataColumn
Protected WithEvents explanation As System.Data.DataColumn
Protected WithEvents shwnote As System.Data.DataColumn
Protected WithEvents shwtotal As System.Data.DataColumn
Protected WithEvents pid As System.Data.DataColumn
Protected WithEvents Products As System.Data.DataTable
Protected WithEvents code As System.Data.DataColumn
Protected WithEvents vendorcode As System.Data.DataColumn
Protected WithEvents description As System.Data.DataColumn
Protected WithEvents qty As System.Data.DataColumn
Protected WithEvents details As System.Data.DataColumn
Protected WithEvents detailsfull As System.Data.DataColumn
Protected WithEvents showspec As System.Data.DataColumn
Protected WithEvents image As System.Data.DataColumn
Protected WithEvents tier1 As System.Data.DataColumn
Protected WithEvents tier2 As System.Data.DataColumn
Protected WithEvents tier3 As System.Data.DataColumn
Protected WithEvents tier4 As System.Data.DataColumn
Protected WithEvents vendor As System.Data.DataColumn
Protected WithEvents supplier As System.Data.DataColumn
Protected WithEvents stock As System.Data.DataColumn
Protected WithEvents eta As System.Data.DataColumn
Protected WithEvents origcostex As System.Data.DataColumn
Protected WithEvents cstex As System.Data.DataColumn
Protected WithEvents cstinc As System.Data.DataColumn
Protected WithEvents sllex As System.Data.DataColumn
Protected WithEvents sllinc As System.Data.DataColumn
Protected WithEvents rrpx As System.Data.DataColumn
Protected WithEvents rrpi As System.Data.DataColumn
Protected WithEvents ordercol As System.Data.DataColumn
Protected WithEvents brochure As System.Data.DataColumn
Protected WithEvents grpnamecol As System.Data.DataColumn
Protected WithEvents cidcol As System.Data.DataColumn
Protected WithEvents colGroupID As System.Data.DataColumn
Protected WithEvents groupOrder As System.Data.DataColumn
Protected WithEvents colgrpFormatted As System.Data.DataColumn
Protected WithEvents DataColumn1 As System.Data.DataColumn 'Declare Dataset
Public Sub New(ByVal Sess As SessionState.HttpSessionState, ByVal Cont As
HttpContext)
session = Sess
context = Cont
If IsNothing(Sess(Cont.User.Identity.Name & "DS")) Then
InitDS()
CreateDS()
End If
End Sub
Public Sub InitDS()
Me.dsQuoteProducts = New System.Data.DataSet
Me.Groups = New System.Data.DataTable
Me.order = New System.Data.DataColumn
Me.name = New System.Data.DataColumn
Me.explanation = New System.Data.DataColumn
Me.shwnote = New System.Data.DataColumn
Me.shwtotal = New System.Data.DataColumn
Me.pid = New System.Data.DataColumn
Me.Products = New System.Data.DataTable
Me.code = New System.Data.DataColumn
Me.vendorcode = New System.Data.DataColumn
Me.description = New System.Data.DataColumn
Me.qty = New System.Data.DataColumn
Me.details = New System.Data.DataColumn
Me.detailsfull = New System.Data.DataColumn
Me.showspec = New System.Data.DataColumn
Me.image = New System.Data.DataColumn
Me.tier1 = New System.Data.DataColumn
Me.tier2 = New System.Data.DataColumn
Me.tier3 = New System.Data.DataColumn
Me.tier4 = New System.Data.DataColumn
Me.vendor = New System.Data.DataColumn
Me.supplier = New System.Data.DataColumn
Me.stock = New System.Data.DataColumn
Me.eta = New System.Data.DataColumn
Me.origcostex = New System.Data.DataColumn
Me.cstex = New System.Data.DataColumn
Me.cstinc = New System.Data.DataColumn
Me.sllex = New System.Data.DataColumn
Me.sllinc = New System.Data.DataColumn
Me.rrpx = New System.Data.DataColumn
Me.rrpi = New System.Data.DataColumn
Me.ordercol = New System.Data.DataColumn
Me.grpnamecol = New System.Data.DataColumn
Me.cidcol = New System.Data.DataColumn
Me.brochure = New System.Data.DataColumn
Me.colGroupID = New System.Data.DataColumn
Me.groupOrder = New System.Data.DataColumn
Me.colgrpFormatted = New System.Data.DataColumn
Me.DataColumn1 = New System.Data.DataColumn
Me.brochure = New System.Data.DataColumn
CType(Me.dsQuoteProducts,
System.ComponentModel.ISupportInitialize).BeginIni t()
CType(Me.Groups, System.ComponentModel.ISupportInitialize).BeginIni t()
CType(Me.Products, System.ComponentModel.ISupportInitialize).BeginIni t()
'
'dsQuoteProducts
'
Me.dsQuoteProducts.DataSetName = "dsQuoteProducts"
Me.dsQuoteProducts.Locale = New System.Globalization.CultureInfo("en-AU")
Me.dsQuoteProducts.Relations.AddRange(New System.Data.DataRelation() {New
System.Data.DataRelation("Relation1", "Groups", "Products", New String()
{"name"}, New String() {"groupname"}, False), New
System.Data.DataRelation("Relation2", "Groups", "Products", New String()
{"id"}, New String() {"groupid"}, False), New
System.Data.DataRelation("Relation3", "Groups", "Products", New String()
{"order"}, New String() {"groupOrder"}, False)})
Me.dsQuoteProducts.Tables.AddRange(New System.Data.DataTable() {Me.Groups,
Me.Products})
'
'Groups
'
Me.Groups.Columns.AddRange(New System.Data.DataColumn() {Me.order, Me.name,
Me.explanation, Me.shwnote, Me.shwtotal, Me.pid})
Me.Groups.Constraints.AddRange(New System.Data.Constraint() {New
System.Data.UniqueConstraint("Constraint1", New String() {"order"}, False),
New System.Data.UniqueConstraint("Constraint2", New String() {"id"}, True),
New System.Data.UniqueConstraint("Constraint3", New String() {"name"},
False)})
Me.Groups.PrimaryKey = New System.Data.DataColumn() {Me.pid}
Me.Groups.TableName = "Groups"
'
'order
'
Me.order.AllowDBNull = False
Me.order.Caption = "order"
Me.order.ColumnName = "order"
Me.order.DataType = GetType(System.Int32)
'
'name
'
Me.name.AllowDBNull = False
Me.name.Caption = "name"
Me.name.ColumnName = "name"
'
'Brochure
Me.brochure.AllowDBNull = True
Me.brochure.Caption = "brochure"
Me.brochure.DataType = GetType(System.String)
'explanation
'
Me.explanation.ColumnName = "explanation"
'
'shwnote
'
Me.shwnote.AllowDBNull = False
Me.shwnote.ColumnName = "shwnote"
Me.shwnote.DataType = GetType(System.Boolean)
Me.shwnote.DefaultValue = False
'
'shwtotal
'
Me.shwtotal.AllowDBNull = False
Me.shwtotal.ColumnName = "shwtotal"
Me.shwtotal.DataType = GetType(System.Boolean)
Me.shwtotal.DefaultValue = False
'
'id
'
Me.pid.AllowDBNull = False
Me.pid.AutoIncrement = True
Me.pid.Caption = "id"
Me.pid.ColumnName = "id"
Me.pid.DataType = GetType(System.Int32)
'
'Products
'
Me.Products.Columns.AddRange(New System.Data.DataColumn() {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.colgrpFormatted, Me.DataColumn1,
Me.brochure})
Me.Products.Constraints.AddRange(New System.Data.Constraint() {New
System.Data.UniqueConstraint("Constraint1", New String() {"cid"}, True), New
System.Data.ForeignKeyConstraint("Relation1", "Groups", New String()
{"name"}, New String() {"groupname"}, System.Data.AcceptRejectRule.None,
System.Data.Rule.Cascade, System.Data.Rule.Cascade), New
System.Data.ForeignKeyConstraint("Relation2", "Groups", New String() {"id"},
New String() {"groupid"}, System.Data.AcceptRejectRule.None,
System.Data.Rule.Cascade, System.Data.Rule.Cascade), New
System.Data.ForeignKeyConstraint("Relation3", "Groups", New String()
{"order"}, New String() {"groupOrder"}, System.Data.AcceptRejectRule.None,
System.Data.Rule.Cascade, System.Data.Rule.Cascade)})
Me.Products.PrimaryKey = New System.Data.DataColumn() {Me.cidcol}
Me.Products.TableName = "Products"
'
'type
'
'
'code
Me.brochure.AllowDBNull = True
Me.brochure.ColumnName = "brochure"
'
Me.code.AllowDBNull = False
Me.code.ColumnName = "code"
'
'vendorcode
'
Me.vendorcode.ColumnName = "vendorcode"
'
'description
'
Me.description.ColumnName = "description"
'
'qty
'
Me.qty.AllowDBNull = False
Me.qty.ColumnName = "qty"
Me.qty.DataType = GetType(System.Int16)
Me.qty.DefaultValue = CType(1, Short)
'
'details
'
Me.details.ColumnName = "details"
'
'detailsfull
'
Me.detailsfull.ColumnName = "detailsfull"
'
'showspec
'
Me.showspec.ColumnName = "showspec"
Me.showspec.DataType = GetType(System.Boolean)
'
'image
'
Me.image.ColumnName = "image"
'
'tier1
'
Me.tier1.ColumnName = "tier1"
'
'tier2
'
Me.tier2.ColumnName = "tier2"
'
'tier3
'
Me.tier3.ColumnName = "tier3"
'
'tier4
'
Me.tier4.ColumnName = "tier4"
'
'vendor
'
Me.vendor.ColumnName = "vendor"
'
'supplier
'
Me.supplier.ColumnName = "supplier"
'
'stock
'
Me.stock.ColumnName = "stock"
Me.stock.DataType = GetType(System.Int32)
'
'eta
'
Me.eta.ColumnName = "eta"
Me.eta.DataType = GetType(System.DateTime)
Me.eta.AllowDBNull = True
'
'origcostex
'
Me.origcostex.ColumnName = "origcostex"
Me.origcostex.DataType = GetType(System.Decimal)
'
'cstex
'
Me.cstex.ColumnName = "costex"
Me.cstex.DataType = GetType(System.Decimal)
'
'cstinc
'
Me.cstinc.Caption = "costinc"
Me.cstinc.ColumnName = "costinc"
Me.cstinc.DataType = GetType(System.Decimal)
'
'sllex
'
Me.sllex.ColumnName = "sellex"
Me.sllex.DataType = GetType(System.Decimal)
'
'sllinc
'
Me.sllinc.ColumnName = "sellinc"
Me.sllinc.DataType = GetType(System.Decimal)
'
'rrpx
'
Me.rrpx.ColumnName = "rrpex"
Me.rrpx.DataType = GetType(System.Decimal)
'
'rrpi
'
Me.rrpi.ColumnName = "rrpinc"
Me.rrpi.DataType = GetType(System.Decimal)
'
'ordercol
'
Me.ordercol.AllowDBNull = False
Me.ordercol.ColumnName = "order"
Me.ordercol.DataType = GetType(System.Int32)
'
'grpnamecol
'
Me.grpnamecol.AllowDBNull = False
Me.grpnamecol.ColumnName = "groupname"
'
'cidcol
'
Me.cidcol.AllowDBNull = False
Me.cidcol.AutoIncrement = True
Me.cidcol.Caption = "cid"
Me.cidcol.ColumnName = "cid"
Me.cidcol.DataType = GetType(System.Int32)
'
'colGroupID
'
Me.colGroupID.ColumnName = "groupid"
Me.colGroupID.DataType = GetType(System.Int32)
'
'groupOrder
'
Me.groupOrder.ColumnName = "groupOrder"
Me.groupOrder.DataType = GetType(System.Int32)
'
'colgrpFormatted
'
Me.colgrpFormatted.ColumnName = "grpFormatted"
Me.colgrpFormatted.Expression = "'[' + iif(groupOrder > 9, groupOrder, '0' +
groupOrder) + '] ' + groupname"
Me.colgrpFormatted.ReadOnly = True
'
'DataColumn1
'
Me.DataColumn1.ColumnName = "prof"
Me.DataColumn1.DataType = GetType(System.Decimal)
Me.DataColumn1.Expression = "qty*costex"
Me.DataColumn1.ReadOnly = True
CType(Me.dsQuoteProducts,
System.ComponentModel.ISupportInitialize).EndInit( )
CType(Me.Groups, System.ComponentModel.ISupportInitialize).EndInit( )
CType(Me.Products, System.ComponentModel.ISupportInitialize).EndInit( )
End Sub
Private Sub CreateDS()
If IsNothing(session.Item(context.User.Identity.Name & "DS")) Then
session.Add(context.User.Identity.Name & "DS", Me.dsQuoteProducts)
End If
CreateGroupTable()
End Sub
Private Sub CreateGroupTable()
Dim GroupTable As DataTable
Dim GroupKey(0) As DataColumn
Dim drGroup As DataRow
GroupTable = RetrieveTable("Group")
drGroup = GroupTable.NewRow
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(ByVal TableName As String) As DataTable
Select Case TableName
Case "Group"
If IsNothing(session.Item(context.User.Identity.Name & "DS")) Then
CreateDS()
End If
Return CType(session.Item(context.User.Identity.Name & "DS"),
DataSet).Tables("Groups")
Case "Product"
If IsNothing(session.Item(context.User.Identity.Name & "DS")) Then
CreateDS()
End If
Return CType(session.Item(context.User.Identity.Name & "DS"),
DataSet).Tables("Products")
End Select
End Function
Public Function RetrieveSuppliers(ByVal UserCred As Net.NetworkCredential,
ByVal Refresh As Boolean) As DataSet
If IsNothing(session.Item("Suppliers")) Or Refresh = True Then
crmIF1 = New crmIF.Custom.crmIF(UserCred)
Dim qResults = crmIF1.ExecuteQuery("<fetch mapping='logical'><entity
name='account'><all-attributes/><filter type='and'><condition attribute =
'customertypecode' operator='eq' value='10'/></filter></entity></fetch>")
Dim dsSuppliers As New DataSet
dsSuppliers.ReadXml(New IO.StringReader(qResults))
session.Add("Suppliers", dsSuppliers)
End If
Return session.Item("Suppliers")
End Function
Public Function RetrieveSupplierNames(ByVal UserCred As
Net.NetworkCredential, ByVal Refresh As Boolean) As DataSet
If IsNothing(session.Item("Suppliers")) Or Refresh = True Then
crmIF1 = New crmIF.Custom.crmIF(UserCred)
Dim qResults = crmIF1.ExecuteQuery("<fetch mapping='logical'><entity
name='account'><attribute name ='name'/><attribute name='accountid'
/><filter type='and'><condition attribute = 'customertypecode' operator='eq'
value='10'/></filter></entity></fetch>")
Dim dsSuppliers As New DataSet
dsSuppliers.ReadXml(New IO.StringReader(qResults))
session.Add("Suppliers", dsSuppliers)
End If
Return session.Item("Suppliers")
End Function
End Class
