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