OLEDB DataAdapter configuration failing - table with multiple columns

[JulesBos]

Hi all

Is there a maximum number of columns for an OLEDB dataadapter to handle? I'm trying to configure a dataadapter to generate select, insert, delete and update commands for an Access table with over 100 columns. I'm going to customise the SQL later because I won't use all 100 columns at the same time, but I just need the Wizard to configure it to start with.

However the configuration wizard fails and doesn't tell me the reason. It generates the select statement only.

I've tried generating it with fewer fields and it works, so my only conclusion is that the table design is too big for the wizard to handle.

Can anyone confirm or otherwise? If this is true, any suggestions, other than reducing the table size.

Julia

 

[ca8msm]

As far as I know, there isn't a limit on the number of columns. However, I wouldn't trust a control to generate my Stored Procedures for me especially when it will be creating them with redundant processing in them. Save yourself the problems and create them yourself based on what you need to select/edit/delete.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Thanks Mark, I've taken your advice and tried to do this myself. However by doing it myself I think I've found out where the problem is.

It's on the DataAdapter table mappings. If I try and put the 100 columns in the SDDataAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() code, I get the following error:

Overload resolution failed because no accessible 'New' accepts this number of arguments.

I do need the 100 columns on the dataadapter.

Any suggestions?

Thanks

Julia

 

[ca8msm]

Can you show all the code you are using to add these mappings? My guess is that you can't add them how you are trying but I'm sure you will still be able to add them another way.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Here it is (very long sorry!!):

CSDDataAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "CSDs", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("AckBy", "AckBy"), New System.Data.Common.DataColumnMapping("AckDate", "AckDate"), New System.Data.Common.DataColumnMapping("AdditonalEmail", "AdditonalEmail"), New System.Data.Common.DataColumnMapping("BillingInstr", "BillingInstr"), New System.Data.Common.DataColumnMapping("BillTo", "BillTo"), New System.Data.Common.DataColumnMapping("BillToAddCountry", "BillToAddCountry"), New System.Data.Common.DataColumnMapping("BillToAddCounty", "BillToAddCounty"), New System.Data.Common.DataColumnMapping("BillToAddLine1", "BillToAddLine1"), New System.Data.Common.DataColumnMapping("BillToAddLine2", "BillToAddLine2"), New System.Data.Common.DataColumnMapping("BillToAddLine3", "BillToAddLine3"), New System.Data.Common.DataColumnMapping("BillToAddPostcode", "BillToAddPostcode"), New System.Data.Common.DataColumnMapping("CommissionNo", "CommissionNo"), New System.Data.Common.DataColumnMapping("CommissionValue", "CommissionValue"), New System.Data.Common.DataColumnMapping("Consignee", "Consignee"), New System.Data.Common.DataColumnMapping("ConsigneeAddCountry", "ConsigneeAddCountry"), New System.Data.Common.DataColumnMapping("ConsigneeAddCounty", "ConsigneeAddCounty"), New System.Data.Common.DataColumnMapping("ConsigneeAddLine1", "ConsigneeAddLine1"), New System.Data.Common.DataColumnMapping("ConsigneeAddLine2", "ConsigneeAddLine2"), New System.Data.Common.DataColumnMapping("ConsigneeAddLine3", "ConsigneeAddLine3"), New System.Data.Common.DataColumnMapping("ConsigneeAddPostcode", "ConsigneeAddPostcode"), New System.Data.Common.DataColumnMapping("ContractType", "ContractType"), New System.Data.Common.DataColumnMapping("CSDNo", "CSDNo"), New System.Data.Common.DataColumnMapping("Currency", "Currency"), New System.Data.Common.DataColumnMapping("CustAddressCountry", "CustAddressCountry"), New System.Data.Common.DataColumnMapping("CustAddressCounty", "CustAddressCounty"), New System.Data.Common.DataColumnMapping("CustAddressLine1", "CustAddressLine1"), New System.Data.Common.DataColumnMapping("CustAddressLine2", "CustAddressLine2"), New System.Data.Common.DataColumnMapping("CustAddressLine3", "CustAddressLine3"), New System.Data.Common.DataColumnMapping("CustAddressPostcode", "CustAddressPostcode"), New System.Data.Common.DataColumnMapping("Customer", "Customer"), New System.Data.Common.DataColumnMapping("DateRaised", "DateRaised"), New System.Data.Common.DataColumnMapping("Dealer", "Dealer"), New System.Data.Common.DataColumnMapping("DealerAddCountry", "DealerAddCountry"), New System.Data.Common.DataColumnMapping("DealerAddCounty", "DealerAddCounty"), New System.Data.Common.DataColumnMapping("DealerAddLine1", "DealerAddLine1"), New System.Data.Common.DataColumnMapping("DealerAddLine2", "DealerAddLine2"), New System.Data.Common.DataColumnMapping("DealerAddLine3", "DealerAddLine3"), New System.Data.Common.DataColumnMapping("DealerAddPostcode", "DealerAddPostcode"), New System.Data.Common.DataColumnMapping("DefaultEmail", "DefaultEmail"), New System.Data.Common.DataColumnMapping("DeliverableData", "DeliverableData"), New System.Data.Common.DataColumnMapping("DropShip", "DropShip"), New System.Data.Common.DataColumnMapping("DSP83", "DSP83"), New System.Data.Common.DataColumnMapping("Duty", "Duty"), New System.Data.Common.DataColumnMapping("EndUse", "EndUse"), New System.Data.Common.DataColumnMapping("EndUseCountry", "EndUseCountry"), New System.Data.Common.DataColumnMapping("FMSCase", "FMSCase"), New System.Data.Common.DataColumnMapping("ForwardingAgent", "ForwardingAgent"), New System.Data.Common.DataColumnMapping("FreeCirculation", "FreeCirculation"), New System.Data.Common.DataColumnMapping("GeneralInfo", "GeneralInfo"), New System.Data.Common.DataColumnMapping("INCOTERMS", "INCOTERMS"), New System.Data.Common.DataColumnMapping("INCOTERMSPlace", "INCOTERMSPlace"), New System.Data.Common.DataColumnMapping("InspReqmnts", "InspReqmnts"), New System.Data.Common.DataColumnMapping("InterCoInstr", "InterCoInstr"), New System.Data.Common.DataColumnMapping("Intermediate", "Intermediate"), New System.Data.Common.DataColumnMapping("IPRSusp", "IPRSusp"), New System.Data.Common.DataColumnMapping("RecordID", "RecordID")New System.Data.Common.DataColumnMapping("LoadableSoftware", "LoadableSoftware"), New System.Data.Common.DataColumnMapping("MasterSched", "MasterSched"), New System.Data.Common.DataColumnMapping("MIDASNo", "MIDASNo"), New System.Data.Common.DataColumnMapping("MoDDuty", "MoDDuty"), New System.Data.Common.DataColumnMapping("NameplateReqmnts", "NameplateReqmnts"), New System.Data.Common.DataColumnMapping("NetworkActivityNo", "NetworkActivityNo"), New System.Data.Common.DataColumnMapping("OrderDate", "OrderDate"), New System.Data.Common.DataColumnMapping("OrderNo", "OrderNo"), New System.Data.Common.DataColumnMapping("OrderRecDate", "OrderRecDate"), New System.Data.Common.DataColumnMapping("OtherNameplateInfo", "OtherNameplateInfo"), New System.Data.Common.DataColumnMapping("PackingInstr", "PackingInstr"), New System.Data.Common.DataColumnMapping("PackingText", "PackingText"), New System.Data.Common.DataColumnMapping("Payer", "Payer"), New System.Data.Common.DataColumnMapping("PayerAddCountry", "PayerAddCountry"), New System.Data.Common.DataColumnMapping("PayerAddCounty", "PayerAddCounty"), New System.Data.Common.DataColumnMapping("PayerAddLine1", "PayerAddLine1"), New System.Data.Common.DataColumnMapping("PayerAddLine2", "PayerAddLine2"), New System.Data.Common.DataColumnMapping("PayerAddLine3", "PayerAddLine3"), New System.Data.Common.DataColumnMapping("PayerAddPostcode", "PayerAddPostcode"), New System.Data.Common.DataColumnMapping("PaymentTerms", "PaymentTerms"), New System.Data.Common.DataColumnMapping("PM", "PM"), New System.Data.Common.DataColumnMapping("PriceTerms", "PriceTerms"), New System.Data.Common.DataColumnMapping("PricingType", "PricingType"), New System.Data.Common.DataColumnMapping("ProcessDate", "ProcessDate"), New System.Data.Common.DataColumnMapping("Processor", "Processor"), New System.Data.Common.DataColumnMapping("ProductLine", "ProductLine"), New System.Data.Common.DataColumnMapping("Programme", "Programme"), New System.Data.Common.DataColumnMapping("ProgressComments", "ProgressComments"), New System.Data.Common.DataColumnMapping("QuoteNo", "QuoteNo"), New System.Data.Common.DataColumnMapping("Raiser", "Raiser"), New System.Data.Common.DataColumnMapping("RCFLicence", "RCFLicence"), New System.Data.Common.DataColumnMapping("RecordID", "RecordID"), New System.Data.Common.DataColumnMapping("RevenueRecForm", "RevenueRecForm"), New System.Data.Common.DataColumnMapping("ROE", "ROE"), New System.Data.Common.DataColumnMapping("Route", "Route"), New System.Data.Common.DataColumnMapping("RoutingInstr", "RoutingInstr"), New System.Data.Common.DataColumnMapping("RRBRCF", "RRBRCF"), New System.Data.Common.DataColumnMapping("RRBUK", "RRBUK"), New System.Data.Common.DataColumnMapping("RRBUS", "RRBUS"), New System.Data.Common.DataColumnMapping("SalesDistrict", "SalesDistrict"), New System.Data.Common.DataColumnMapping("SAPDate", "SAPDate"), New System.Data.Common.DataColumnMapping("ScannedOrder", "ScannedOrder"), New System.Data.Common.DataColumnMapping("SecChips", "SecChips"), New System.Data.Common.DataColumnMapping("SecChipsAuth", "SecChipsAuth"), New System.Data.Common.DataColumnMapping("ShipMarks", "ShipMarks"), New System.Data.Common.DataColumnMapping("ShippingCondition", "ShippingCondition"), New System.Data.Common.DataColumnMapping("ShippingInstr", "ShippingInstr"), New System.Data.Common.DataColumnMapping("ShipTo", "ShipTo"), New System.Data.Common.DataColumnMapping("ShipToAddCountry", "ShipToAddCountry"), New System.Data.Common.DataColumnMapping("ShipToAddCounty", "ShipToAddCounty"), New System.Data.Common.DataColumnMapping("ShipToAddLine1", "ShipToAddLine1"), New System.Data.Common.DataColumnMapping("ShipToAddLine2", "ShipToAddLine2"), New System.Data.Common.DataColumnMapping("ShipToAddLine3", "ShipToAddLine3"), New System.Data.Common.DataColumnMapping("ShipToAddPostcode", "ShipToAddPostcode"), New System.Data.Common.DataColumnMapping("SoldToBus", "SoldToBus"), New System.Data.Common.DataColumnMapping("SoldToCat", "SoldToCat"), New System.Data.Common.DataColumnMapping("SpecialTest", "SpecialTest"), New System.Data.Common.DataColumnMapping("SpecialTestAuth", "SpecialTestAuth"), New System.Data.Common.DataColumnMapping("SpecialTooling", "SpecialTooling"), New System.Data.Common.DataColumnMapping("SpecialToolingAuth", "SpecialToolingAuth"), New System.Data.Common.DataColumnMapping("Status", "Status"), New System.Data.Common.DataColumnMapping("SubAckBy", "SubAckBy"), New System.Data.Common.DataColumnMapping("SubAckDate", "SubAckDate"), New System.Data.Common.DataColumnMapping("SubmittedBy", "SubmittedBy"), New System.Data.Common.DataColumnMapping("SubmittedDate", "SubmittedDate"), New System.Data.Common.DataColumnMapping("Superseded", "Superseded"), New System.Data.Common.DataColumnMapping("UKEndUseCert", "UKEndUseCert"), New System.Data.Common.DataColumnMapping("UKLicence", "UKLicence"), New System.Data.Common.DataColumnMapping("UKPONo", "UKPONo"), New System.Data.Common.DataColumnMapping("UKSAPNo", "UKSAPNo"), New System.Data.Common.DataColumnMapping("UKSONo", "UKSONo"), New System.Data.Common.DataColumnMapping("USLicence", "USLicence"), New System.Data.Common.DataColumnMapping("USRCFSONo", "USRCFSONo"), New System.Data.Common.DataColumnMapping("ValidatedBy", "ValidatedBy"), New System.Data.Common.DataColumnMapping("ValidatedDate", "ValidatedDate"), New System.Data.Common.DataColumnMapping("VATStatus", "VATStatus"), New System.Data.Common.DataColumnMapping("Warranty", "Warranty"), New System.Data.Common.DataColumnMapping("WBSElement", "WBSElement")})})

 

[ca8msm]

Yes, that's the problem then. Either the System.Data.Common.DataColumnMapping can't accept an array of more than 100 items or more likely the AddRange function can't accept that many arguments.

Possible solutions are to create a System.Data.Common.DataColumnMapping array and then add that to the AddRange function, or use a standard array as the AddRange function will accept either.

Personally, I wouldn't use a TableAdaptor at all but may just be down to preference.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Hi Mark, thanks for your response.

I'm not sure I understand what you mean though. Sorry I'm relatively new to this.

Julia

 

[ca8msm]

OK, an easier example would be for you just to use the Add method for each column e.g.

d.TableMappings.Add("Column1", "Field1")
d.TableMappings.Add("Column2", "Field2")
etc...

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

OK thanks Mark, that makes sense. I'll give it a go.

Thanks for all your help.

Julia