Column Mapping Update Error

Column Mapping Update Error

Postby jeff » Tue 19 Dec 2006 16:27

I am having a problem with column mapping and hope that someone can help me.

I map column from the database to a datatable in a typed dataset using this sub:

Code: Select all
    Private Sub AddProductTableMappings(ByRef ColumnMappings As System.Data.Common.DataColumnMappingCollection)
        If ColumnMappings Is Nothing Then
            ColumnMappings = New System.Data.Common.DataColumnMappingCollection()
        End If
        With ColumnMappings
            .Add("NUMPUB", Constants.Product.ID_Field)
            .Add("PUBLISHER", Constants.Product.VendorID_Field)
            .Add("NUMBER", Constants.Product.VendorItemID_Field)
            .Add("DISCOUNT_CODE", Constants.Product.DiscountCode_Field)
            .Add("DISC_DATE", Constants.Product.DiscountDate_Field)
            .Add("PRICE", Constants.Product.RetailPrice_Field)
            .Add("PRICE_DATE", Constants.Product.RetailPriceDate_Field)
            .Add("PRICE_ID", Constants.Product.PriceCategory_Field)
            .Add("INVENTORY", Constants.Product.OnHandQuantity_Field)
            .Add("BIN_LOCATION", Constants.Product.OnHandBinLocation_Field)
            .Add("ENTER_DATE", Constants.Product.EnteredDate_Field)
            .Add("VENDORVERIFIED", Constants.Product.VendorUpdateDate_Field)
            .Add("CHANGE_DATE", Constants.Product.ChangedDate_Field)
            .Add("LAST_PROCESSED", Constants.Product.ProcessedDate_Field)
            .Add("DELETE_DATE", Constants.Product.DeletedDate_Field)
            .Add("THUMB_UP_ETAIL", Constants.Product.ImageDate_Field)
            .Add("THUMB_UP_WEB", Constants.Product.EtailUpdateOfImageDate_Field)
            .Add("DATE_LAST_SOLD", Constants.Product.DateLastSold_Field)
            .Add("BOUGHT_TO_DATE", Constants.Product.QuantityPurchasedToDate_Field)
            .Add("USE_ON_WEB", Constants.Product.PermissionForWeb_Field)
            .Add("USE_ON_ETAIL", Constants.Product.PermissionForEtail_Field)
            .Add("CD_CATALOG", Constants.Product.PermissionForAE_Field)
            .Add("UNITED_INCH", Constants.Product.UnitedInch_Field)
            .Add("SIZE_ID", Constants.Product.SizeCategory_Field)
            .Add("SIZE", Constants.Product.Size_Field)
            .Add("IMGWIDTH", Constants.Product.ImageWidth_Field)
            .Add("IMGHEIGHT", Constants.Product.ImageHeight_Field)
            .Add("PPRWIDTH", Constants.Product.PaperWidth_Field)
            .Add("PPRHEIGHT", Constants.Product.PaperHeight_Field)
            .Add("CAT_SUBJECT", Constants.Product.Subject_Field)
            .Add("CAT_STYLE_GENRE", Constants.Product.ArtStyle_Field)
            .Add("CAT_DECOR", Constants.Product.Decor_Field)
            .Add("CAT_PRIM_COLOR", Constants.Product.Color1_Field)
            .Add("CAT_SEC_COLOR", Constants.Product.Color2_Field)
            .Add("CAT_KEYWORD", Constants.Product.Keywords_Field)
            .Add("ALL_KEYWORDS", Constants.Product.AllKeywords_Field)
            .Add("Orientation", Constants.Product.Orientation_Field)
            .Add("MEDIUM_TYPE", Constants.Product.MediumType_Field)
            .Add("PRODUCT_TYPE", Constants.Product.ProductType_Field)
            .Add("SIGNED_NUM", Constants.Product.SignedAndNumbered_Field)
            .Add("ENHANCEMENTS", Constants.Product.Enhancements_Field)
            .Add("EDITION_SIZE", Constants.Product.EditionSize_Field)
            .Add("NUM_OF_COLORS", Constants.Product.NumberOfColors_Field)
            .Add("PRINTED_WITH", Constants.Product.PrintedWith_Field)
            .Add("ACID_FREE", Constants.Product.AcidFree_Field)
            .Add("PAPER_TYPE", Constants.Product.PaperType_Field)
            .Add("PAPER_COMPOSITION", Constants.Product.PaperComposition_Field)
            .Add("PAPER_WEIGHT", Constants.Product.PaperWeight_Field)
            .Add("SUBSTRATE", Constants.Product.Substrate_Field)
            .Add("AUTOGRAPH_BY_SUBJ", Constants.Product.AutographedBySubject_Field)
            .Add("CAN_CANV_TRANS", Constants.Product.CanCanvasTransfer_Field)
            .Add("READY_TO_HANG", Constants.Product.ReadyToHang_Field)
            .Add("TITLE", Constants.Product.Legacy_TITLE_Field)
            .Add("PROPER_TITLE", Constants.Product.Legacy_PROPER_TITLE_Field)
            .Add("ARTIST", Constants.Product.Legacy_ARTIST_Field)
            .Add("PROPER_LAST", Constants.Product.Legacy_PROPER_LAST_Field)
            .Add("FIRST_NAME", Constants.Product.Legacy_FIRST_NAME_Field)
            .Add("PROPER_FIRST", Constants.Product.Legacy_PROPER_FIRST_Field)
            .Add("HAS_FULL_IMAGE", Constants.Product.Legacy_HAS_FULL_IMAGE_Field)
            .Add("HAS_THUMB_IMAGE", Constants.Product.Legacy_HAS_THUMB_IMAGE_Field)
            .Add("HAS_IMAGE_SET", Constants.Product.Legacy_HAS_IMAGE_SET_Field)
            .Add("REMARKS", Constants.Product.Legacy_REMARKS_Field)
            .Add("XREF_NUM", Constants.Product.Legacy_XREF_NUM_Field)
            .Add("XREF_PUB", Constants.Product.Legacy_XREF_PUB_Field)
            .Add("IN_PRINT", Constants.Product.Legacy_IN_PRINT_Field)
            .Add("OUT_FOR_CAT", Constants.Product.Legacy_OUT_FOR_CAT_Field)
            .Add("NORMAL_SHIPMENT", Constants.Product.Legacy_NORMAL_SHIPMENT_Field)
            .Add("VENDOR_INV_ID", Constants.Product.Legacy_VENDOR_INV_ID_Field)
            .Add("VENDOR_ID", Constants.Product.Legacy_VENDOR_ID_Field)
            .Add("NUMPUB2", Constants.Product.Legacy_NUMPUB2_Field)
            .Add("VSP1", Constants.Product.Legacy_VSP1_Field)
            .Add("VSP2", Constants.Product.Legacy_VSP2_Field)
            .Add("RECNUM", Constants.Product.Legacy_RECNUM_Field)
            .Add("VALIDATED", Constants.Product.Legacy_VALIDATED_Field)
            .Add("ORDER_STAT", Constants.Product.Legacy_ORDER_STAT_Field)
            .Add("NEED_WORK", Constants.Product.Legacy_NEED_WORK_Field)
            .Add("CATEGORY", Constants.Product.Legacy_CATEGORY_Field)
            .Add("CENTURY", Constants.Product.Legacy_CENTURY_Field)
            .Add("SHORT_DESCRIPT", Constants.Product.ShortDescription_Field)
            .Add("FULL_DESCRIPT", Constants.Product.FullDescription_Field)
            .Add("FRAME_DESCRIPT", Constants.Product.FrameDescription_Field)
            .Add("MAT_DESCRIPT", Constants.Product.MatDescription_Field)
            .Add("SHIPPING_WEIGHT", Constants.Product.ShippingWeight_Field)
            .Add("SHIP_LEAD_TIME", Constants.Product.ShipLeadTime_Field)
            .Add("SecondImagePath", Constants.Product.SecondImagePath_Field)
            .Add("ThirdImagePath", Constants.Product.ThirdImagePath_Field)
            .Add("BOX_SKU", Constants.Product.BoxSku_Field)
            .Add("EDITION_QTY", Constants.Product.EditionQty_Field)
            .Add("OUR_COST", Constants.Product.OurPrice_Field)
        End With
    End Sub

