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

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 h开发者_运维问答ours and I haven't gotten anywhere. I've tried messing with the connection string and changing a registry setting, but no luck.

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.

I used OleDb and with dotnetN00b's tip I managed to do it.

        string con = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\test.xls;Extended Properties=""Excel 8.0;HDR=YES;""");           

        var cnn = new OleDbConnection(con);
        cnn.Open();

        string createCom = "CREATE TABLE [Sheet1] ( [A] string, [B] Memo);";
        string insertCom = "INSERT INTO [Sheet1] VALUES('This can be max 255', 'This one can be realy looooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooong!');";

        OleDbCommand cmd = new OleDbCommand(createCom, cnn);
        cmd.ExecuteNonQuery();

        cmd = new OleDbCommand(insertCom, cnn);
        cmd.ExecuteNonQuery();

        cnn.Close();