Excel 2003 cell is not allowing to enter more than 255 charecters in a cell.

[Anjan]

Hi All,
I am facing the following problem in inserting data into
excel 2003 file through OleDB provider.
I am generating excel 2003 file through a .net program. I
am reading the data from XML file and writing in to the
dataset. I am opening oledbconnection to excel file
through OleDB provider. And using the OleDbDataAdapter to
update the dataset(which is having XML data) into the
excel.

While executing da.update(ds) statement, If the data is
more than 255 characters it is throwing an exception as
follows

Exception is
**************************
The field is too small to accept the amount of data you
attempted to add .

The following code I am using.

public int UpdateExcelWithData()

{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='"+ strEXLPath +"';Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM
[Sheet1$]", conn);



int intColumnsCount = arrlColumns.Count;

string strInsertSQL = "INSERT INTO [Sheet1$] values (";



// Creating parameters dinamically

for(int i=0; i<intColumnsCount; i++)

strInsertSQL += "?,";



strInsertSQL = strInsertSQL.Substring
(0,strInsertSQL.Length-1) + ")";

da.InsertCommand = new OleDbCommand(strInsertSQL,conn);



for(int i=0; i<intColumnsCount; i++)

{

da.InsertCommand.Parameters.Add( GetParameter(XmlDS.Tables
[0].Columns.ColumnName,((string[])arrlColumns)[1],
((string[])arrlColumns)[2]);

}



od.Update(XmlDS,"row");



}



// Building and returning OledbParameters

private OleDbParameter GetParameter(string strName,string
strDataType,string strLength)

{

OleDbParameter param = new OleDbParameter();

param.ParameterName = "@" + strName;

param.SourceColumn = strName;

param.Direction = ParameterDirection.Input;



switch(strDataType.ToLower())

{

case "string" :

param.OleDbType =
OleDbType.VarChar;

if((!strLength.Equals
(String.Empty) || strLength != null) && (strLength.Length

0))

param.Size =
Convert.ToInt32(strLength);

break;

case "integer" :

param.OleDbType =
OleDbType.Integer;

if((!strLength.Equals
(String.Empty) || strLength != null) && (strLength.Length

0))

param.Size =
Convert.ToInt32(strLength);

break;

case "float" :

param.OleDbType =
OleDbType.Double;

if((!strLength.Equals
(String.Empty) || strLength != null) && (strLength.Length

0))

param.Size =
Convert.ToInt32(strLength);

break;

case "date" :

param.OleDbType =
OleDbType.Date;

break;

}
return param;
}

Please sugguest some solution.
Thanks in advance
Regards
Anjan

 

[keepITcool]

What's the data type read from the offending column?


External Data - Mixed Data Types
http://www.dicks-blog.com/excel/2004/06/external_data_m.html



Maybe this page offers some insight?
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook
With Visual Basic .NET

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934#4




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Anjan wrote :

 

[Guest]

Hi....
I am using OleDBType.Varchar for the OleDBparameter.

Thanks & Regards
Anjan

 

[keepITcool]

I'm no expert but i can google!

I think you need oledbtype.Wchar
see the table halfway on THIS page:

Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in
Visual Basic .NET

http://support.microsoft.com/default.aspx?scid=kb;EN-US;318373


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Anjan wrote :