Can any one give solution for :unspecifird error when open oledb connection in C#

Question

Hi All

I am using database as a MS Access.When I insert record from datagridview  to database.if records are less in no (upto 12 records) code work fine.but when records are large it gives Exception unspecified error occur.

Also when i read more  records (more than 10)using OleDbDataReader  same error occur.

when i debug point exception it found that connection is not open.
I check connection string,code but it looking without bugs
Can any one solve my problem.

Code is as below :-

     // Save  Estimate Item overview
       private void button1_Click(object sender, EventArgs e)
        {
            BL_EstimateItem BLEstimateItem = null;
            try
            {
              
                BLEstimateItem = new BL_EstimateItem();
                int count = 0;
                foreach (DataGridViewRow row in dg_Estimate_Item1.Rows)
                  {
                    if (((bool)row.Cells["chkid1"].FormattedValue) == true)
                    {
                        count++;
                    }
                  }


                if (count != 0)
                {
                    clsNew_Estimate obj = (clsNew_Estimate)(obj_Hashtable["New_Estimate"]);
                    int intzone = obj.Zone;
                    int intYear = obj.Year;
                    int intarea = obj.Area1;
                    int intval = 0;
                    double doubleAmount = 0;

                        //int ENO = 0;
                        //ENO = BLEstimateItem.getEstimateID();
                        //ENO = ENO + 1;
                  
                    string str = txtarea.Text;
                    str += "_";
                    str += txtNameOfWork.Text;


                    foreach (DataGridViewRow row in dg_Estimate_Item1.Rows)
                      {
                        intval = intval + 1;
                        if (((bool)row.Cells["chkid1"].FormattedValue) == true)
                        {
                            doubleAmount += double.Parse(row.Cells["Amount"].Value.ToString());
                        }
                      }

                    int myInt = int.Parse((Math.Round(doubleAmount)).ToString());         
                    string logid = "";
                    try
                    {
                        clsLoginName objLog = new clsLoginName();
                        Hashtable obj_Hashtable1 = Singleton.GetInstance();
                        objLog = (clsLoginName)(obj_Hashtable1["LoginInfo"]);

                        if (objLog.StrLoginId.ToString() == "")
                        {
                            logid = "";
                        }
                        else
                        {
                            logid = objLog.StrLoginId.ToString();
                        }
                    }
                    catch (Exception ex)
                    {
                    }
                 
                    /// No of Estimate
                    string strEstimateNumber = str;
                    ////
                    //// Check Exist of Estimate
                    ///
                    DataSet ds_Exist = BLEstimateItem.CheckExistEstimate(strEstimateNumber,intYear,intzone,intarea);
                    if (ds_Exist.Tables[0].Rows.Count > 0)
                    {
                        MessageBox.Show("This Estimate Already Exist");
                        return;
                    }                   


                    int intvalinsert = BLEstimateItem.InsertEstimation(strEstimateNumber,doubleAmount.ToString(), "V1.1", logid, intYear, intzone, intarea);
                    int ENO = BLEstimateItem.getEstimateID();
                    strEstimateNumber = str;
                    int intvalupdate = BLEstimateItem.UpdateEstimation(ENO.ToString(), strEstimateNumber);                                     
                   
                    ///
                  /// 
                 /// Save items into DB
                 ///
                ///

                    string[] strSelectAR = new string[100];
                    string strSelect = "";
                    int cnt = 0;

                    foreach (DataGridViewRow row in dg_Estimate_Item1.Rows)
                    {
                        intval = intval + 1;
                        if (((bool)row.Cells["chkid1"].FormattedValue) == true)
                        {
                            string strNSNumber = "";
                            string strItem = "";
                            string strSubItem = "";
                            string strSubSubItem = "";
                            string strDesc = "";
                            double intQty = 0;
                            string strUnit = "";
                            double intRate = 0;
                            double intAmount = 0.0;
                            string strNS = "";

                            int EstimateId = BLEstimateItem.getEstimateID();

                            intval = int.Parse(row.Cells["RefofDSR"].Value.ToString());
                            ListItem lstnew = new ListItem();
                            ListItem lstnew1 = new ListItem();
                            ListDetails lst = (ListDetails)obj_Hashtable["Cart"];

                            intval = lst.getIndex(intval.ToString());
                            int intid = intval - 1;
                            lstnew = lst.getDataById(row.Cells["RefofDSR"].Value.ToString());
                          
                            if (lstnew.Item != "")
                            {
                                strItem = lstnew.Item;
                                strSubItem = lstnew.Subitem;
                                strDesc = lstnew.desc;                    
                                strNSNumber = lstnew.itemNumber;
                            }
                            else
                            {
                                lstnew1 = lst.getDataByIndex(intval - 2);
                                int i = 0;
                                while (lstnew1.Item == "")
                                {
                                  lstnew1 = lst.getDataByIndex(intval - 2 - i);
                                  i++;
                                }

                                strItem = lstnew1.Item;
                                strSubItem = lstnew.Subitem;
                                strDesc = lstnew1.desc;            
                                strNSNumber = lstnew1.itemNumber;
                            }

                            strSubSubItem = lstnew.Subsubitem;
                            intQty = double.Parse(lstnew.qty);
                            strUnit = (lstnew.unit);
                            intRate = double.Parse(lstnew.rate);
                            intAmount = double.Parse(lstnew.Amount.ToString());
                            strNS = lstnew.nonSceduleYN;

                            if (strNS == "Y")
                            {
    int intvalofNS = BLEstimateItem.InsertEstimationItemIntoDB(EstimateId,
    intRate.ToString(), intQty.ToString(), intAmount.ToString(), strNSNumber, 
   strSubItem, strDesc, strUnit, "Y");
                            }

                            else if (strNS == "N")
                            {
                                DataLinkLayer DL = new DataLinkLayer();  
                                int intItem = 0;
                                int intSubItem = 0;
                                int intSubSubItem = 0;
                                int intSubSubSubItem = 0;
                                if(strItem!="")
                                {  
                                   OleDbDataReader dr =null;
                                   string str1 = "Select Top 1 Item_Id from tbl_Item where Item_No='" +strItem + "'";
                                   dr= DL.SelectData_Reader(str1);
                                   if (dr.Read())
                                   {
                                       intItem = Convert.ToInt16(dr[0].ToString());
                                   }                               
                                  
                              
                                }
                                if (strSubItem != "")
                                {
                                    OleDbDataReader dr2 = null;
                                    string str2 = "Select Top 1 SubItem_ID from tbl_SubItem where SubItem_Name='" + strSubItem + "'";
                                    dr2 = DL.SelectData_Reader(str2);
                                    if (dr2.Read())
                                    {
                                        intSubItem = Convert.ToInt32(dr2[0].ToString());
                                    }                                   
                                }

                                if (strSubSubItem != "")
                                {
                                    OleDbDataReader dr3 = null;
                                    string str3 = "Select Top 1 SubSubItem_ID from tbl_SubSubItem where SubSubItem_Name='" + strSubSubItem + "'";
                                    dr3 = DL.SelectData_Reader(str3);
                                    if (dr3.Read())
                                    {
                                        intSubSubItem = Convert.ToInt32(dr3[0].ToString());
                                    }    
                                   
                                }
                                if (strItem != "")
                                {
                                    OleDbDataReader dr4 = null;
                                    string str4 = "Select * from tbl_SubSubSubItem where SubSubSubItem_Name='" +strItem + "'";
                                    dr4 = DL.SelectData_Reader(str4);
                                    if (dr4.Read())
                                    {
                                        intSubSubSubItem = Convert.ToInt32(dr4[0].ToString());
                                    }   
                                   
                                }
                                ///int intvalofNS = BLEstimateItem.InsertEstimatItemintoDB(EstimateId, intRate.ToString(), intQty.ToString(), intAmount.ToString(), intItem.ToString(), intSubItem.ToString(), intSubSubItem.ToString(), intSubSubSubItem.ToString(), strDesc, strUnit, "N");
                               
                                strSelect = "insert into tbl_EstimateItem(Estimate_Id,Rate,Qty,Amount,Item_id,Subitem_id,Subsubitem_id,Subsubsubitem_id,Unit,Descitem,NSitemYorN) values ('" + EstimateId + "','" + intRate.ToString() + "','" + intQty.ToString() + "','" + intAmount.ToString() + "','" + intItem.ToString() + "','" + intSubItem.ToString() + "','" + intSubSubItem.ToString() + "','" + intSubSubSubItem.ToString() + "','" + strUnit + "','" + strDesc + "','N') ";
                                //strSelectAR[cnt++] = strSelect; 
                                strSelectAR[cnt] = strSelect;
                                cnt++;
                            }
                        }
                    }

                    if (strSelect != "")
                    {
                        DataLinkLayer DL = new DataLinkLayer();
/// found error here                     
                        int intval1 = DL.InsertDataArray(strSelectAR);                      
                        if (intval == 1)
                          MessageBox.Show("Success");
                    }
                    clsEstimateOverview objEstimateOverview = new clsEstimateOverview();
                    objEstimateOverview.NetCost = doubleAmount;
                    objEstimateOverview.ENO2 = strEstimateNumber;
                    obj_Hashtable.Remove("clsEstimateOverview_Cost");
                    obj_Hashtable.Add("clsEstimateOverview_Cost", objEstimateOverview);

                    this.Close();
                    Estimate_Overview obj_Estimate_Overview = new Estimate_Overview();
                    obj_Estimate_Overview.Show();
                }
                else
                {
                    MessageBox.Show("Please select Item To show");
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                BLEstimateItem = null;
            }
        }       


 Data Link Layers code
namespace MIDC
{
    public class DataLinkLayer
    {
        private int lintReturn;
        // string value = ConfigurationManager.AppSettings["ConnectionString"];
        static string AppPath = System.Windows.Forms.Application.StartupPath + @"\MIDC.mdb";
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AppPath);
        //OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MIDC.mdb");       
       
   #region "SelectData_Reader Method"
        /**
            *    The "SelectData_Reader" method selects data from the specified table in the main database & returns these data as a dataset.
            *    @method     SelectData_Reader
            *    @param        strSqlQuery - Select query to be executed
            *    @return     drSelectResult - data reader
            */
        public OleDbDataReader SelectData_Reader(string strSqlQuery)
        {
            conn.Close();
            //create a datareader object
            OleDbDataReader drSelectResult=null;

            try
            {
                //stablish connection with the main databse using  strConnString.
                //SqlConnection objNewConn = new SqlConnection(strConnString);

                //create a command object to execute the select query       

                conn.Open();

                OleDbCommand objCommd = new OleDbCommand(strSqlQuery, conn);

                //open the connection          

                //execute the command
                drSelectResult = objCommd.ExecuteReader();    
         
            //return the result          
            }
            catch (Exception Ex)
            {
                throw Ex;
                return null;

            }

            return (drSelectResult);

        }
        #endregion



#region "InsertDataArray Method"
        /**
            *    The "InsertDataArray" method inserts data into the specified table in the main database.
            *    @method     InsertData
            *    @param        strSqlQuery - insert query to be executed
            *    @return     int-0 or 1
            */
        public int InsertDataArray(string[] strSqlQuery)
        {
            OleDbCommand objSqlCmd = null;
            try
            {
                //Establish connection with the main databse using  strConnString.
                // SqlConnection objNewConn = new SqlConnection(strConnString);
                //open the connection
                // objNewConn.Open();
                //create a command object to execute insert query

                conn.Open();
                //objSqlCmd.CommandTimeout = 100000;
                //objSqlCmd.CommandType = CommandType.Text;

                for (int i = 0; i < strSqlQuery.Length; i++)
                {
                    if (strSqlQuery[i] != null)
                    {
                        objSqlCmd = new OleDbCommand(strSqlQuery[i], conn);
                        lintReturn = objSqlCmd.ExecuteNonQuery();
                        objSqlCmd.Dispose();
                    }
                }


                //execute the insert query

                return (lintReturn);
                //close the connection
                conn.Close();

            }
            catch (Exception ex)
            {
                //return exc;
                throw ex;
                return 0;
                //System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Error");
                //MessageBox.Show(ex.Message.ToString(), "Error");
                //  clsErrorLog.WriteErrorLog("D:/OnlineLLR/masterpage/ErrorLogs/ErrorLog", exc);
            }
            finally
            {

            }

        }
        #endregion


        public int InsertData(string strSqlQuery)
        {
            try
            {
                //Establish connection with the main databse using  strConnString.
                // SqlConnection objNewConn = new SqlConnection(strConnString);
                conn.Close();
                //open the connection
                // objNewConn.Open();

                //create a command object to execute insert query
                OleDbCommand objSqlCmd = new OleDbCommand(strSqlQuery, conn);
                conn.Open();
                //execute the insert query
                lintReturn = objSqlCmd.ExecuteNonQuery();

                //close the connection
                conn.Close();

            }
            catch (Exception exc)
            {
                throw exc;
                return 0;
                //return exc;
                //  clsErrorLog.WriteErrorLog("D:/OnlineLLR/masterpage/ErrorLogs/ErrorLog", exc);
            }
            return (lintReturn);
        }
        #endregion

Jalindar Lagad
Software Engineer
   OCS