I create the fields that are returned from the data table from with the following function:

Code: Select all
    Private Function CreateSelectionExpressions(ByRef Columns As DataColumnCollection, ByRef ColumnMappings As System.Data.Common.DataColumnMappingCollection, Optional ByVal WithFieldRenaming As Boolean = False) As String
        Dim Result As String = ""
        Dim ColumnMapping As System.Data.Common.DataColumnMapping
        Dim IsFirst As Boolean = True
        For Each col As DataColumn In Columns
            If IsFirst Then
                IsFirst = False
                Result &= ","
            End If
            If ColumnMappings.IndexOfDataSetColumn(col.ColumnName) <> -1 Then
                ColumnMapping = ColumnMappings.GetByDataSetColumn(col.ColumnName)
                If WithFieldRenaming Then
                    Result &= "`" & ColumnMapping.SourceColumn & "` `" & ColumnMapping.DataSetColumn & "`"
                    Result &= "`" & ColumnMapping.SourceColumn & "`"
                End If
                If IsDBNull(col.DefaultValue) Then
                    Result &= "NULL `" & col.ColumnName & "`"
                    Select Case col.DataType.ToString
                        Case "System.String"
                            Result &= "'" & col.DefaultValue & "' `" & col.ColumnName & "`"
                        Case Else
                            Result &= col.DefaultValue & " `" & col.ColumnName & "`"
                    End Select
                End If
            End If
        Return Result
    End Function

