all rows is not importing excel data inserting into sqlserver2008

See more:

ASP.NET

Hello ,

* User uploads the Excel File.
* Based on the extension it is decided whether it is Excel 2007 format.
* Excel file is uploaded and imported into the SQL Server Database table

i importing data into sql server only 45 rows inserted; actual excel data 100 rows but only 45 inserted into sql server2008.

Without any exceptions all rows are not inserted into sqlserver

plz tell me how to do

My code:

try
{
    //////
    bool blnValidate = validate();
    string connectionString = "";
    if (blnValidate == true)
    {
        string strTempPath = System.Configuration.ConfigurationManager.AppSettings["UploadTickers"].ToString();
        //string fileName = Path.GetFileName(fluplEarningsFile.PostedFile.FileName);
        string fileExtension = Path.GetExtension(fluplEarningsFile.PostedFile.FileName);
        //Store file name in the string variable
        filename = fluplEarningsFile.FileName.ToString();
        string strMainFilename = strTempPath + filename;
                       
        //Save file upload file in to server path for temporary

        //fluplEarningsFile.PostedFile.SaveAs(Server.MapPath(filename));
        string strFileFormat = "";
        int ModelRequestIDs = 0;
        connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strMainFilename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=0'";
        fluplEarningsFile.SaveAs(strTempPath + filename);
                        
        DataTable dt = new DataTable();
     
    
        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Connection = con;
        con.Open();
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
        DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
        cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
        dAdapter.SelectCommand = cmd;
        dAdapter.Fill(dt);

        //sqlconnection
        if (dt.Rows.Count > 0)
        {
            for (int exceldata = 0; exceldata < dt.Rows.Count; exceldata++)
            {
                if (dt.Rows[exceldata]["Client"].ToString() != ""
                    && dt.Rows[exceldata]["Ticker"].ToString() != ""
                    && dt.Rows[exceldata]["Broker"].ToString() != "")
                {
                    int companyid = GetCompanyID(dt.Rows[exceldata]["Client"].ToString());
                    int tickerid = GetTickerName(dt.Rows[exceldata]["Ticker"].ToString());
                    int borkerid = GetBrokerName(dt.Rows[exceldata]["Broker"].ToString());

                    //checking the data                
                    DataTable dtData = GetALLPendingTickerNames(companyid, borkerid, tickerid);
                               
                    if (dtData.Rows.Count > 0)
                    {
                        string strGroupName = "";

                        if (dtData.Rows[0]["FileNameOnDisk"].ToString() != "")
                        {
                            FunctionalClass fn = new FunctionalClass();
                            strGroupName = fn.GetGroupFullName(Convert.ToInt32(dtData.Rows[0]["GroupId"].ToString()));
                            strFileFormat = dtData.Rows[0]["FileNameOnDisk"].ToString();
                            ModelRequestIDs = Convert.ToInt32(dtData.Rows[0]["ModelPerRequestID"].ToString());
                            //status change and reuplod

                            if ((Convert.ToInt32(dtData.Rows[0]["ModelStatusId"].ToString()) == 6) 
|| Convert.ToInt32(dtData.Rows[0]["ModelStatusId"].ToString()) == 9)
                            {
                                updatemodelstatus(companyid, tickerid, borkerid);
                                //reupload the file again
                                updateAddondate(companyid, borkerid, tickerid);

                                UpdateFileNameOnDisk(companyid, borkerid, ModelRequestIDs, strFileFormat);
                                            
                                //update Borkerdetails
                                updateBorkerDetails(dt.Rows[exceldata]["Client"].ToString(), strGroupName, dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString());
                            }
                            else
                            {
                                //reupload the file again
                                updateAddondate(companyid, borkerid, tickerid);

                                UpdateFileNameOnDisk(companyid, borkerid, ModelRequestIDs, strFileFormat);


                                //update Borkerdetails
                                updateBorkerDetails(dt.Rows[exceldata]["Client"].ToString(), strGroupName, dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString());

                            }
                                    
                                    
                        }
                        else if (dtData.Rows[0]["FileNameOnDisk"].ToString() == "" || dtData.Rows[0]["FileNameOnDisk"].ToString() == null)
                        {
                            int ModelPerRequestIDs = 0;
                            string strShortGroupName = "";
                            FunctionalClass fn = new FunctionalClass();

                            strShortGroupName = fn.GetGroupFullName(Convert.ToInt32(dtData.Rows[0]["GroupId"].ToString()));
                            strFileFormat = fn.GetFileformat(dt.Rows[exceldata]["Broker"].ToString(), dt.Rows[exceldata]["Client"].ToString(), strShortGroupName, dt.Rows[exceldata]["Ticker"].ToString());
                            ModelPerRequestIDs = Convert.ToInt32(dtData.Rows[0]["ModelPerRequestID"].ToString());

                            //pre-q 

                            if ((Convert.ToInt32(dtData.Rows[0]["ModelStatusId"].ToString()) == 6) || Convert.ToInt32(dtData.Rows[0]["ModelStatusId"].ToString()) == 9)
                             {
                                updatemodelstatus(companyid, tickerid, borkerid);

                                //update fileondisk and date
                                UpdateFileNameOnDisk(companyid, borkerid, ModelPerRequestIDs, strFileFormat);

                                //update Borkerdetails
                                updateBorkerDetails(dt.Rows[exceldata]["Client"].ToString(), strShortGroupName, dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString());

                            }
                            else
                            {
                                //update fileondisk and date
                                UpdateFileNameOnDisk(companyid, borkerid, ModelPerRequestIDs, strFileFormat);

                                //update Borkerdetails
                                updateBorkerDetails(dt.Rows[exceldata]["Client"].ToString(), strShortGroupName, dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString());
                            }
                        }
                    }
                    else
                    {
                        if (tickerid == 0 || companyid == 0 || borkerid == 0)
                        {
                            if (tickerid == 0 && companyid == 0 && borkerid == 0)
                            {
                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString() + " " + "not available in System", dt.Rows[exceldata]["Client"].ToString() + " " + "not available in System" + " " + "and" + " " + dt.Rows[exceldata]["Ticker"].ToString() + " " + "not available in System" + " " + "and" + " " + dt.Rows[exceldata]["Broker"].ToString() + " " + "not available in System");
                            }
                            else if (tickerid == 0 && companyid == 0)
                            {
                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), dt.Rows[exceldata]["Client"].ToString() + " " + "not available in System" + " " + "and" + " " + dt.Rows[exceldata]["Ticker"].ToString() + " " + "not available in System");
                            }
                            else if (companyid == 0 && borkerid == 0)
                            {
                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), dt.Rows[exceldata]["Client"].ToString() + " " + "not available in System" + " " + "and" + " " + dt.Rows[exceldata]["Broker"].ToString() + " " + "not available in System");

                            }
                            else if (tickerid == 0 && borkerid == 0)
                            {

                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), dt.Rows[exceldata]["Ticker"].ToString() + " " + "not available in System" + " " + "and" + " " + dt.Rows[exceldata]["Broker"].ToString() + " " + "not available in System");
                            }

                            else if (tickerid == 0)
                            {
                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), dt.Rows[exceldata]["Ticker"].ToString() + " " + "not available in System");
                            }
                            else if (companyid == 0)
                            {
                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), dt.Rows[exceldata]["Client"].ToString() + " " + "not available in System");

                            }
                            else if (borkerid == 0)
                            {
                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), dt.Rows[exceldata]["Broker"].ToString() + " " + "not available in System");

                            }
                            else
                            {
                                Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), "Status is not pending");
                            }

                        }
                        else
                        {
                            Insertmodelpendingdata(dt.Rows[exceldata]["Client"].ToString(), dt.Rows[exceldata]["Ticker"].ToString(), dt.Rows[exceldata]["Broker"].ToString(), "Status is not pending,pre-q,old");

                        }
                        //tickerid =0 ,companyid=0,borkerid=0,status is not pending ,hell0
                    }
                }
            }

            con.Close();
            System.IO.File.Delete(strTempPath + luplEarningsFile.FileName.ToString());

            AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(this, typeof(string), "Success", "alert('The data has been imported successfully');", true);
        }
    }
    else
    {
        lblErrorMessageFile.Visible = true;
        lblErrorMessageFile.Text = "Please select excel file to import";
    }

    if (grdvwExportError.Rows.Count > 0)
    {
        btnexport.Visible = true;
    }

}
catch (Exception exp)
{
    AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(this, typeof(string), "ImportError", "alert('There was a problem in importing the data. Please try again later.');", true);
}
finally
{
    //if (con == ConnectionState.Open)
    //{
    //    conn1.Close();
       
      
    //}
}

Regrads (Regards)
sadhana belge