Regarding solving a problem in Converting Excel Data into Dataset

[Ramakrishnan Nagarajan]

Hi,
I am converting Excel data into a Dataset in C#. There are around 24 columns
in the Excel Sheet.
First I tried to insert one row with correct values in the Excel sheet. i.e.
for text columns I entered text values and for numeric columns I entered
numeric values. It works fine and pass through all the validation checks and
gets inserted into the database successfully.
But when I gave some junk values in the excel sheet and tried to insert, the
previous one with the correct values are also reporting error.

In all the columns I have entered values, but for columns that has
irrelevant data it is entering as Null value in the DataSet. I am not able to
identify where the problem may be. Following is the code I am using to
convert Excel Data into DataSet.

Code:

public DataSet UploadStudentsX LFile(string filePath)
{
int i = 0;
string sheetName;

try
{
DataSet xlDataSet = new DataSet();
xlDataSet.Table s.Add("Students Xml");

strConn = ("Provider=Micr osoft.Jet.OLEDB .4.0;" +
"Data Source= " + filePath + "; Extended Properties=Exce l 8.0");

OleDbConnection xlCon = new OleDbConnection (strConn);

xlCon.Open();

System.Data.Dat aTable dt =
xlCon.GetOleDbS chemaTable(OleD bSchemaGuid.Tab les, null);

string[] excelSheet = new String[dt.Rows.Count];
foreach(DataRow row in dt.Rows)
{
excelSheet[i] = row["Table_Name "].ToString();
i++;
}
sheetName = excelSheet[0];

OleDbCommand xlCmd = new OleDbCommand("S ELECT * FROM [" + sheetName +
"]", xlCon);
xlCmd.CommandTy pe = CommandType.Tex t;
OleDbDataReader xlReader = xlCmd.ExecuteRe ader();

//Create a structure for the dataset
for(short fldIndex = 0; fldIndex < xlReader.FieldC ount; fldIndex++)
{

//xlDataSet.Table s[0].Columns.Add(ne w
DataColumn(xlRe ader.GetName(fl dIndex),xlReade r.GetFieldType( fldIndex)));
xlDataSet.Table s[0].Columns.Add(ne w
DataColumn(xlRe ader.GetName(fl dIndex), Type.GetType("S ystem.String")) );
}
DataRow dRow;
while(xlReader. Read())
{
dRow = xlDataSet.Table s[0].NewRow();
for(short fldIndex = 0; fldIndex < xlReader.FieldC ount; fldIndex++)
{
dRow[fldIndex] = Convert.ToStrin g(xlReader[fldIndex]);
}
xlDataSet.Table s[0].Rows.Add(dRow) ;
}
xlReader.Close( );

xlCon.Close();
return xlDataSet;
}
catch(Exception )
{
throw;
}
}

Can you please help me out to solve this problem.

I am using .Net 2003, SQLServer 2000, Excel 2000.

Thanks in advance,
N.Ramakrishnan

[Armin Zingler]

Re: Regarding solving a problem in Converting Excel Data into Dataset

"Ramakrishn an Nagarajan" <ramakrishnan@d iscussions.micr osoft.com> schrieb[color=blue]
> Hi,
> I am converting Excel data into a Dataset in C#.[/color]



The C# groups is probably the better place to ask. :-)


Armin