Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 Sql trancation has completed it is no longer usabl

Author  Topic 

kvt.aravind
Starting Member

24 Posts
Posted - 2010-09-01 : 07:24:16
Kindly help me in this ....

public bool ProcessExecution(int intActionID)
{
if (intActionID != 0)
{
string strExecQuery = "";
string strExecutionQuery = "";
string strExecutionQueryInput = "";
string[] strExecutionQueryArray;
string[] strExecutionInputArray;
string strExecutionParamName = "";
string strEtemp = "";
string[] strEResVal;
string[] strEInputValue;
string Qry = "";
string strTblName = "";
string strActivityId = "";
string tablename = "";
string strToStatusName = "";
string strToStatusId = "";
string strCurButton = "";
string strUnitQuery = "";
string strDEQuery = "";
string strQuery123 = "";
string strQry12 = "";
string strTableUsed = "";
string strFirstVar = "";
string strTrimColon = "";
string[] strTrimTilde;
string strBeforeTrim = "";
string[] strStoreTilde;
string strTempField = "";
string[] strTempFieldArray;
string strTFlag = "";
string strTestQry = "";
string[] strArrayTemp;
string strTempQuery = "";
string strTempInput120 = "";
string strNamedParam = "";
string strFinalQuery = "";
strCurButton = hdnButtonFlag.Value;
bool blntblFlag = false;
string strDEName1 = "";
bool blnYflag = false;
int intTempActivityId;
string strCnt = "";
SqlTransaction sqlTrans = null;
try
{
sqlTrans = Obj.Con.BeginTransaction();
Obj.Cmd.Transaction = sqlTrans;
Obj.Cmd.CommandText = "SELECT ORG_APP_TABLE_NAME FROM " + strOrgShortName + "WDT_TEMPLATE_MAST WHERE TEMPLATE_NO=" + Templatenumber;
if ((Obj.Cmd.ExecuteScalar().ToString() != "") && (Obj.Cmd.ExecuteScalar() != null))
{
tablename = Obj.Cmd.ExecuteScalar().ToString();
}
Obj.Cmd.CommandText = "select To_Status_Name,TO_Status_Id from " + strOrgShortName + "WF_TO_STATUS where Button_Id=" + strCurButton + " and status_id=" + strStatusId + "and STEP_ID=" + strStepno + " and Prefix_Activity_Id=" + OrgAppId;
App1 = Obj.Cmd.ExecuteReader();
if (App1.HasRows)
{
while (App1.Read())
{
strToStatusName = App1["TO_STATUS_NAME"].ToString();
strToStatusId = App1["TO_Status_Id"].ToString();
}
}
App1.Close();
string strItemTempNo = "";
string[] strTableArray;
if (HidItemTemplateNo.Value.ToString() != "")
{
strItemTempNo = HidItemTemplateNo.Value.ToString();
strQry12 = "SELECT DB_TABLE FROM " + strOrgShortName + "ITEM_TEMPLATE_MAST WHERE ITEM_TEMPLATE_NO = " + strItemTempNo;

Obj.Cmd.CommandText = strQry12;
if (Obj.Cmd.ExecuteScalar() != null && Obj.Cmd.ExecuteScalar().ToString() != "")
{
strTableUsed = Obj.Cmd.ExecuteScalar().ToString();
}
}
else
{
strTableUsed = "";
}
Obj.Cmd.CommandText = "SELECT ACTION_EXECUTIONQUERY FROM " + strOrgShortName + "ACTION_MASTER WHERE WF_ID = '" + strWfId + "' AND WDT_ID = '" + Templatenumber + "' AND STATUS_ID = '" + strStatusId + "' AND ACTION_ID = '" + intActionID + "' and Prefix_Activity_Id=" + OrgAppId + " order by action_fieldname desc";
App1 = Obj.Cmd.ExecuteReader();
if (App1.HasRows)
{
while (App1.Read())
{
strExecQuery = App1["ACTION_EXECUTIONQUERY"].ToString();
}
}
App1.Close();
strExecutionQuery = strExecQuery;
if (strExecutionQuery != "")
{
strExecutionQueryArray = strExecutionQuery.Split(';');
for (int intExec = 0; intExec < strExecutionQueryArray.Length; intExec++)
{
strUnitQuery = strExecutionQueryArray[intExec].ToString();
strExecutionQueryInput = GetStringInBetween('[', ']', strUnitQuery);
strExecutionQueryInput = strExecutionQueryInput.TrimEnd(',');

if (strExecutionQueryInput != "")
{
strExecutionInputArray = strExecutionQueryInput.Split(',');

for (int intInp = 0; intInp < strExecutionInputArray.Length; intInp++)
{
strExecutionParamName = strExecutionInputArray[intInp].TrimStart('[');
strExecutionParamName = strExecutionParamName.TrimEnd(']');

if (blntblFlag == true)
{
break;
}
if (strTableUsed.Contains(",") == true)
{
strTableArray = strTableUsed.Split(',');
for (int inth = 0; inth < strTableArray.Length; inth++)
{
if (strExecutionParamName.ToUpper().Trim() == strTableArray[inth].ToString().ToUpper().Trim())
{
if (viewStr == "Y" && Session["FieldWithValue"] == null)
{
blntblFlag = false;
}
else
{
blntblFlag = true;
break;
}
}
else
{
blntblFlag = false;
}
}
}
else
{
if (strExecutionParamName.ToUpper() == strTableUsed.ToUpper().Trim())
{
if (viewStr == "Y" && Session["FieldWithValue"] == null)
{
blntblFlag = false;
}
else
{
blntblFlag = true;
}
}
else
{
blntblFlag = false;
}
}
}
if (blntblFlag == true)
{
strTestQry = strUnitQuery;
if (Session["FieldWithValue"] == null)
{
strFirstVar = "";
}
else
{
strFirstVar = Session["FieldWithValue"].ToString();
}
strTempQuery = GetStringInBetween('[', ']', strTestQry);
strTempQuery = strTempQuery.TrimEnd(',');
if (strTempQuery != "")
{
strArrayTemp = strTempQuery.Split(',');

if (strFirstVar != "")
{
strTrimColon = strFirstVar.TrimEnd(':');
strTrimTilde = strTrimColon.Split(':');
for (int intB = 0; intB < strTrimTilde.Length; intB++)
{
strBeforeTrim = strTrimTilde[intB].ToString();
strBeforeTrim = strBeforeTrim.TrimEnd('~');
strStoreTilde = strBeforeTrim.Split('~');
strFinalQuery = strTestQry;
for (int intC = 0; intC < strStoreTilde.Length; intC++) // for all dataelement columns
{
strTempField = strStoreTilde[intC].ToString();
strTempFieldArray = strTempField.Split('^');

for (int intU = 0; intU < strArrayTemp.Length; intU++)
{
if (strArrayTemp[intU].ToString().ToUpper().Trim() == "[" + strTempFieldArray[0].ToString().ToUpper().Trim() + "]")
{
if (strTempFieldArray[1].ToString() != "")
{
if (strFinalQuery.Contains("`"))
{
strFinalQuery = strFinalQuery.Replace("`", "'"); // ranjani
}

strFinalQuery = strFinalQuery.Replace("'" + strArrayTemp[intU].ToString() + "'", "'" + strTempFieldArray[1].ToString() + "'");
}
}
}

} // for all dataelement columns
string strCondition = "";
string strFinalCondition = "";
string[] strConAry;
string strConValue = "";
string strConParam = "";
strCondition = GetStringInBetween('[', ']', strFinalQuery);
strCondition = strCondition.TrimEnd(',');

if (strCondition != "")
{
strConAry = strCondition.Split(',');
for (int k = 0; k < strConAry.Length; k++)
{
strConValue = strConAry[k].ToString();
strConParam = strConAry[k].ToString();

if (strConValue != "")
{
strConValue = strConValue.TrimStart('[');
strConValue = strConValue.TrimEnd(']');

if (strConValue.ToLower() == "activityno")
{
strFinalQuery = strFinalQuery.Replace("" + strConParam + "", "" + strActivityNo + "");
}
else
{
strFinalQuery = strFinalQuery;
}
}
}

}
strDEQuery += strFinalQuery + ";";
strTempField = "";
}
}
}
blntblFlag = false;

}
else
{
strEtemp = HdnFieldval.Value;
strEtemp = strEtemp.Substring(0, strEtemp.Length - 1);
strEResVal = strEtemp.Split('^');
strTestQry = strUnitQuery;
strTempQuery = GetStringInBetween('[', ']', strTestQry);
strTempQuery = strTempQuery.TrimEnd(',');
if (strTempQuery != "")
{
strArrayTemp = strTempQuery.Split(',');
for (int intT = 0; intT < strEResVal.Length; intT++)
{
strEInputValue = strEResVal[intT].Split(':');
for (int intU = 0; intU < strArrayTemp.Length; intU++)
{
strNamedParam = strArrayTemp[intU].ToString();
strTempInput120 = strArrayTemp[intU].ToString().TrimStart('[');
strTempInput120 = strTempInput120.TrimEnd(']');
if (strArrayTemp[intU].ToString().ToUpper().Trim() == "[" + strEInputValue[1].ToString().ToUpper().Trim() + "]")
{
if (strEInputValue[0] == "TB")
{
string sessionName = strEInputValue[2].ToString();
strDEName1 += sessionName + ",";
string DEXMLVal = GETXML(sessionName);
if (DEXMLVal != "" && DEXMLVal != "NULL")
{
string[] tempQryval = Session[sessionName + "val"].ToString().Split(':');
activityid = fun.GetMaxId("ACTIVITY_NO", "[" + strEInputValue[2].ToString() + "]");
string strDETempActID = "";
intTempActivityId = fun.GetCurrentIdentity(tablename.ToString());
string strTempFieldValue1 = "";
string strSessionValue = "";
string strWereQry = "";
string[] strElementField;
string strElementName = "";
string[] strPurpose;
string strFieldName12 = "";
string strFieldValue12 = "";
string[] strTempValue1;
string strTempValue2 = "";
strSessionValue = Session[sessionName].ToString().TrimEnd(':');
strSessionValue = strSessionValue.TrimEnd('~');
strSessionValue = strSessionValue.Replace("'", "");
strSessionValue = strSessionValue.Trim();
string[] strSess123;
strSess123 = strSessionValue.Split(':');
for (int n = 0; n < strSess123.Length; n++)
{
strWereQry = strSess123[n].ToString().TrimEnd('~');
strElementField = strWereQry.Split('~');
for (int g = 0; g < strElementField.Length; g++)
{

strElementName = strElementField[g].ToString();
strPurpose = strElementName.Split('^');
strFieldName12 = strPurpose[0].ToString();
strFieldValue12 = strPurpose[1].ToString();
strFieldName12 = strFieldName12.Replace("'", "");

if (strFieldName12 != "")
{
string strDataType = "";

Qry = " Select Data_Type from information_schema.columns where table_name = '" + strEInputValue[2].ToString() + "' and Column_Name ='" + strFieldName12.Trim() + "'";
Obj.Cmd.CommandText = Qry;
if (Obj.Cmd.ExecuteScalar() != null)
{
strDataType = Obj.Cmd.ExecuteScalar().ToString();
}

if (strDataType != "")
{
if (strDataType.ToUpper() == "DATETIME")
{
strTempFieldValue1 += "convert(datetime,'" + strFieldValue12 + "',3),";

}
else
{
strTempFieldValue1 += "'" + strFieldValue12 + "',";
strTempFieldName1 += strFieldName12 + ",";

}

}
else
{
}
}
}

strTempFieldValue1 = strTempFieldValue1.TrimEnd(',');
strTempFieldValue1 = strTempFieldValue1 + ":";
strTempFieldName1 = strTempFieldName1.TrimEnd(',');

}
strTempFieldValue1 = strTempFieldValue1.TrimEnd(':');
strTempValue1 = strTempFieldValue1.Split(':');
for (int intj = 0; intj < strTempValue1.Length; intj++)
{
strTempValue2 = strTempValue1[intj].ToString();
Qry = "INSERT INTO [" + strEInputValue[2].ToString() + "] VALUES(''," + hdntempno.Value + "," + activityid + "," + intTempActivityId + ",'" + Session["UserName"].ToString() + "',getdate()," + (intj + 1) + "," + strTempValue2.Trim() + ")";
Obj.Cmd.CommandText = Qry;
Obj.Cmd.ExecuteNonQuery();
}
if (strEInputValue[2].ToString() != "")
{
strEInputValue[2] = "'DE" + activityid + "_" + strEInputValue[2].ToString() + "',";
strEInputValue[2] = strEInputValue[2].ToString().TrimEnd(',');
}
blnYflag = true;

}
else
{
if (strEInputValue[2].ToString() != "")
{
strEInputValue[2] = "'" + strEInputValue[2].ToString() + "',";
strEInputValue[2] = strEInputValue[2].ToString().TrimEnd(',');
}
blnYflag = false;
}
Session[sessionName] = "";
Session[sessionName + "val"] = "";
}
else if (strEInputValue[0] == "DA")
{
string strTest = "";
string stFldID = "";
string strConvert = "";
strTest = strEInputValue[2].ToString();
stFldID = strEInputValue[3].ToString();
Obj.Cmd.CommandText = "select date_format from " + strOrgShortName + "wdt_template_properties where FIELD_ID = " + stFldID.ToString() + " and template_no=" + Templatenumber;
if ((Obj.Cmd.ExecuteScalar().ToString() != "") && (Obj.Cmd.ExecuteScalar() != null))
{
strDateFormat = Obj.Cmd.ExecuteScalar().ToString();
}
if (strTest != "")
{
if (strDateFormat != null && strDateFormat != "")
{
if (strDateFormat == "dd/MM/yy")
{
strConvert = "convert(datetime," + strTest + ",3)";
}

else if (strDateFormat == "dd-MMM-yyyy" || strDateFormat == "dd-MM-yyyy")
{
strConvert = "convert(datetime," + strTest + ",105)";
}
else if (strDateFormat == "dd-MM-yy")
{
strConvert = "convert(datetime," + strTest + ",5)";
}

else if (strDateFormat == "MM-dd-yyyy")
{
strConvert = "convert(datetime," + strTest + ",110)";
}

else if (strDateFormat == "MM/dd/yy")
{
strConvert = "Convert(datetime," + strTest + ",1)";
}

else if (strDateFormat == "dd.MM.yy")
{
strConvert = "Convert(datetime," + strTest + "',4)";
}
else if (strDateFormat == "yy/MM/dd")
{
strConvert = "Convert(datetime,'" + strTest + ",11)";
}
else if (strDateFormat == "ddMMMyy")
{
strConvert = "Convert(datetime," + strTest + ",06)";
}
else if (strDateFormat == "MM-dd-yy")
{
strConvert = "Convert(datetime," + strTest + ",10)";
}
}

}

if (strEInputValue[2].ToString() != "NULL")
{
strEInputValue[2] = strConvert.ToString() + ",";
strEInputValue[2] = strEInputValue[2].ToString().TrimEnd(',');
}

}

else if (strEInputValue[0] == "UL")
{

string filename = "";
string[] fileNames;
if (strEInputValue[2].ToString() == "L")
{
string strLinkFile = "";
strLinkFile = strEInputValue[3].ToString().Replace("'", "");
filename = strLinkFile.Trim();
}
else
{
HttpFileCollection Files;
Files = Request.Files;
fileNames = Files.AllKeys;
filename = Files[cntval].FileName;
System.Random rndm = new System.Random();

if (filename == "" || filename.Trim().Equals(""))
{
filename = "";
}
else
{
if (Files[cntval].ContentType == "image/gif" || Files[cntval].ContentType == "image/jpg" || Files[cntval].ContentType == "image/jpeg" || Files[cntval].ContentType == "image/pjpeg" || Files[cntval].ContentType == "image/bmp" || Files[cntval].ContentType == "image/GIF" || Files[cntval].ContentType == "image/JPG" || Files[cntval].ContentType == "image/JPEG" || Files[cntval].ContentType == "image/PJPEG" || Files[cntval].ContentType == "image/BMP")
{
filename = filename.Substring(filename.LastIndexOf("\\") + 1);
string path = ConfigurationSettings.AppSettings["ImageUpload"];
path = Server.MapPath(path);
filename = filename.Replace(" ", "_");
filename = (rndm.Next() + "_" + filename);
Files[cntval].SaveAs(path + "\\" + filename);
}
else
{
filename = filename.Substring(filename.LastIndexOf("\\") + 1);
string path = ConfigurationSettings.AppSettings["uploadDocs"];
path = Server.MapPath(path);
if (!System.IO.Directory.Exists(path))
{
try
{
System.IO.Directory.CreateDirectory(path);
}
catch (Exception ex)
{
throw ex;
}
}
filename = filename.Replace(" ", "_");
filename = (rndm.Next() + "_" + filename);
Files[cntval].SaveAs(path + "\\" + filename);
}
}
cntval++;
}
if (strEInputValue[2].ToString() != "NULL")
{
strEInputValue[2] = "'" + filename + "',";
strEInputValue[2] = strEInputValue[2].ToString().TrimEnd(',');
}

}
if (strEInputValue[2].ToString() != "")
{
if (strTestQry.Contains("`"))
{
strTestQry = strTestQry.Replace("`", "'"); // ranjani
}

string strAutoNo = "";
Obj.Cmd.CommandText = "SELECT IS_AUTONUMBER FROM " + strOrgShortName + "WDT_TEMPLATE_PROPERTIES WHERE FIELD_ID=(SELECT FIELD_ID FROM " + strOrgShortName + "WDT_TEMPLATE_FIELDS WHERE FIELD_NAME='" + strEInputValue[1].ToString() + "' AND TEMPLATE_NO=" + Templatenumber + ")AND TEMPLATE_NO=" + Templatenumber;
if (Obj.Cmd.ExecuteScalar() != null)
{
strAutoNo = Obj.Cmd.ExecuteScalar().ToString();
}

if (strAutoNo == "Y")
{

string strAutoNumValue = "";
Obj.Cmd.CommandText = "SELECT AUTONUM_VALUE FROM " + strOrgShortName + "WDT_TEMPLATE_PROPERTIES WHERE FIELD_ID=(SELECT FIELD_ID FROM " + strOrgShortName + "WDT_TEMPLATE_FIELDS WHERE FIELD_NAME='" + strEInputValue[1].ToString() + "' AND TEMPLATE_NO=" + Templatenumber + ")AND TEMPLATE_NO=" + Templatenumber;
if (Obj.Cmd.ExecuteScalar() != null)
{
strAutoNumValue = Obj.Cmd.ExecuteScalar().ToString();
}

if (strTestQry.ToUpper().Contains("INSERT INTO [" + strTblName.ToUpper() + "]")) // to check with meena17108 855PM
{
string strAutoValue = GetAutoNumber(strAutoNo, strEInputValue[1].ToString(), strAutoNumValue);
strTestQry = strTestQry.Replace("'" + strArrayTemp[intU].ToString() + "'", "'" + strAutoValue + "'");
}
else
{
strTestQry = strTestQry.Replace("'" + strArrayTemp[intU].ToString() + "'", strEInputValue[2].ToString());
}

}
else
{
strTestQry = strTestQry.Replace("'" + strArrayTemp[intU].ToString() + "'", strEInputValue[2].ToString());
}
}
}
if (strTestQry.Contains(strNamedParam) == true)
{
if (strNamedParam.ToLower() == "[username]")
{
strTestQry = strTestQry.Replace("'" + strNamedParam + "'", "'" + Session["UserName"].ToString() + "'");
}
}
if (strTempInput120.ToLower() == "userrole")
{
strQuery123 = strTestQry.Replace("" + strNamedParam + "", "" + Session["sesUserType"].ToString() + "");
}
if (strTempInput120.ToLower() == "activityno")
{
if (Qryflag == "D")
{
if (strTestQry.ToUpper().Contains("INSERT INTO [" + strTblName.ToUpper() + "]"))
{
Obj.Cmd.CommandText = "Select IDENT_CURRENT('" + strTblName + "')";
if (Obj.Cmd.ExecuteScalar() != null)
{
strActivityId = Obj.Cmd.ExecuteScalar().ToString();
Obj.Cmd.CommandText = "select count(*) from [" + strTblName + "]";
if (Obj.Cmd.ExecuteScalar() != null)
{
strCnt = Obj.Cmd.ExecuteScalar().ToString();
if (strCnt == "0") // Changes done for button mismatch -- Rsubbu@091009
{
strActivityId = strActivityId;
}
else
{
strActivityId = Convert.ToString(int.Parse(strActivityId) + 1);
}
}
strQuery123 = strTestQry.Replace("" + strNamedParam + "", "" + strActivityId + "");
}
}
else
{
strQuery123 = strTestQry.Replace("" + strNamedParam + "", "" + strActivityNo + "");
}
}
else
{
strQuery123 = strTestQry.Replace("" + strNamedParam + "", "" + strActivityNo + "");
}
}
else
{
if (strTestQry.Contains("`"))
{
strTestQry = strTestQry.Replace("`", "'"); // ranjani
}
strQuery123 = strTestQry;
}

if (strNamedParam.ToLower() == "[deptname]") // for department name replace
{
strTestQry = strTestQry.Replace("" + strNamedParam + "", "" + Session["sesDeptName"].ToString().Trim() + "");
}

if (strNamedParam.ToLower() == "[orgid]") // for orgid replace
{
strTestQry = strTestQry.Replace("" + strNamedParam + "", "" + Session["sesOrgId"].ToString() + "");
}

if (strNamedParam.ToLower() == "[contactperson]") // for Organisation Contact Person replace
{
strTestQry = strTestQry.Replace("" + strNamedParam + "", "" + Session["SesCntPerName"].ToString().Trim() + "");
}

if (strNamedParam.ToLower() == "[usermailid]") // for Organisation Contact Person mail id replace
{
strTestQry = strTestQry.Replace("" + strNamedParam + "", "" + Session["sesUserMailId"].ToString().Trim() + "");
}
if (strTempInput120.ToUpper() == tablename.ToUpper())
{
strTblName = strTempInput120;
}
else if (strQuery123.StartsWith("exec") == true || strQuery123.StartsWith("EXEC") == true)
{
strTblName = tablename;
}
}
}
}
}
}
else
{

}
if (strQuery123.Contains("[activityno]"))
{
if (Qryflag == "D")
{
Obj.Cmd.CommandText = "Select IDENT_CURRENT('" + strTblName + "')";
if (Obj.Cmd.ExecuteScalar() != null)
{
strActivityId = Obj.Cmd.ExecuteScalar().ToString();
Obj.Cmd.CommandText = "select count(*) from [" + strTblName + "]";
if ((Obj.Cmd.ExecuteScalar() != null) || (Obj.Cmd.ExecuteScalar().ToString() != ""))
{
strCnt = Obj.Cmd.ExecuteScalar().ToString();
strQuery123 = strQuery123.Replace("[activityno]", strActivityId);
}
}
}
else
{
strQuery123 = strQuery123.Replace("[activityno]", strActivityNo);
}
}
if (strDEQuery != "")
{
strQuery110 += strDEQuery + ";";
}
else
{
strQuery110 += strQuery123 + ";";
}
}
if (Session["sesUserType"].ToString() == "F & A" || Session["sesUserType"].ToString() == "F & A Manager")
{
obj3.ds = new DataSet();
string FldTotalAmt = "select total_amt,total_Corp from dbo.tata_travel_expenseinfonew where exp_id='" + Session["ExpId"].ToString() + "'";
obj3.OleAdp = new System.Data.SqlClient.SqlDataAdapter(FldTotalAmt, obj3.Con);
obj3.OleAdp.Fill(obj3.ds, "Fields");
if (obj3.ds.Tables[0].Rows.Count > 0)
{
AppTotalAmt = obj3.ds.Tables[0].Rows[0]["total_amt"].ToString();
AppTotalCCAmt = obj3.ds.Tables[0].Rows[0]["total_Corp"].ToString();
}
}
Obj.Cmd.CommandText = strQuery110;
try
{
Obj.Cmd.ExecuteScalar();
if (strActivityNo != null && strActivityNo.ToString() != "")
{
string strTempFlag = "";
if (strQuery110.ToUpper().Contains("INSERT INTO [" + strTblName.ToUpper() + "]")) // to check with meena17108 855PM
{
strTempFlag = "Y";
}
string strTempActNo = "";
strTempActNo = strActivityNo.ToString();
if (Qryflag.ToString() == "D")
{

Obj.Cmd.CommandText = "Select IDENT_CURRENT('" + strTblName + "')";
if (Obj.Cmd.ExecuteScalar() != null)
{
strActivityId = Obj.Cmd.ExecuteScalar().ToString();
if (strTempFlag == "Y")
{
Obj.Cmd.CommandText = "UPDATE [" + strTblName + "] SET Template_No = '" + Templatenumber + "',Created_By = '" + Session["UserName"].ToString() + "',Created_on=getdate(),Temp_Activity_No = '" + strTempActNo + "', status_Id = " + strToStatusId + ",Button_id = '" + strButtonId + "',Wf_Id = '" + strWfId + "',Prefix_Activity_Id='" + OrgAppId + "' WHERE Activity_No = '" + strActivityId + "'";
}
else
{
Obj.Cmd.CommandText = "UPDATE [" + strTblName + "] SET status_Id = " + strToStatusId + ",Button_id = '" + strButtonId + "',Wf_Id = '" + strWfId + "' ,Prefix_Activity_Id='" + OrgAppId + "' WHERE Activity_No = '" + strActivityId + "'";
}
Obj.Cmd.ExecuteNonQuery();
}
}
else
{
strActivityId = strActivityNo.ToString();
if (strActivityId.Contains(",") == true)
{
Obj.Cmd.CommandText = "Select IDENT_CURRENT('" + strTblName + "')";

if (Obj.Cmd.ExecuteScalar() != null)
{
strActivityId = Obj.Cmd.ExecuteScalar().ToString();
}
}
else
{

strActivityId = strActivityNo.ToString();
}
if (strTempFlag == "Y")
{
string strActivityIdent = "";
Obj.Cmd.CommandText = "Select ident_current('" + strTblName + "')";

if (Obj.Cmd.ExecuteScalar() != null)
{
strActivityIdent = Obj.Cmd.ExecuteScalar().ToString();
}
Obj.Cmd.CommandText = "UPDATE [" + strTblName + "] SET template_no = '" + Templatenumber + "',created_by = '" + Session["UserName"].ToString() + "',Created_on=getdate(),temp_activity_No = '" + strActivityIdent + "', status_Id = " + strToStatusId + ",button_id = '" + strButtonId + "',wf_Id = '" + strWfId + "',Prefix_Activity_Id='" + OrgAppId + "' WHERE activity_no = '" + strActivityIdent + "'";
}
else
{
strActivityId = strActivityNo.ToString();
Obj.Cmd.CommandText = "UPDATE [" + strTblName + "] SET status_Id = " + strToStatusId + ",Button_id = '" + strButtonId + "',Wf_Id = '" + strWfId + "',Prefix_Activity_Id='" + OrgAppId + "' WHERE Activity_No = '" + strActivityId + "'";

}

Obj.Cmd.ExecuteNonQuery();

}
}
else
{
Obj.Cmd.CommandText = "Select Ident_Current('" + strTblName + "')";

if (Obj.Cmd.ExecuteScalar() != null)
{
strActivityId = Obj.Cmd.ExecuteScalar().ToString();
Obj.Cmd.CommandText = "UPDATE [" + strTblName + "] SET Template_No = '" + Templatenumber + "',Created_By = '" + Session["UserName"].ToString() + "',Created_on=getdate(),Temp_Activity_No = '" + strActivityId + "', status_Id = " + strToStatusId + ",Button_id = '" + strButtonId + "',Wf_Id = '" + strWfId + "' ,Prefix_Activity_Id='" + OrgAppId + "' WHERE Activity_No = '" + strActivityId + "'";
Obj.Cmd.ExecuteNonQuery();
}
}
sqlTrans.Commit(); // Meena
if (Session["sesUserType"].ToString() == "F & A" || Session["sesUserType"].ToString() == "F & A Manager")
{
if (strQuery110.Contains("Query"))
{
obj3.ds = new DataSet();
string FldValue1 = "select total_amt,total_Corp from dbo.tata_travel_expenseinfonew where exp_id='" + Session["ExpId"].ToString() + "'";
obj3.OleAdp = new System.Data.SqlClient.SqlDataAdapter(FldValue1, obj3.Con);
obj3.OleAdp.Fill(obj3.ds, "Fields");
if (obj3.ds.Tables[0].Rows.Count > 0)
{
AppTotalAmtEnd = obj3.ds.Tables[0].Rows[0]["total_amt"].ToString();
AppTotalCCAmtEnd = obj3.ds.Tables[0].Rows[0]["total_Corp"].ToString();
}
if (AppTotalAmtEnd != AppTotalAmt || AppTotalCCAmt != AppTotalCCAmtEnd)
{
Session["AppStatus"] = "Query";
}

}
if (strQuery110.Contains("Clear"))
{
obj3.ds = new DataSet();
string FldValue1 = "select total_amt,total_Corp from dbo.tata_travel_expenseinfonew where exp_id='" + Session["ExpId"].ToString() + "'";
obj3.OleAdp = new System.Data.SqlClient.SqlDataAdapter(FldValue1, obj3.Con);
obj3.OleAdp.Fill(obj3.ds, "Fields");
if (obj3.ds.Tables[0].Rows.Count > 0)
{
AppTotalAmtEnd = obj3.ds.Tables[0].Rows[0]["total_amt"].ToString();
AppTotalCCAmtEnd = obj3.ds.Tables[0].Rows[0]["total_Corp"].ToString();
}
if (AppTotalAmtEnd != AppTotalAmt || AppTotalCCAmt != AppTotalCCAmtEnd)
{
Session["AppStatus"] = "Clear1";
}
}
if (strQuery110.Contains("Rejected"))
{
Session["AppStatus"] = "Reject";
}
if (strQuery110.Contains("Hold"))
{
Session["AppStatus"] = "Hold";
}
if (strQuery110.Contains("Returned"))
{
Session["AppStatus"] = "Return";
}
if (strQuery110.Contains("Posted"))
{
obj3.ds = new DataSet();
string FldValue1 = "select total_amt,total_Corp from dbo.tata_travel_expenseinfonew where exp_id='" + Session["ExpId"].ToString() + "'";
obj3.OleAdp = new System.Data.SqlClient.SqlDataAdapter(FldValue1, obj3.Con);
obj3.OleAdp.Fill(obj3.ds, "Fields");
if (obj3.ds.Tables[0].Rows.Count > 0)
{
AppTotalAmtEnd = obj3.ds.Tables[0].Rows[0]["total_amt"].ToString();
AppTotalCCAmtEnd = obj3.ds.Tables[0].Rows[0]["total_Corp"].ToString();
}
if (AppTotalAmtEnd != AppTotalAmt || AppTotalCCAmt != AppTotalCCAmtEnd)
{
Session["AppStatus"] = "Post";
}
}
}
if (HdnFieldval.Value != "")
{
string actid = "";
string strTablename = "";
string strWDTTempname = "";
string fldid = "";
string fieldname = "";
string fieldtype = "";
string fieldval = "";
string AppId = Templatenumber.Substring(4, 2);
string AppName = "";
int ChgActID = 0;
Obj.Cmd.CommandText = "select wdt_name from " + strOrgShortName + "wf_step_wdt where wf_id=" + wid + " and step_id=" + strStepno + " and wdt_id=" + Templatenumber;
if (Obj.Cmd.ExecuteScalar() != null)
{
strWDTTempname = Obj.Cmd.ExecuteScalar().ToString();
}
Obj.Cmd.CommandText = "select Application_name from Application_master where application_id=" + AppId;
if (Obj.Cmd.ExecuteScalar() != null)
{
AppName = Obj.Cmd.ExecuteScalar().ToString();
AppName = AppName + "_";
}
strTablename = strOrgShortName + AppName + strWDTTempname;
Obj.Cmd.CommandText = "Select IDENT_CURRENT('" + strTablename + "')";
if (Obj.Cmd.ExecuteScalar() != null)
{
actid = Obj.Cmd.ExecuteScalar().ToString();
ChgActID = Int32.Parse(actid);
}
DataSet dsChg = new DataSet();
string QryChg = "SELECT * FROM [" + strTablename + "] where activity_no=" + ChgActID;
Obj.OleAdp = new System.Data.SqlClient.SqlDataAdapter(QryChg, Obj.Con);
Obj.OleAdp.Fill(dsChg, "Status");
if (dsChg.Tables["Status"].Rows.Count > 0)
{
drChangeValue = dsChg.Tables["Status"].Rows[0];
string strClmnName = "REVISION_NO";
string strTbleName = strOrgShortName + "WDT_Deliverable_Transaction";
int Rev_No = fun.GetMaxId(strClmnName, strTbleName);
string strTmpValue = "";
string[] strVTmpValue;
string[] strWTmpValue;
string[] strFieldType;
string[] strFieldId1;
string[] strFieldValue;
string strFieldQry = "";
fldid = "";
Obj.Cmd.CommandText = "Select field_id from " + strOrgShortName + "wdt_template_fields where template_no='" + Templatenumber + "' Order by field_id";
App1 = Obj.Cmd.ExecuteReader();
if (App1.HasRows)
{
while (App1.Read())
{
fldid += App1["FIELD_ID"].ToString() + "~";
}
}
App1.Close();
fldid = fldid.ToString().TrimEnd('~');
strTmpValue = HdnFieldval.Value;
strTmpValue = strTmpValue.Substring(0, strTmpValue.Length - 1);
strVTmpValue = strTmpValue.Split('^');
for (int i = 0; i < strVTmpValue.Length; i++)
{
strWTmpValue = strVTmpValue[i].Split(':');
fieldtype += strWTmpValue[0].ToString() + "~";
fieldval += strWTmpValue[2].ToString() + "~";
}
fieldtype = fieldtype.TrimEnd('~');
fieldval = fieldval.TrimEnd('~');
strFieldType = fieldtype.Split('~');
strFieldId1 = fldid.Split('~');
strFieldValue = fieldval.Split('~');
string QryID = "";
for (int i = 0; i <= strFieldType.Length - 1; i++)
{
if (strFieldType[i].ToString() != "TB")
{
if ((strFieldType[i].ToString() == "UL") && (hdnULValue.Value != "Image"))
{
HttpFileCollection Files;
Files = Request.Files;
string[] fileNames = Files.AllKeys;
string filename = Files[cntval1].FileName;
System.Random rndm = new System.Random();
if (filename == "" || filename.Trim().Equals(""))
{
}
else
{
if (Files[cntval1].ContentType == "image/gif" || Files[cntval1].ContentType == "image/jpg" || Files[cntval1].ContentType == "image/jpeg" || Files[cntval1].ContentType == "image/pjpeg" || Files[cntval1].ContentType == "image/bmp")
{
filename = filename.Substring(filename.LastIndexOf("\\") + 1);
string path = ConfigurationSettings.AppSettings["ImageUpload"];
path = Server.MapPath(path);
filename = filename.Replace(" ", "_");
filename = (rndm.Next() + "_" + filename);
Files[cntval1].SaveAs(path + "\\" + filename);
}
else
{
filename = filename.Substring(filename.LastIndexOf("\\") + 1);
string path = ConfigurationSettings.AppSettings["uploadDocs"];
path = Server.MapPath(path);
if (!System.IO.Directory.Exists(path))
{
try
{
System.IO.Directory.CreateDirectory(path);
}
catch (Exception ex)
{
throw ex;
}
}
filename = filename.Replace(" ", "_");
filename = (rndm.Next() + "_" + filename);
Files[cntval1].SaveAs(path + "\\" + filename);
}
}
cntval1++;
strFieldULValue = "'" + filename + "',";
strFieldULValue = strFieldULValue.TrimEnd(',');
if (drChangeValue[(int.Parse(strFieldId1[i].ToString())) + 8] != null && drChangeValue[(int.Parse(strFieldId1[i].ToString())) + 8].ToString() != "")
{
//QryID = "insert into " + strOrgShortName + "WDT_Deliverable_Transaction(WF_ID,TEMPLATE_NO,ACTIVITY_ID,FIELD_ID,FIELD_VALUE,CREATED_BY,UPDATED_DATE,REVISION_NO,PREFIX_ACTIVITY_ID) values(" + wid + "," + Templatenumber + "," + actid + "," + strFieldId1[i].ToString() + "," + strFieldULValue + ",'" + Session["UserName"].ToString() + "',getdate()" + "," + Rev_No + "," + OrgAppId + ")";
//QryID = "insert into " + strOrgShortName + "WDT_Deliverable_Transaction(WF_ID,TEMPLATE_NO,ACTIVITY_ID,FIELD_ID,FIELD_VALUE,CREATED_BY,UPDATED_DATE,REVISION_NO,PREFIX_ACTIVITY_ID) values(" + wid + "," + Templatenumber + "," + ChgActID + "," + strFieldId1[i].ToString() + ",'" + fun.StringHand(drChangeValue[(int.Parse(strFieldId1[i].ToString())) + 8].ToString()) + "','" + Session["UserName"].ToString() + "',getdate()" + "," + Rev_No + "," + OrgAppId + ")";
}
else
{
//QryID = "insert into " + strOrgShortName + "WDT_Deliverable_Transaction(WF_ID,TEMPLATE_NO,ACTIVITY_ID,FIELD_ID,FIELD_VALUE,CREATED_BY,UPDATED_DATE,REVISION_NO,PREFIX_ACTIVITY_ID) values(" + wid + "," + Templatenumber + "," + ChgActID + "," + strFieldId1[i].ToString() + "," + "''" + ",'" + Session["UserName"].ToString() + "',getdate()" + "," + Rev_No + "," + OrgAppId + ")";

}
//Obj.Cmd.CommandText = QryID;
//Obj.Cmd.ExecuteNonQuery();
}
else
{
if (drChangeValue[(int.Parse(strFieldId1[i].ToString())) + 8] != null && drChangeValue[(int.Parse(strFieldId1[i].ToString())) + 8].ToString() != "")
{
//QryID = "insert into " + strOrgShortName + "WDT_Deliverable_Transaction(WF_ID,TEMPLATE_NO,ACTIVITY_ID,FIELD_ID,FIELD_VALUE,CREATED_BY,UPDATED_DATE,REVISION_NO,PREFIX_ACTIVITY_ID) values(" + wid + "," + Templatenumber + "," + ChgActID + "," + strFieldId1[i].ToString() + ",'" + fun.StringHand(drChangeValue[(int.Parse(strFieldId1[i].ToString())) + 8].ToString()) + "','" + Session["UserName"].ToString() + "',getdate()" + "," + Rev_No + "," + OrgAppId + ")";
}
else
{
//QryID = "insert into " + strOrgShortName + "WDT_Deliverable_Transaction(WF_ID,TEMPLATE_NO,ACTIVITY_ID,FIELD_ID,FIELD_VALUE,CREATED_BY,UPDATED_DATE,REVISION_NO,PREFIX_ACTIVITY_ID) values(" + wid + "," + Templatenumber + "," + ChgActID + "," + strFieldId1[i].ToString() + "," + "''" + ",'" + Session["UserName"].ToString() + "',getdate()" + "," + Rev_No + "," + OrgAppId + ")";

}
//Obj.Cmd.CommandText = QryID;
//Obj.Cmd.ExecuteNonQuery();

}
}
else
{
strFieldValue[i] = "'DE_" + strFieldValue[i] + "'";

//QryID = "insert into " + strOrgShortName + "WDT_Deliverable_Transaction(WF_ID,TEMPLATE_NO,ACTIVITY_ID,FIELD_ID,FIELD_VALUE,CREATED_BY,UPDATED_DATE,REVISION_NO) values(" + wid + "," + Templatenumber + "," + ChgActID + "," + strFieldId1[i].ToString() + "," + strFieldValue[i] + ",'" + Session["UserName"].ToString() + "',getdate()" + "," + Rev_No + ")";
//Obj.Cmd.CommandText = QryID;
//Obj.Cmd.ExecuteNonQuery();
}
}


}

}
if (File.Exists(Xmlfilename))
{
File.Delete(Xmlfilename);

}
return true;

}
catch (SqlException sqlex1)
{
if (sqlex1.Number != 1205)
{
sqlTrans.Rollback();
}

throw (sqlex1);

}
catch (Exception excLoc)
{
strErrorSource = excLoc.Source;
strErrorMessage2 = excLoc.Message;
sqlTrans.Rollback();
return false;
}
finally
{
sqlTrans.Dispose();
}
}
}
catch (SqlException sqlex)
{
if (sqlex.Number != 1205)
{
sqlTrans.Rollback();
}

throw (sqlex);

}
catch (Exception excTest)
{
sqlTrans.Rollback();
return false;
}
finally
{
sqlTrans.Dispose();
}
return true;
}
return true;
}

The above function is called when received button is clicked to convert the status from approved to received. some times its working fine but most often when db is busy or many users using its giving error sql trtansaction has completed its no longer usable....

Kindly help.....

Aravind.T
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site