I need to save excel data from sql server 2008. So I am using grid view to show the data from excel after that i have to save the data from grid view to database.
there is no problem when the whole column is string or the whole column is numbers. While the column has combination of both string and int values, It only read the String value. Int values cannot be read? How to solve it? Below is my code
try
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
textBox1.Text = openFileDialog1.FileName;
//OleDbConnection co = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + " ;Extended Properties=Excel 8.0;");
OleDbConnection co = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;Importmixedtypes=text;typeguessrows=0;\"");
co.Open();
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", co);
da.TableMappings.Add("table", "Master_Product");
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
co.Close();
label2.Text = Convert.ToString(dataGridView1.Rows.Count - 1);
}
else
{
MessageBox.Show("Please Select The File");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}