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
Loading
Kirtan PatelPosted Oct 15, 2009, 12:46 AM
If you don't mind can you mail me your project solution at [email protected]
it will be better for me I will send the solution after correcting error :)
Roei BarPosted Oct 10, 2009, 10:13 AM
use sample code like this:
using (oleconnection conn = new oleconnection(
{
conn.Open()
using (oleCommand comm = new (oleCommand))
{
// run your command and the connection will be closed @ the end
}
}
Kirtan PatelPosted Oct 10, 2009, 4:38 AM
Provide me the Code You are Using .
Please post Code in alignment :) so i can read it properly :)
Thank you :)