The field is too small to accept the amount of data you attempted to add. Has anyone gotten this error from ADO.NET?

Question

Updating an Excel file from a dataset seems to work fine. But if I have text that's longer than 255 characters then I get the error above. Has anyone else gotten such an error? How do you fix it? I've been working on this for a couple of hours and I haven't gotten anywhere. I've tried messing with the connection string and changing a registry setting, but no luck.

Answer 1

There is no easy fix for this. Matter of fact I had to use a hack of sorts.

I tried to insert some text in an Excel field that was 262 characters long and got this error: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

This trick/hack works easily because I already have the Excel document created and it's empty (ie. no rows except the header). So in Excel, I pasted text that was 262 characters (it can be anything over 255) into the cells of the first row that could receive text that big. Afterwards, I ran ADO.NET (thru VB.NET) and pushed the data thru a dataset (ds.Update) to Excel and all the data went over nicely with no errors.

Unless someone knows of a way in Excel or code-behind to force the Excel cells to be what's called Memo fields, this is the only way that worked. I tried the trick with the registry but it didn't work for me.