Problem using oledb datatypes to write data to excel sheet

Question

hi everyone,

I am trying insert some data into excel sheet using oledb dataadapter which is obtained from MYSQL Db.This data obtained from mysql db contains very long texts whose datatypes in MYSQL have been defined as(Varchar(1023),Text,Longtext etc).When I try to pass these to the oledb Dataadapter I tried to use oledb.VarWChar,oledb.LongVarWChar with size 5000 and so on.But I am getting the following exception when I try to run da.update(...) command.

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data

I am having trouble understanding what datatypes with what sizes should I use in oledb to map to these long text values.

Could someone please help me with this?

Thanks.

Answer 1

I am doing something similar and ran into the same error with varchar(max) data types that come from SQL Server. It doesn't matter where the data is coming from though. When you get the data from your database, you need to define the schema for the column data types and sizes. I do this by calling FillSchema on the data adapter that I am using -

 DataTable dt = new DataTable();
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 da.Fill(dt);
 da.FillSchema(dt, SchemaType.Source);
 return dt;

You could also set the column properties individually, if you wanted.

Then I loop through each column in my DataTable and set up my columns for export with oleDB using ADOX.NET. You don't have to use ADOX.NET, the main concept here is to use the sizes that came from the original database.

    foreach (DataColumn col in dt.Columns)
    {
       columnList.Append(dt.TableName + "." + col.ColumnName + ", ");
       ADOX.Column adoxCol = new Column();
       adoxCol.ParentCatalog = cat;
       adoxCol.Name = col.ColumnName;
       adoxCol.Type = TranslateType(col.DataType, col.MaxLength);
       int size = col.MaxLength > 0 ? col.MaxLength : 0;
       if (col.AllowDBNull)
       {
          adoxCol.Attributes = ColumnAttributesEnum.adColNullable;
       }
       adoxTable.Columns.Append(adoxCol, adoxCol.Type, size);
    }

Here is a snippet from my TranslateType method that determines whether or not to use the LongVarWChar or VarWChar. These data types are the ADOX.NET version of the oleDB data types. I believe that anything over 4000 characters should use the LongVarWChar type but I'm not sure about that. You didn't mention which version of Excel is your target, but I have this working with both Excel 2003 and Excel 2007.

    case "System.String":
       if (maxLength > 4000)
       {
          return DataTypeEnum.adLongVarWChar;
       }
       return DataTypeEnum.adVarWChar;

The LongVarWChar can take large sizes that can accomadate 2 GB. So don't worry about making the size too big.