When I use these functions to generate a dataadapter and fill, modify, then update the data table, I get a concurrency exception during the update process.

Code: Select all
        Dim ProductDataSet As New ProductDataSet()

        Dim WriteDA As New MySqlDataAdapter("", WriteConnection)
        Dim SelStr As String

        With WriteDA
            AddProductTableMappings(WriteDA.TableMappings.Add("newinv", "Product").ColumnMappings)
            SelStr = CreateSelectionExpressions(ProductDataSet.Product.Columns, .TableMappings("newinv").ColumnMappings)
            .SelectCommand.CommandText = "select " & SelStr & " from newinv limit 0,1"
        End With

        Dim cb As New MySqlCommandBuilder(WriteDA)

        WriteDA.SelectCommand.CommandText = "Select " & SelStr & " from newinv where numpub='nyg7770'"

        ProductDataSet.Product(0).ProcessedDate = Now


I check the values of the in the datatable and they show as follows:

Current Value is 8/2/2006 12:00:00 AM
Change to 12/19/2006 11:21:04 AM

Does anyone have any Ideas why this is not working?

This did work correctly with the MySQL .NET 1.80 version from Corelab but stopped working on the 3.50 version.

Posts: 6
Joined: Mon 06 Nov 2006 21:04
Location: Florence, Massachusetts

Postby Alexey » Wed 20 Dec 2006 12:56

What is the error message?
Try to use MySQLDirect .NET 3.55.
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby jeff » Wed 20 Dec 2006 15:28

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Posts: 6
Joined: Mon 06 Nov 2006 21:04
Location: Florence, Massachusetts

Postby jeff » Wed 20 Dec 2006 15:29

I have down loaded the latest and will try it today.
Posts: 6
Joined: Mon 06 Nov 2006 21:04
Location: Florence, Massachusetts

Postby jeff » Wed 20 Dec 2006 21:39

Just finished testing with 3.55 and still get the same error

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Posts: 6
Joined: Mon 06 Nov 2006 21:04
Location: Florence, Massachusetts

Postby Alexey » Thu 21 Dec 2006 07:08

Please send us your test project to reproduce the problem; include definition of your own database objects.
Use e-mail address provided in the Readme file.
Do not use third party components.
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Found Problem

Postby jeff » Wed 27 Dec 2006 18:51

The problem is associated with a single field. If the field is defined as a boolean in the datatable and the database table value is an integer with a value of -1 a concurency error occurs.

It does not specifically have anything to do with column mapping.

In the process of comming up with a test application to show the problem I discovered where the error was.

If you would like me to send you an example of how to create the error as I found it I will do so.

Posts: 6
Joined: Mon 06 Nov 2006 21:04
Location: Florence, Massachusetts

Postby Alexey » Thu 28 Dec 2006 11:09

Please send one to AlexeyI at crlab dot com.
Include your database table definition.
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Alexey » Fri 29 Dec 2006 15:30

I've reproduced the error. Now we are investigating it.
Look forward to hearing from me again.
By the way, your mail server rejects my letters.
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Alexey » Tue 16 Jan 2007 13:45

The problem was that you inserted "-1" value into the boolean field (only values "0" and "1" are allowed). Also the string was to long for varchar(20) field. If you correct this, everything will go right. In future please send us only working examples.
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Return to dotConnect for MySQL