Hello,
I am facing a problem during inserting excel data to a database table. The values of every field that are non-numeric type are getting inserted, but in case of numeric type data the values inserted are NULL.
How can I get rid of it? Please help.
The Code:
private void button1_Click(object sender, EventArgs e)
{
string connectionString = @“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Excel\GBLChar200909.xls;Extended Properties=”“Excel 8.0;HDR=YES;”“”;
string constr = System.Configuration.ConfigurationSettings.AppSettings[“ConnectionStringImport”].ToString();
SqlConnection sqlcon = new SqlConnection(constr);
sqlcon.Open();
SqlCommand com = new SqlCommand();
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
string test0, test1, test2, test3, test4;
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [Weights$]";
connection.Open();
int RowID = 1;
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
com.CommandText = "Insert Into TempImportTable Values(" + RowID + ", 'ACI Characteristics 2009-03-31.xls', 'Sheet1', '" + dr[0].ToString().Replace("'", "''") + "','" + dr[1].ToString().Replace("'", "''") + "','" + dr[2].ToString().Replace("'", "''") + "','" + dr[3].ToString().Replace("'", "''") + "','" + dr[4].ToString().Replace("'", "''") + "','" + dr[5].ToString().Replace("'", "''") + "','" + dr[6].ToString().Replace("'", "''") + "','" + dr[7].ToString().Replace("'", "''") + "','" + dr[8].ToString().Replace("'", "''") + "','" + dr[9].ToString().Replace("'", "''") + "')";
com.CommandType = CommandType.Text;
com.Connection = sqlcon;
com.ExecuteNonQuery();
RowID++;
}
}
}
}
sqlcon.Close();
sqlcon.Dispose();
}
Thanks and Regards,
Anujit Karmakar