Excel crashing after SQL Insert using OleDB

B

bullpit

Hi,
I have written a piece of code in C# that executes on a Button click event.
What it does is just insert some values in an Excel sheet. The problem is
that if I have the workbook and sheet opened, I don't get any errors and
everything works just fine but when the workbook is closed and I try to
insert, it does insert something (I don't know what but I know since the file
size increases) but crashes when I try to open the workbook with just that
typical "Send-Don't Send Error Report" message. I don't even know what's
causing the problem. Here is my code:

private void button2_Click_1(object sender, EventArgs e)
{
string connectionString =
@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=template.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;

int catCount = 1; //To store number of different categories
int rowNumber = 0; //to store index of the last row read in a
category block
int temp = -1;

for (int k = 0; k <= dataGridView1.Rows.Count - 2; k++)
{
if (dataGridView1.Rows[k].Cells[0].Value.ToString() !=
dataGridView1.Rows[k + 1].Cells[0].Value.ToString())
{
catCount++;
}
}

for (int k = 1; k <= catCount; k++)
{
//write Category
if (dataGridView1.Rows[rowNumber].Cells[0].Value.ToString()
== dataGridView1.Rows[rowNumber].Cells[1].Value.ToString())
{
cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i +
":D" + i + "] VALUES ('" +
dataGridView1.Rows[rowNumber].Cells[0].Value.ToString() + "','')";
cmd.ExecuteNonQuery();
i++;
}
//write Category and Subcategory
if (dataGridView1.Rows[rowNumber].Cells[0].Value.ToString()
!= dataGridView1.Rows[rowNumber].Cells[1].Value.ToString())
{
cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i +
":D" + i + "] VALUES ('" +
dataGridView1.Rows[rowNumber].Cells[0].Value.ToString() + "','')";
cmd.ExecuteNonQuery();
i++;
cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i +
":D" + i + "] VALUES ('" +
dataGridView1.Rows[rowNumber].Cells[1].Value.ToString() + "','')";
cmd.ExecuteNonQuery();
i++;
}
do
{
cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i +
":D" + i + "] VALUES ('" +
dataGridView1.Rows[rowNumber].Cells[2].Value.ToString() + "','" +
dataGridView1.Rows[rowNumber].Cells[3].Value.ToString() + "')";
cmd.ExecuteNonQuery();
i++; //Excel region cell increment
rowNumber++;
temp++;
if (temp == dataGridView1.Rows.Count - 1)
{
break;
}
}
while (dataGridView1.Rows[temp].Cells[0].Value.ToString() ==
dataGridView1.Rows[temp + 1].Cells[0].Value.ToString());
i++;
}
conn.Close();
conn.Dispose();
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top