Column Mapping Update Error

jeff · Post by **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
            Else
                Result &= ","
            End If
            If ColumnMappings.IndexOfDataSetColumn(col.ColumnName)  -1 Then
                ColumnMapping = ColumnMappings.GetByDataSetColumn(col.ColumnName)
                If WithFieldRenaming Then
                    Result &= "`" & ColumnMapping.SourceColumn & "` `" & ColumnMapping.DataSetColumn & "`"
                Else
                    Result &= "`" & ColumnMapping.SourceColumn & "`"
                End If
            Else
                If IsDBNull(col.DefaultValue) Then
                    Result &= "NULL `" & col.ColumnName & "`"
                Else
                    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
        Next
        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'"
        WriteDA.Fill(ProductDataSet.Product)

        ProductDataSet.Product(0).ProcessedDate = Now

        MsgBox(WriteDA.Update(ProductDataSet.Product))

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

(ProcessedDate)
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.

Thanks

Alexey · Post by **Alexey** » Wed 20 Dec 2006 12:56

What is the error message?
Try to use MySQLDirect .NET 3.55.

jeff · Post by **jeff** » Wed 20 Dec 2006 15:28

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

jeff · Post by **jeff** » Wed 20 Dec 2006 15:29

I have down loaded the latest and will try it today.

jeff · Post by **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.

Alexey · Post by **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.

jeff · Post by **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.

Thanks,

Alexey · Post by **Alexey** » Thu 28 Dec 2006 11:09

Please send one to AlexeyI at crlab dot com.
Include your database table definition.

Alexey · Post by **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.

Alexey · Post by **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.

Devart Forums

Column Mapping Update Error

Column Mapping Update Error

Found Problem