OLE DB Provider and Excel

D

David Mullins

I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT a
row of data into an Excel speadsheet. The problem I encounter is that there
is one column (the [Description] column) that I need to INSERT a lot of data
into, but I keep getting a "field is too small" error from the Jet engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any way for
me to tweak the "field," which is actually a column in this case, so that I
can get more data into it using the OLE DB provider? Thanks for any help.
 
B

Bob Phillips

Post the code so that we can try it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
D

David Mullins

Well, let me give the offending code snippet here (please excuse the vertical
coding style):

This massive line is where I setup the query to the Jet engine. Everything
works fine as long as I keep the [Description] field value small (probably at
or below 255 chars), but I get an error when .ItemNote is too big. The
ExecuteNonQuery() call at the end is, of course, where I actually get the
exception that I have to catch.

ebayAddFileOleDbCommand.CommandText = _
"INSERT INTO [eBayAddFile$] " & _
"([Action], " & _
"[A:Condition], " & _
"[AmEx], " & _
"[ApplyShippingDiscount], " & _
"[BestOfferEnabled], " & _
"[BoldTitle], " & _
"[Border], " & _
"[BuyItNowPrice], " & _
"[Category], " & _
"[Category2], " & _
"[Counter], " & _
"[Country], " & _
"[Currency], " & _
"[CustomLabel], " & _
"[Description], " & _
"[Discover], " & _
"[DispatchTimeMax], " & _
"[Duration], " & _
"[FE:ListByDate], " & _
"[Featured], " & _
"[Format], " & _
"[GalleryType], " & _
"[GetItFast], " & _
"[GiftIcon], " & _
"[GiftExpressShipping], " & _
"[GiftShipToRecipient], " & _
"[GiftWrap], " & _
"[Highlight], " & _
"[ImmediatePayRequired], " & _
"[InsuranceOption], " & _
"[InsuranceFee], " & _
"[IntlShippingService-1:priority], "
& _
"[IntlShippingService-1:Option], " & _
"[IntlShippingService-1:Locations], "
& _
"[IntlShippingService-1:Cost], " & _

"[IntlShippingService-1:AdditionalCost], " & _
"[IntlShippingService-2:priority], "
& _
"[IntlShippingService-2:Option], " & _
"[IntlShippingService-2:Locations], "
& _
"[IntlShippingService-2:Cost], " & _

"[IntlShippingService-2:AdditionalCost], " & _
"[IntlShippingService-3:priority], "
& _
"[IntlShippingService-3:Option], " & _
"[IntlShippingService-3:Locations], "
& _
"[IntlShippingService-3:Cost], " & _

"[IntlShippingService-3:AdditionalCost], " & _
"[Location], " & _
"[LotSize], " & _
"[MOCashiers], " & _
"[OtherOnlinePayments], " & _
"[PackagingHandlingCosts], " & _
"[PaymentInstructions], " & _
"[PaymentSeeDescription], " & _
"[PayPalAccepted], " & _
"[PayPalEmailAddress], " & _
"[PersonalCheck], " & _
"[PicURL], " & _
"[PostalCode], " & _
"[PrivateAuction], " & _
"[PrivateID], " & _
"[PrivateNotes], " & _
"[ProductIDType], " & _
"[ProductIDValue], " & _
"[Quantity], " & _
"[Region], " & _
"[ReservePrice], " & _
"[SalesTaxState], " & _
"[SalesTaxPercent], " & _
"[ScheduleTime], " & _
"[ShipFromZipCode], " & _
"[ShipToLocations], " & _
"[ShippingInTax], " & _
"[ShippingIrregular], " & _
"[ShippingPackage], " & _
"[ShippingService-1:priority], " & _
"[ShippingService-1:Option], " & _
"[ShippingService-1:Cost], " & _
"[ShippingService-1:AdditionalCost],
" & _
"[ShippingService-2:priority], " & _
"[ShippingService-2:Option], " & _
"[ShippingService-2:Cost], " & _
"[ShippingService-2:AdditionalCost],
" & _
"[ShippingService-3:priority], " & _
"[ShippingService-3:Option], " & _
"[ShippingService-3:Cost], " & _
"[ShippingService-3:AdditionalCost],
" & _
"[ShippingType], " & _
"[SiteID], " & _
"[StartPrice], " & _
"[StoreCategory], " & _
"[StoreCategory2], " & _
"[SubTitle], " & _
"[Title], " & _
"[UseTaxTable], " & _
"[VisaMastercard], " & _
"[WeightUnit], " & _
"[WeightMajor], " & _
"[WeightMinor]) " & _
"VALUES " & _
"('VerifyAdd', " & _
"'" & _
IIf(.ItemCondition.Trim.ToUpper() =
"NEW", "New", "Used") & _
"', " & _
"NULL, " & _
"1, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"378, " & _
"377, " & _
"'Hidden', " & _
"'US', " & _
"'USD', " & _
"'" & _
.SellerSku.Trim.ToUpper.Replace("'",
"''") & _
"', " & _
"'" & _
("CONDITION: " & _

HttpUtility.HtmlEncode(.ItemCondition.Trim.ToUpper()).Replace("'", "''") & ".
" & _

HttpUtility.HtmlEncode(.ItemNote.Trim()).Replace("'", "''")).Substring(0, 1)
& _
"', " & _
"NULL, " & _
"1, " & _
"'GTC', " & _
"NULL, " & _
"NULL, " & _
"'StoresFixedPrice', " & _
"NULL, " & _
"1, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"1, " & _
"'NotOffered', " & _
"NULL, " & _
"1, " & _
"'USPSEconomyLetter', " & _
"'Worldwide', " & _
"9.99, " & _
"0.00, " & _
"2, " & _
"'USPSAirmailLetter', " & _
"'Worldwide', " & _
"9.99, " & _
"0.00, " & _
"3, " & _
"'USPSGlobalPriority', " & _
"'Worldwide', " & _
"9.99, " & _
"0.00, " & _
"'Memphis, Tennessee, United States',
" & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"1, " & _
"'(e-mail address removed)',
" & _
"NULL, " & _
"NULL, " & _
"'38672-6433', " & _
"NULL, " & _
"'" & _
.SellerSku.Trim.ToUpper.Replace("'",
"''") & _
"', " & _
"'FROM AMAZON', " & _
"'ISBN', " & _
"'" & _
.ProductId.Trim.ToUpper.Replace("'",
"''") & _
"', " & _
.ItemQuantity & ", " & _
"NULL, " & _
"NULL, " & _
"'MS', " & _
"7.00, " & _
"NULL, " & _
"NULL, " & _
"'Worldwide', " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"1, " & _
"'USPSMedia', " & _
"3.49, " & _
"0.00, " & _
"2, " & _
"'USPSPriority', " & _
"5.79, " & _
"0.00, " & _
"3, " & _
"'USPSExpressMail', " & _
"18.99, " & _
"0.00, " & _
"'Flat', " & _
"'US', " & _
.ItemPrice & ", " & _
"1, " & _
"1, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL, " & _
"NULL)"

ebayAddFileOleDbCommand.ExecuteNonQuery()



Bob Phillips said:
Post the code so that we can try it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

David Mullins said:
I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT a
row of data into an Excel speadsheet. The problem I encounter is that there
is one column (the [Description] column) that I need to INSERT a lot of data
into, but I keep getting a "field is too small" error from the Jet engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any way for
me to tweak the "field," which is actually a column in this case, so that I
can get more data into it using the OLE DB provider? Thanks for any help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top