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

A

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

param.Size =
Convert.ToInt32(strLength);

break;

case "integer" :

param.OleDbType =
OleDbType.Integer;

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

param.Size =
Convert.ToInt32(strLength);

break;

case "float" :

param.OleDbType =
OleDbType.Double;

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

param.Size =
Convert.ToInt32(strLength);

break;

case "date" :

param.OleDbType =
OleDbType.Date;

break;

}
return param;
}

Please sugguest some solution.
Thanks in advance
Regards
Anjan
 

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