Not able to retrieve data from SQL Server
Hi all
I m developing a small web app. using Visual Studio 2005( C#, SQL Server 2005 ). i m using Duwamish Architecture.
The web page works like this:
there is one textbox and two radiobuttons and a dropdownlist(for displaying project version).
on entering ProjectID in the textbox,i will check radio button named new, then it will check with the database whether it exists or not, if it exists then a new version will be created and it will be displayed in the dropdownlist. i am not able to retrieve the data.
here are the codes:
Code Behind page:
using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using BusinessFacade;
using QMS.Common;
public partial class QMS_ProjectGoalsMstr : System.Web.UI.Page
{
DataSet dsPGM1 = new DataSet();
DataTable dTPGM = new DataTable();
//string strProj;
QMS.Common.Class1 objCommon;
QMS_ProjectGoalsMstrBF objQms;
Boolean blnFlag;
protected void Page_Load(object sender, EventArgs e)
{
Session["UID"] = "10082";
if (!Page.IsPostBack)
{
//FillDropDowns();
blnFlag = false;
}
btnsubmit.Attributes.Add("OnClick", "return fnValidate();");
rdNewPee.Attributes.Add("OnClick", "return fnCheck();");
rdModifyPee.Attributes.Add("OnClick", "return fnCheck();");
}
protected void ddlPee_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlPee.SelectedValue != "Select")
{
objQms = new QMS_ProjectGoalsMstrBF();
dTPGM = objQms.GetProjectGoalsBF(txtProjId.Text, Convert.ToInt32(ddlPee.SelectedValue));
if (dTPGM.Rows.Count > 0)
{
rdTechH.Checked = false;
rdTechL.Checked = false;
rdTechM.Checked = false;
txtPrepOn.Text = dTPGM.Rows[0]["PREP_ON"].ToString();
txtQtyReq.Text = dTPGM.Rows[0]["QTY_REQ"].ToString();
txtUnitSize.Text = dTPGM.Rows[0]["UOM"].ToString();
txtTresSize.Text = dTPGM.Rows[0]["THD_SIZE"].ToString();
txtTresEff.Text = dTPGM.Rows[0]["THD_EFFORT"].ToString();
txtSch.Text = dTPGM.Rows[0]["THD_SCHEDULE"].ToString();
txtMeanSize.Text = dTPGM.Rows[0]["MEAN_SIZE"].ToString();
txtMeanEff.Text = dTPGM.Rows[0]["MEAN_EFFORT"].ToString();
txtMeanSch.Text = dTPGM.Rows[0]["MEAN_SCHEDULE"].ToString();
txtProjGoalsIpQual.Text = dTPGM.Rows[0]["IP_QUALITY"].ToString();
txtProjGoalsEff.Text = dTPGM.Rows[0]["EFFORT"].ToString();
txtSch.Text = dTPGM.Rows[0]["SCHEDULE"].ToString();
txtSize.Text = dTPGM.Rows[0]["SIZE"].ToString();
txtReq.Text = dTPGM.Rows[0]["REQUIREMENTS"].ToString();
txtCode.Text = dTPGM.Rows[0]["CODEUT"].ToString();
txtDesign.Text = dTPGM.Rows[0]["DESIGN"].ToString();
txtOthTst.Text = dTPGM.Rows[0]["OTHERTESTS"].ToString();
txtPlanSize.Text = dTPGM.Rows[0]["PLANNED_SIZE"].ToString();
txtActSize.Text = dTPGM.Rows[0]["ACTUAL_SIZE"].ToString();
txtPostRelDef.Text = dTPGM.Rows[0]["POST_RELEASE_DEFECTS"].ToString();
txtPostRelChange.Text = dTPGM.Rows[0]["POST_RELEASE_CHANGES"].ToString();
txtExp.Text = dTPGM.Rows[0]["EXPERIENCE"].ToString();
txtRem.Text = dTPGM.Rows[0]["REMARKS"].ToString();
txtBudEff.Text = dTPGM.Rows[0]["BUDGETED_EFFORT"].ToString();
if (dTPGM.Rows[0]["TECHNOLOGY"].ToString() == "H")
rdTechH.Checked = true;
else if (dTPGM.Rows[0]["TECHNOLOGY"].ToString() == "M")
rdTechM.Checked = true;
else if (dTPGM.Rows[0]["TECHNOLOGY"].ToString() == "L")
rdTechL.Checked = true;
}
}
else
{
ClearTextBoxes();
}
}
protected void rdNewPee_CheckedChanged(object sender, EventArgs e)
{
ClearTextBoxes();
hdnNew.Value = rdNewPee.Checked.ToString();
ddlPee.DataSource = "";
ddlPee.DataBind();
FillDropDowns();
}
protected void rdModifyPee_CheckedChanged(object sender, EventArgs e)
{
ClearTextBoxes();
hdnModify.Value = rdModifyPee.Checked.ToString();
ddlPee.DataSource = "";
ddlPee.DataBind();
FillDropDowns();
}
private void FillDropDowns()
{
objQms = new QMS_ProjectGoalsMstrBF();
//string strUser = "";
string strVersionMode="";
if (rdNewPee.Checked == true)
strVersionMode = "N";
else
strVersionMode = "M";
dsPGM1 = objQms.GetProjectVersion(txtProjId.Text,strVersion Mode.ToString());
if (dsPGM1.Tables["PROJECTS_GOALS_MSTR"].Rows.Count > 0)
{
ddlPee.DataSource = dsPGM1.Tables["PROJECTS_GOALS_MSTR"];
ddlPee.DataTextField = dsPGM1.Tables["PROJECTS_GOALS_MSTR"].Columns["UPDATE_NO"].ColumnName;
ddlPee.DataValueField = dsPGM1.Tables["PROJECTS_GOALS_MSTR"].Columns["UPDATE_NO"].ColumnName;
ddlPee.DataBind();
}
else
{
//ddlPee.DataSource = "";
//ddlPee.Items.Add(new ListItem("Select", "Select"));
//ddlPee.SelectedValue = "Select";
//ddlPee.DataBind();
}
if (rdNewPee.Checked == true)
{
ddlPee.SelectedValue = dsPGM1.Tables["PROJECTS_GOALS_MSTR"].Rows[0]["UPDATE_NO"].ToString();
ddlPee.Enabled = false;
}
else
{
ddlPee.Items.Add(new ListItem("Select", "Select"));
ddlPee.SelectedValue = "Select";
//ddlPee.SelectedIndex = 0;
ddlPee.Enabled = true;
}
}
protected void btnsubmit_Click(object sender, EventArgs e)
{
Filltable();
}
//private bool Alert(string strMsg)
//{
// string strScript;
// strScript = "<script language=javascript> window.alert(" + "\"" + strMsg + "\"" + ")</script>";
// Page.ClientScript.RegisterStartupScript(this.GetTy pe(), "Alert", strScript);
// return false;
//}
private void Alert(string strMsg)
{
ScriptManager.RegisterStartupScript(this.UpdatePan el1, typeof(string), "alertScript", string.Format("alert('{0}')", strMsg), true);
}
private void ClearTextBoxes()
{
txtActSize.Text = "";
txtBudEff.Text = "";
txtCode.Text = "";
txtDesign.Text = "";
txtExp.Text = "";
txtProjGoalsIpQual.Text = "";
txtProjGoalsEff.Text = "";
txtMeanSize.Text = "";
txtMeanEff.Text = "";
txtMeanSch.Text = "";
txtOthTst.Text = "";
txtPlanSize.Text = "";
txtPostRelChange.Text = "";
txtPostRelDef.Text = "";
txtPrepOn.Text = "";
txtQtyReq.Text = "";
txtRem.Text = "";
txtReq.Text = "";
txtSch.Text = "";
txtSize.Text = "";
txtTresSize.Text = "";
txtTresEff.Text = "";
txtTresSch.Text = "";
txtUnitSize.Text = "";
rdTechH.Checked = false;
rdTechL.Checked = false;
rdTechM.Checked = false;
if (blnFlag == true)
{
rdNewPee.Checked = false;
rdModifyPee.Checked = false;
ddlPee.DataSource = "";
ddlPee.DataBind();
txtProjId.Text = "";
}
}
private void Filltable()
{
string strreturn;
//bool blncheck = true;
//dsPGM1 = (DataSet)["temp"];
//if (dsPGM1.Tables[0].Rows.Count == 0)
//{
//blncheck = Alert("Insert Data");
//}
objQms = new QMS_ProjectGoalsMstrBF();
objCommon =new QMS.Common.Class1();
dTPGM = objQms.GetTempDataBF();
DataRow drRow;
drRow = dTPGM.NewRow();
drRow["PROJECT_ID"] = txtProjId.Text;
drRow["UPDATE_NO"] = ddlPee.SelectedValue;
drRow["PREP_ON"] = objCommon.DateFormat(txtPrepOn.Text,"DD/MM/YYYY");
drRow["QTY_REQ"] = txtQtyReq.Text;
drRow["UOM"] = txtUnitSize.Text;
drRow["THD_SIZE"] = txtTresSize.Text;
drRow["THD_EFFORT"] = txtTresEff.Text;
drRow["THD_SCHEDULE"] = txtTresSch.Text;
drRow["MEAN_SIZE"] = txtMeanSize.Text;
drRow["MEAN_EFFORT"] = txtMeanEff.Text;
drRow["MEAN_SCHEDULE"] = txtMeanSch.Text;
drRow["IP_QUALITY"] = txtProjGoalsIpQual.Text;
drRow["EFFORT"] = txtProjGoalsEff.Text;
drRow["SCHEDULE"] = txtSch.Text;
drRow["SIZE"] = txtSize.Text;
drRow["REQUIREMENTS"] = txtReq.Text;
drRow["CODEUT"] = txtCode.Text;
drRow["DESIGN"] = txtDesign.Text;
drRow["OTHERTESTS"] = txtOthTst.Text;
drRow["PLANNED_SIZE"] = txtPlanSize.Text;
drRow["ACTUAL_SIZE"] = txtActSize.Text;
drRow["POST_RELEASE_DEFECTS"] = txtPostRelDef.Text;
drRow["POST_RELEASE_CHANGES"] = txtPostRelChange.Text;
drRow["EXPERIENCE"] = txtExp.Text;
if (rdTechH.Checked == true)
drRow["TECHNOLOGY"] = "H";
else if (rdTechM.Checked == true)
drRow["TECHNOLOGY"] = "M";
else if(rdTechL.Checked == true)
drRow["TECHNOLOGY"] = "L";
drRow["BUDGETED_EFFORT"] = txtBudEff.Text;
drRow["REMARKS"] = txtRem.Text;
if (rdNewPee.Checked == true)
drRow["CREATED_USER"] = Session["UID"].ToString();
else
drRow["MODIFIED_USER"] = Session["UID"].ToString();
dTPGM.Rows.Add(drRow);
dsPGM1.Tables.Add(dTPGM);
if (rdNewPee.Checked == true)
{
strreturn = objQms.Submit_Pgm_DetailsBF(txtProjId.Text, ddlPee.SelectedValue, dsPGM1.GetXml(), "NEW");
if (strreturn == "True")
{
Alert("Data inserted successfully");
blnFlag = true;
ClearTextBoxes();
}
else
{
Alert("Error in inserting data");
}
}
else if (rdModifyPee.Checked == true)
{
strreturn = objQms.Submit_Pgm_DetailsBF(txtProjId.Text, ddlPee.SelectedValue, dsPGM1.GetXml(), "MODIFY");
if (strreturn == "True")
{
Alert("Data updated successfully");
blnFlag = true;
ClearTextBoxes();
}
else
{
Alert("Error in updating data");
}
}
}
protected void btnCancel_Click(object sender, EventArgs e)
{
Response.Redirect("QMS_ProjectGoalsMstr.aspx");
}
}
Business Facade layer
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.SqlClient;
using QMS.DataAccess;
namespace BusinessFacade
{
public class QMS_ProjectGoalsMstrBF
{
public DataSet GetProjectVersion(string strProj,string strVer)
{
QMS_ProjectGoalsMstrDA objQms = new QMS_ProjectGoalsMstrDA();
return objQms.GetProjectVersionDA(strProj, strVer);
}
public DataSet GetProjectId(string strProj)
{
QMS_ProjectGoalsMstrDA objQms = new QMS_ProjectGoalsMstrDA();
return objQms.GetProjectId(strProj);
}
public DataTable GetTempDataBF()
{
QMS_ProjectGoalsMstrDA objQms = new QMS_ProjectGoalsMstrDA();
return objQms.GetTempDataDA();
}
public string Submit_Pgm_DetailsBF(string strProj, string strUpdateNo, string strXML, string strMode)
{
QMS_ProjectGoalsMstrDA objQms = new QMS_ProjectGoalsMstrDA();
return objQms.Submit_Pgm_DetailsDA(strProj, strUpdateNo, strXML,strMode);
}
public DataTable GetProjectGoalsBF(string strProjID, Int32 intVerNo)
{
QMS_ProjectGoalsMstrDA objQms = new QMS_ProjectGoalsMstrDA();
return objQms.GetProjectGoalsDA(strProjID, intVerNo);
}
}
}
Data Access Layer
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace QMS.DataAccess
{
public class QMS_ProjectGoalsMstrDA
{
public SqlDataAdapter daPGM;
public SqlConnection connPGM;
public SqlCommand cmdPGM;
public QMS_ProjectGoalsMstrDA()
{
daPGM = new SqlDataAdapter();
connPGM = new SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings.Get("ConStr"));
}
public DataSet GetProjectVersionDA(string strProj,string strVer)
{
DataSet dsPGM = new DataSet();
daPGM.TableMappings.Add("Table","PROJECTS_GOALS_MS TR");
cmdPGM = new SqlCommand("QMS_SP_GetProjectVersion", connPGM);
cmdPGM.CommandType = CommandType.StoredProcedure;
cmdPGM.Parameters.Add(new SqlParameter("@Proj_Id", SqlDbType.VarChar, 24));
cmdPGM.Parameters["@Proj_Id"].Value = strProj;
cmdPGM.Parameters.Add(new SqlParameter("@Version", SqlDbType.Char, 1));
cmdPGM.Parameters["@Version"].Value = strVer;
daPGM.SelectCommand = cmdPGM;
daPGM.Fill(dsPGM);
return dsPGM;
}
public DataSet GetProjectId(string strProj)
{
DataSet dsPGM = new DataSet();
cmdPGM = new SqlCommand("QMS_SP_ProjectGoalsMaster_Insert", connPGM);
cmdPGM.CommandType = CommandType.StoredProcedure;
cmdPGM.Parameters.Add(new SqlParameter("@PROJECT_ID", SqlDbType.VarChar, 24));
cmdPGM.Parameters["PROJECT_ID"].Value = strProj;
daPGM.SelectCommand = cmdPGM;
daPGM.Fill(dsPGM);
return dsPGM;
}
public DataTable GetTempDataDA()
{
DataTable dtPGM = new DataTable();
cmdPGM = new SqlCommand("QMS_SP_GetGoalsMasterTemp", connPGM);
cmdPGM.CommandType = CommandType.StoredProcedure;
daPGM.SelectCommand = cmdPGM;
daPGM.Fill(dtPGM);
return dtPGM;
}
public DataTable GetProjectGoalsDA(string strProjID, Int32 intVerNo)
{
DataTable dtPGM = new DataTable();
cmdPGM = new SqlCommand("QMS_SP_GetProjectGoals", connPGM);
cmdPGM.CommandType = CommandType.StoredProcedure;
cmdPGM.Parameters.Add(new SqlParameter("@PROJECT_ID", SqlDbType.VarChar, 24));
cmdPGM.Parameters["@PROJECT_ID"].Value = strProjID;
cmdPGM.Parameters.Add(new SqlParameter("@VER_NO", SqlDbType.Int));
cmdPGM.Parameters["@VER_NO"].Value = intVerNo;
daPGM.SelectCommand = cmdPGM;
daPGM.Fill(dtPGM);
return dtPGM;
}
public string Submit_Pgm_DetailsDA(string strProj,string strUpdateNo,string strXML,string strMode)
{
SqlParameter PrmErr = new SqlParameter();
cmdPGM = new SqlCommand("QMS_SP_ProjectGoalsMaster_Insert", connPGM);
cmdPGM.CommandType = CommandType.StoredProcedure;
cmdPGM.Parameters.Add(new SqlParameter("@ProjectId", SqlDbType.VarChar, 24));
cmdPGM.Parameters["@ProjectId"].Value = strProj;
cmdPGM.Parameters.Add(new SqlParameter("@UpdNo", SqlDbType.VarChar, 5));
cmdPGM.Parameters["@UpdNo"].Value = strUpdateNo;
cmdPGM.Parameters.Add(new SqlParameter("@Data", SqlDbType.Text));
cmdPGM.Parameters["@Data"].Value = strXML;
cmdPGM.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar, 24));
cmdPGM.Parameters["@Mode"].Value = strMode;
PrmErr.ParameterName = "@ErrInfo";
PrmErr.Size = 100;
PrmErr.Direction = ParameterDirection.Output;
cmdPGM.Parameters.Add(PrmErr);
cmdPGM.Connection.Open();
Int32 pgm = cmdPGM.ExecuteNonQuery();
return cmdPGM.Parameters["@ErrInfo"].Value.ToString();
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true);
}
protected virtual void Dispose(bool disposing)
{
if (daPGM != null)
{
if (daPGM.SelectCommand != null)
{
if (daPGM.SelectCommand.Connection != null)
daPGM.SelectCommand.Connection.Dispose();
daPGM.SelectCommand.Dispose();
}
daPGM.Dispose();
daPGM = null;
}
}
}
}
I am not getting any error, build is getting succedded, the thing is that i m not able to retrieve anything from the database, on checking new radiobutton, nothing is getting loaded in the dropdownlist. Please help what is wrong or what i am missing in the code.
|
