Problem with datacolumn expression

  • Thread starter Thread starter Chris Ericoli
  • Start date Start date
C

Chris Ericoli

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.DefaultCredentials)

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).DataKeyValue)

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("supplierName"))

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.AppSettings("gstrate"))

prodRow.Item("sellex") = CheckedDataRw.Item("sellEx")

prodRow.Item("sellinc") = CheckedDataRw.Item("sellEx") * (1 +
System.Configuration.ConfigurationSettings.AppSettings("gstrate"))

prodRow.Item("rrpex") = CheckedDataRw.Item("rrpEx")

prodRow.Item("rrpinc") = CheckedDataRw.Item("rrpEx") * (1 +
System.Configuration.ConfigurationSettings.AppSettings("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).BeginInit()

CType(Me.Groups, System.ComponentModel.ISupportInitialize).BeginInit()

CType(Me.Products, System.ComponentModel.ISupportInitialize).BeginInit()

'

'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
 
Hi Chris,

This is fine documentated however in my opinion to much for a newsgroup.

Bring your problem down to the essentials and when needed do it in more
steps by sending them.

I saw you are talking about accept.changes, you know that this function is
needed when you do not want to update the changes in the dataset? It is
something as fix changes as if the update was done. (To be more precise all
rowstates are set to no changes)

However in my opinion is your desctription real to much to check the
problems for a newsgroup. I understand and appriciate that you did your best
to document it as good as possible.

Cor


Chris Ericoli said:
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.DefaultCredentials)

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).DataKeyValue)

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("supplierName"))

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.AppSettings("gstrate"))

prodRow.Item("sellex") = CheckedDataRw.Item("sellEx")

prodRow.Item("sellinc") = CheckedDataRw.Item("sellEx") * (1 +
System.Configuration.ConfigurationSettings.AppSettings("gstrate"))

prodRow.Item("rrpex") = CheckedDataRw.Item("rrpEx")

prodRow.Item("rrpinc") = CheckedDataRw.Item("rrpEx") * (1 +
System.Configuration.ConfigurationSettings.AppSettings("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).BeginInit()

CType(Me.Groups, System.ComponentModel.ISupportInitialize).BeginInit()

CType(Me.Products, System.ComponentModel.ISupportInitialize).BeginInit()

'

'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
Click to expand...
 
Back
Top