Prevent Blank Windows Form DataGridView Row to insert in Database using C#

Last Reply one month ago By jmprateek

Posted one month ago

I'm importing excel file in to datagrid and then i'm inserting it in to database. Problem is excel blank rows are also getting inserted in to database.

        private void btnChooseFile_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            string fileExt = string.Empty;
            OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
            if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user
            {
                filePath = file.FileName; //get the path of the file
                fileExt = Path.GetExtension(filePath); //get the file extension
                if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
                {
                    try
                    {
                        DataTable dtExcel = new DataTable();
                        dtExcel = ReadExcel(filePath, fileExt); //read excel file
                        dataGridView1.Visible = true;
                        dataGridView1.DataSource = dtExcel;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                    }
                }
                else
                {
                    MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
                }
            }
        }
 
        private void btnClose_Click(object sender, EventArgs e)
        {
            for(int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                string val = this.dataGridView1.Rows[i].Cells[0].Value as string;
                if (string.IsNullOrEmpty(val) == false)
                {
                    con = new SqlConnection(constring);
                    con.Open();
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = "insert into Barcode(PartNumber, Description, Quantity, Bdate, Location, Mrp, ParentModel) values('" + dataGridView1.Rows[i].Cells[0].Value + "', '" + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "', '" + dataGridView1.Rows[i].Cells[4].Value + "', '" + dataGridView1.Rows[i].Cells[5].Value + "', '" + dataGridView1.Rows[i].Cells[6].Value + "')";
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                else
                {
                    //this.dataGridView1.Rows[i].Cells[0].Visible = false;
                    MessageBox.Show("Some of the contents of this excel files are empty!");
                }
            }
            MessageBox.Show("Part Details Uploaded Successfully!");
        }

 

You are viewing reply posted by: jmprateek one month ago.
Posted one month ago

Try this instead, it worked for me:

        private void btnClose_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
               string val = this.dataGridView1.Rows[i].Cells[0].Value.ToString();
                {
                    if (val == "")
                    {
                        dataGridView1.Rows.RemoveAt(i);
                        i--;
                    }
                    else
                    {
                        con = new SqlConnection(constring);
                        con.Open();
                        cmd = new SqlCommand();
                        cmd.Connection = con;
                        cmd.CommandText = "insert into Barcode(PartNumber, Description, Quantity, Bdate, Location, Mrp, ParentModel) values('" + dataGridView1.Rows[i].Cells[0].Value + "', '" + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "', '" + dataGridView1.Rows[i].Cells[4].Value + "', '" + dataGridView1.Rows[i].Cells[5].Value + "', '" + dataGridView1.Rows[i].Cells[6].Value + "')";
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }