over load or not to overload

http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21906738.html

Hi Experts,

Follow up to this

and I went with option 1

But everything i click on the edit link,
my datagrid disappears.

How do solve this

thankful as always


codes
private void BindGrid(string Id)
            {

                  string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  SqlParameter param = new SqlParameter( "@employerId", SqlDbType.VarChar, 50);
                  
                  param.Value = Id;
                  myCommand.Parameters.Add(param);

                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }



called it like this


public void dgCompensation_Edit (object sender, DataGridCommandEventArgs e)
            {
                  dgCompensation.EditItemIndex = e.Item.ItemIndex;
                              BindGrid("");
            }
SirReadAlotAsked:
Who is Participating?
 
existenz2Connect With a Mentor Commented:
I cant really understand everything what you are doing in your code and why you are doing it, mostly because it contains quite some business constraints, but if this stored procedure returns what you need when nothing is selected, yes.
0
 
existenz2Commented:
I would do it this way, but it's a matter of taste and your coding standards:


private void BindGrid() {
   BindGrid("");
}

private void BindGrid(string Id)
          {

               string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
               SqlConnection  myConnection = new SqlConnection(ConnectionString);

               SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
               myCommand.CommandType = CommandType.StoredProcedure;
               SqlParameter param = new SqlParameter( "@employerId", SqlDbType.VarChar, 50);
               
               param.Value = Id;
               myCommand.Parameters.Add(param);

               try
               {
                    myConnection.Open();
                    dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    dgCompensation.DataBind();
               }
               catch
                    (SqlException Sqlexc)
               {
                    lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
               }
          }

0
 
SirReadAlotAuthor Commented:
will try this

and let you know
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SirReadAlotAuthor Commented:
the datagrid still disappears
0
 
existenz2Commented:
Actually, the solution I posted is a combination of both TheLearnedOne's options in your first question.
0
 
existenz2Commented:
Then your problem has nothing to do with overloading it seems. Could you paste the code for the whole page?
0
 
SirReadAlotAuthor Commented:
using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using AutoDataCapture.utilities;


namespace AutoDataCapture
{
      /// <summary>
      /// Summary description for MapCompensationElements.
      /// </summary>
      public class MapCompensationElements : System.Web.UI.Page
      {
            protected System.Web.UI.WebControls.Label lblMappings;
            protected System.Web.UI.WebControls.Label lblPull;
            protected System.Web.UI.WebControls.DataGrid dgCompensation;
            protected System.Web.UI.WebControls.Label lblStatus;
      //      protected string strEmpID;
      
      
            private void Page_Load(object sender, System.EventArgs e)
            {
                  string  employerId;
                  string action = "";
                  int j =0;
                  SqlDataReader dataReader = null;
                 SqlDataReader dReader = null;
          //Response.Write("Gets here");
                  //employerId = Request.Params["PATH_INFO"];
                  
                  if (Request.Params["Employer"] != null && Request.Params["Employer"] != "")
                  {
                        employerId =  Request.Params["Employer"];
                        if (Request.Params["Action"] != null && Request.Params["Action"] != "")
                              action = Request.Params["Action"].ToLower();
                        switch (action)
                        {
                              case "destinationelements":
                                    dataReader = getPolicyAtlasId(employerId);
                                    j = getRemunerationItems(dataReader,employerId);
                                    if (j > 0)
                                    {
                                          dReader = getPolicyAtlasId(employerId);
                                          //BindGrid("employerId");

                                          BindGrid("");

                                          if( !(Page.IsPostBack))
                                          {
                                                BindGrid(employerId);
                                          }
                                          
                                                                              
                                          if (j > 1)
                                                lblPull.Text = j.ToString() + " elements have been added";
                                    else
                                          {
                                                lblPull.Text = j.ToString() + " element has been added";
                                          }
                                                }
                                    else
                                          lblPull.Text = "No elements pulled";
                                    break;
                              case "lastyearsmappings":
                                    j = getMappings(employerId);
                                    if (j > 0)
                                          lblMappings.Text = j + " elements added to the mappings list";
                                    else lblMappings.Text = "No new mappings added to the mappings list.";
                                    break;
                              default:
                                    break;
                        }
                        }
            }


            /* Chi's Enhancements
             * 03/07/2006 */

            private void BindGrid()
            {
                  BindGrid("");
            }


            

            #region BindGrid Code
            private void BindGrid(string Id)
            {

                  string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  SqlParameter param = new SqlParameter( "@employerId", SqlDbType.VarChar, 50);
                  
                  param.Value = Id;
                  myCommand.Parameters.Add(param);

                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }

            #endregion

            #region DataGrid Edit Code
            public void dgCompensation_Edit (object sender, DataGridCommandEventArgs e)
            {
                  dgCompensation.EditItemIndex = e.Item.ItemIndex;
                  BindGrid("");
            }
            #endregion
            #region DataGrid  Cancel Code
            public void dgCompensation_Cancel (object sender, DataGridCommandEventArgs e)
            {
                  dgCompensation.EditItemIndex = -1;
                  BindGrid("");      
            }
            #endregion
            #region DataGrid  ItemDataBound Code
         public void dgCompensation_ItemDataBound(Object Sender, DataGridItemEventArgs e)
            {
   
                  if (e.Item.ItemType == ListItemType.EditItem)
                  {
   
                        DataRowView objDataRowView = (DataRowView)e.Item.DataItem;
   
                        string currentDeduction = (string)objDataRowView[2].ToString();
   
                        DropDownList ctlDropDownList = (DropDownList)e.Item.FindControl("DeductDropDownList");
   
                        ctlDropDownList.SelectedIndex = ctlDropDownList.Items.IndexOf(ctlDropDownList.Items.FindByText(currentDeduction));
                  }
   
            }
#endregion
            #region DataGrid Update Code
            public void dgCompensation_Update(Object Sender, DataGridCommandEventArgs e)
            {
   
                  string employerId = e.Item.Cells[1].Text;
                  string  destinationelements = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
                  int deduction = Int32.Parse(((DropDownList)e.Item.Cells[3].Controls[1]).SelectedItem.Value);
      
                  SqlConnection objConnection = new SqlConnection("Server=(10.217.1.85);Database=ADC11;Password=;Persist Security Info=True;User ID=sa;");
                  SqlCommand updateCommand = new SqlCommand();

                  updateCommand.Connection = objConnection;
                  updateCommand.CommandText = "update DestinationElements set DestinationElement = @DestinationElements, Deduction = @Deduction where EmployerId = @EmployerId";
                  updateCommand.Parameters.Add("@EmployerId", SqlDbType.NVarChar, 20).Value = employerId;
                  updateCommand.Parameters.Add("@DestinationElements", SqlDbType.NChar, 50).Value = destinationelements;
                  updateCommand.Parameters.Add("@Deduction", SqlDbType.Int).Value = deduction;
               
                  try
                  {
                        objConnection.Open();
                        updateCommand.ExecuteNonQuery();
                      }
   
                  catch (SqlException SqlEx)
                  {
                        lblStatus.Text = SqlEx.Message.ToString();
                  }
   
                  catch (Exception ex)
                  {
                        lblStatus.Text = ex.Message.ToString();
                  }
   
                  finally
                  {
                        objConnection.Close();
                  }
   
                  dgCompensation.EditItemIndex = -1;
                  BindGrid("");
   
            }
# endregion
            #region GetDeductionData Code
            public DataTable GetDeductionDataTable()
            {
   
                  if (Cache["DeductionDataTable"] == null)
                  {
 

                        string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                        SqlConnection  myConnection = new SqlConnection(ConnectionString);

                        SqlCommand myCommand = new SqlCommand("sp_GetDeduction", myConnection);
                        myCommand.CommandType = CommandType.StoredProcedure;
                        
                        SqlDataAdapter  objDataAdapter = new SqlDataAdapter(myCommand);
                  
                        DataSet objDataSet = new DataSet();
   

                        try
                        {
                              objDataAdapter.Fill(objDataSet, "DeductionDataTable");
                        //      Cache.Insert("DeductionDataTable", objDataSet.Tables["DeductionDataTable"]);
                              Cache.Insert("RegionDataTable", objDataSet.Tables["DeductionDataTable"], null, DateTime.Now.AddHours(1), TimeSpan.Zero);
            
                        }
   
                        catch (SqlException SqlEx)
                        {
                              lblStatus.Text = SqlEx.Message.ToString();
                        }
   
                        catch (Exception Ex)
                        {
                              lblStatus.Text = Ex.Message.ToString();
                        }
   
                        finally
                        {
                              myConnection.Close();
                        }
                  }
   
                  return (DataTable)Cache["DeductionDataTable"];
            }
            #endregion

            /* end of Chi's Enhancements*/
      
            

       public SqlDataReader getDestinationElements(string employerId)
            {
                  DBUtil db = new DBUtil(ConfigurationSettings.AppSettings["connectLocal"]);
                  SqlParameter[] procParams = new SqlParameter[0];
                  SqlDataReader dataRead = null;
                                    
                  try
                  {
                        SqlParameter[] procPram = new SqlParameter[1];
                        procParams[0] = new SqlParameter("@employerId",employerId);
                        dataRead = db.getData("sp_DestinationElements",procParams);
                  }
                  catch{}
              return dataRead;

      }
            
            public SqlDataReader getPolicyAtlasId(string employerId)
            {
                  DBUtil db = new DBUtil(ConfigurationSettings.AppSettings["connectLocal"]);
                  SqlParameter[] procParams = new SqlParameter[1];
                  SqlDataReader dataReader = null;
                  try
                  {
                        procParams[0] = new SqlParameter("@employerId",employerId);
                        dataReader = db.getData("sp_getPolicyAtlasId",procParams);                        
            
                  }
                  catch (Exception ex) { Response.Write(ex.Message);}
                  return dataReader;

            }


      

            public int getRemunerationItems(SqlDataReader dataReader,string employerId) {
                  DBUtil db = new DBUtil(ConfigurationSettings.AppSettings["connectTAXpointTest"]);
                  DestinationElementDB deDb = new DestinationElementDB(ConfigurationSettings.AppSettings["connectLocal"]);
                  OracleDataReader rDRdr = null;
                        int count = 0;
                  string deduction = "";
                        
                  try
                  {      
                        if (dataReader != null && dataReader.HasRows)
                        {
                              while (dataReader.Read())
                              {
                                    if (dataReader["AtlasId"] != null && !dataReader.IsDBNull(dataReader.GetOrdinal("AtlasId")) && dataReader["AtlasId"].ToString().Trim() != "")  
                                    {
                                          
                                          rDRdr = db.getOracleRemunerationData(dataReader["AtlasId"].ToString().Trim());
                                          DestinationElement element;
                                                                               
                                          if (rDRdr != null && rDRdr.HasRows)
                                          {
                                                while (rDRdr.Read() )
                                                {
                                                      string test = rDRdr["REM_DESCRIPTION"].ToString().Trim();
                                                      
                                                      if (rDRdr["REM_DESCRIPTION"] != null && !rDRdr.IsDBNull(rDRdr.GetOrdinal("REM_DEScRIPTION")) && rDRdr["REM_DESCRIPTION"].ToString().Trim() != "")
                                                      {
                                                            if (rDRdr["Deductions"] != null && !rDRdr.IsDBNull(rDRdr.GetOrdinal("Deductions")))
                                                                  deduction = rDRdr["Deductions"].ToString().Trim();
                                                            element = new DestinationElement(employerId,rDRdr["REM_DESCRIPTION"].ToString().Trim());
                                                            SqlParameter[] procParams = new SqlParameter[3];
                                                            procParams[0] = new SqlParameter("@employerId",element.Employer);
                                                            procParams[1] = new SqlParameter("@elementName", element.Name);
                                                            procParams[2] = new SqlParameter("@deduction",deduction);
                                                            deDb = new DestinationElementDB(ConfigurationSettings.AppSettings["connectLocal"]);
                                                            if (deDb.insertElement("sp_insertDestinationElement",procParams) > 0)
                                                                  count++ ;
                                                      }
                                                }                              
                                    
                                          }
                                    }
                              }            
                        }                                          
                                                                                                 
                  }
                  catch(Exception ex) { Response.Write(ex.Message);}
                  finally
                  {
                        rDRdr = null;
                        deDb = null;
                        db = null;
                  }
                  return count;
                        
            }
      

            public int getMappings(string employerId)
            {
                  int count = 0;
                  DBUtil db = new DBUtil(ConfigurationSettings.AppSettings["connectLocal"]);
                  SqlDataReader dataReader = null;
                  SqlParameter[] procParams = new SqlParameter[1];
                  procParams[0] = new SqlParameter("@employerId",employerId);
                  
                  Mapping map;
                  MappingDB mapDb = new MappingDB(ConfigurationSettings.AppSettings["connectLocal"]);

                  try
                  {
                        dataReader = db.getData("sp_getPriorYearMapping",procParams);

                        if (dataReader != null && dataReader.HasRows)
                        {
                              while (dataReader.Read())
                              {
                                    map = new Mapping(dataReader["EmployerId"].ToString().Trim(),dataReader["CompElement"].ToString().Trim(),dataReader["Source"].ToString().Trim(),dataReader["DestinationElement"].ToString().Trim());
                                    count += mapDb.insertMapping(map);

                              }
                        }
                  }
                  catch(Exception ex) { Response.Write(ex.Message + " Possibly no 03/04 data"); }
                  return count;

            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
                  this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion

      }
}
0
 
existenz2Commented:
myConnection.Open();
                    dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    dgCompensation.DataBind();

should be:

myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
dgCompensation.DataBind();
myConnection.Close();

That's one problem.

I can't see any other problem causing the datagrid to disappear. Is the sp_DestinationElements stored procedure return any rows when you use an empty string as parameter? That might cause your problem.
0
 
SirReadAlotAuthor Commented:
sp looks like this

CREATE PROCEDURE sp_DestinationElements @employerId varchar(50) AS
Select distinct
employerId,
DestinationElement,  
Deduction
from DestinationElements
where employerId  = @employerId


GO
0
 
existenz2Commented:
Select distinct
employerId,
DestinationElement,  
Deduction
from DestinationElements
where employerId  = ""

will return nothing to your datagrid aka no rows, which means it most likely will show only the header.
0
 
SirReadAlotAuthor Commented:
will try this
0
 
existenz2Commented:
Oh and my previous comment about the connection. I was totally wrong:

myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
dgCompensation.DataBind();

is the correct syntax. I didnt notice the CommandBehavior.CloseConnection, which closes the connection.
0
 
SirReadAlotAuthor Commented:
but i need that @ parameter

thanks
0
 
existenz2Commented:
What is the idea of the stored procedure and using "" as a parameter value?
0
 
SirReadAlotAuthor Commented:
because but the datagrid is filled, a user may select  any employer.

As I do not know which one will be choose, only the data relating to that ID WILL
be load in the grid,

From then one we will edit, update etc

0
 
SirReadAlotAuthor Commented:
this code

      private void BindGrid(string Id)
            {

                  string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  SqlParameter param = new SqlParameter( "@employerId", SqlDbType.VarChar, 50);
                  
                  param.Value = Id;
                  myCommand.Parameters.Add(param);

                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                        myConnection.Close();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }

      
0
 
existenz2Commented:
As said in a earlier post:

myConnection.Close(); <<< Remove that. I was mistaken, didn't see the CommandBehavior.CloseConnection.

In your case using a empty string as the parameter will return nothing and binding nothing to the datagrid will show nothing. If you want to show everything you will need to call a different stored procedure.

0
 
SirReadAlotAuthor Commented:
I have done this

bit

myConnection.Close(); <<< Remove that. I was mistaken, didn't see the CommandBehavior.CloseConnection.
0
 
SirReadAlotAuthor Commented:
In your case using a empty string as the parameter will return nothing and binding nothing to the datagrid will show nothing. If you want to show everything you will need to call a different stored procedure.

do u mean this

and then calling it in page_load?
private void BindGrid()
            {

                string ConnectionString = ConfigurationSettings.AppSettings["ConnPubs"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_stores_sel", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  try
                  {
                        myConnection.Open();
                        DG_CheckBox.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        DG_CheckBox.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.