Click here to Skip to main content
15,353,432 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i am trying to import excel row datas in to table database in SQL

when i run the program , it does not enter onto the for loop and it comes to 'finally {}' ;

What I have tried:

private void button2_Click(object sender, EventArgs e)
        {
            ////user excel
            DataSet tbds = new DataSet();
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string FileName = Path.GetFileName(openFileDialog1.FileName);
                HFExcel.Text = FileName.Trim();
                string Extension = Path.GetExtension(openFileDialog1.FileName);
                string FilePath = System.Windows.Forms.Application.StartupPath + "\\App_Data\\" + FileName;
                System.IO.File.Copy(openFileDialog1.FileName, FilePath);
                string conStr = string.Empty;
                switch (Extension)
                {
                    case ".xls": //Excel 97-03
                        conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        break;
                    case ".xlsx": //Excel 07
                        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        break;
                }
                OleDbConnection connExcel = new OleDbConnection(conStr);
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataTable dt = new DataTable();

                try
                {
                    connExcel.Open();
                    DataTable dtExcelSchema;
                    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    var SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                    connExcel.Close();
                    //Read Data from First Sheet.
                    connExcel.Open();
                    OleDbCommand cmdExcel = new OleDbCommand("SELECT * From [" + SheetName + "]", connExcel);
                    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                    oda.SelectCommand = cmdExcel;
                    oda.Fill(tbds);

                    DataTable exceldt = tbds.Tables[0];
                    string Result = string.Empty;
                    int TotalCount = 0;
                    int ResultCount = 0;
                    int FailedCount = 0;
                    int AlreadyCount = 0;

                    List<string> ListLinkedIds = new List<string>();
                    // Convert empty cells in of DataTable to NULL for VARCHAR column and 0 for other.
                    for (int i = 0; i < exceldt.Rows.Count; i++)
                    {
                        for (int j = 0; j < exceldt.Columns.Count; j++)
                        {
                            if (String.IsNullOrEmpty(exceldt.Rows[i][j].ToString()))
                            {
                                if (exceldt.Columns[j].DataType == typeof(string))
                                {
                                    exceldt.Rows[i][j] = "NULL";
                                }
                                else
                                {
                                    exceldt.Rows[i][j] = 0;
                                }
                            }
                        }
                    }
                    if (exceldt.Rows.Count > 0)
                    {
                         MessageBox.Show("Enter");
                        for (int i = 0; i < exceldt.Rows.Count; i++)
                        {
                            MessageBox.Show("Enter again");
                            objBO.QueryType = "Insert";
           objBO.EmpId = exceldt.Rows[i]["EmployeeId"].ToString().Trim();
           objBO.LocationId = exceldt.Rows[i]["Location"].ToString().Trim();
           objBO.FirstName = exceldt.Rows[i]["FirstName"].ToString().Trim();
           objBO.LastName = exceldt.Rows[i]["LastName"].ToString().Trim();
      objBO.Gender = exceldt.Rows[i]["Gender"].ToString().Trim();
       
     objBO.City = exceldt.Rows[i]["City"].ToString().Trim();
     objBO.Address = exceldt.Rows[i]["Address"].ToString().Trim();
 objBO.DOB = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i}["DOB"].ToString()).Trim();
                            
         objBO.State = exceldt.Rows[i]["State"].ToString().Trim();
         objBO.MobileNo = exceldt.Rows[i]["PhoneNo"].ToString().Trim();
           objBO.EmailId = exceldt.Rows[i]["Email"].ToString().Trim();
  objBO.UserName = exceldt.Rows[i]["UserName"].ToString().Trim();
 objBO.UserRole = exceldt.Rows[i]["Role"].ToString().Trim();
 objBO.Password = PasswordCode.Encryptdata(exceldt.Rows[i]["Password"].ToString()).Trim();
 objBO.LoginPin = exceldt.Rows[i]["LoginPin"].ToString().Trim();
   
   objBO.CreatedBy = userName;
    objBO.TransactionFlag = "N";
   //Result = objBAL.ProductMaster(objBO);
                            Result = objBAL.UsersMaster(objBO);
                            if (Result == "Success")
                            {
                                ResultCount = ResultCount + 1;
                            }
                            else if (Result == "Already")
                            {
                                AlreadyCount = AlreadyCount + 1;
                            }
                            else
                            {
                                FailedCount = FailedCount + 1;
                            }
                            
                          



                        }
                       

                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);

                    
                }
                finally
                {
                    MessageBox.Show("Imported Successfully!");
                    connExcel.Close();
                    
                    FileInfo file = new FileInfo(FilePath);
                    /*if (file.Exists)
                        file.Delete();*/
                    //MessageBox.Show("finally");
                    if (System.IO.File.Exists(FilePath))
                    {
                        System.IO.File.Delete(FilePath);
                    }
                    //MessageBox.Show("deleted");
                }
            }
            else
            {
                MessageBox.Show("no rows");
            }
        }
    }
}
Posted
Updated 25-Apr-22 1:36am
v2
Comments
Richard MacCutchan 23-Apr-22 7:02am
   
You should use the debugger to find out why.

1 solution

If it is not entering the for loop then exceldt.Rows.Count must be 0.
Use the debugger to examine the line
C#
DataTable exceldt = tbds.Tables[0];
Does exceldt have a value? It will probably either be null or contain an empty table, so keep working your way up your code until you find the culprit.

The problem probably originates with a misspelling of either the filename or the sheet name. We cannot see your data so it is for you to debug to find out why.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900