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
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