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 UploadStudentsXLFile(string filePath)
{
int i = 0;
string sheetName;

try
{
DataSet xlDataSet = new DataSet();
xlDataSet.Tables.Add("StudentsXml");

strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source= " + filePath + "; Extended Properties=Excel 8.0");

OleDbConnection xlCon = new OleDbConnection(strConn);

xlCon.Open();

System.Data.DataTable dt =
xlCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 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("SELECT * FROM [" + sheetName +
"]", xlCon);
xlCmd.CommandType = CommandType.Text;
OleDbDataReader xlReader = xlCmd.ExecuteReader();

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

//xlDataSet.Tables[0].Columns.Add(new
DataColumn(xlReader.GetName(fldIndex),xlReader.Get FieldType(fldIndex)));
xlDataSet.Tables[0].Columns.Add(new
DataColumn(xlReader.GetName(fldIndex), Type.GetType("System.String")));
}
DataRow dRow;
while(xlReader.Read())
{
dRow = xlDataSet.Tables[0].NewRow();
for(short fldIndex = 0; fldIndex < xlReader.FieldCount; fldIndex++)
{
dRow[fldIndex] = Convert.ToString(xlReader[fldIndex]);
}
xlDataSet.Tables[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]

"Ramakrishnan Nagarajan" <ra**********@discussions.microsoft.com> schrieb

Hi,
I am converting Excel data into a Dataset in C#.

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