Solved

over load or not to overload

Posted on 2006-07-05
19
200 Views
Last Modified: 2010-04-16
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("");
            }
0
Comment
Question by:SirReadAlot
  • 10
  • 9
19 Comments
 
LVL 14

Expert Comment

by:existenz2
ID: 17041364
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
 

Author Comment

by:SirReadAlot
ID: 17041369
will try this

and let you know
0
 

Author Comment

by:SirReadAlot
ID: 17041378
the datagrid still disappears
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
LVL 14

Expert Comment

by:existenz2
ID: 17041380
Actually, the solution I posted is a combination of both TheLearnedOne's options in your first question.
0
 
LVL 14

Expert Comment

by:existenz2
ID: 17041383
Then your problem has nothing to do with overloading it seems. Could you paste the code for the whole page?
0
 

Author Comment

by:SirReadAlot
ID: 17041390
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
 
LVL 14

Expert Comment

by:existenz2
ID: 17041426
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
 

Author Comment

by:SirReadAlot
ID: 17041443
sp looks like this

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


GO
0
 
LVL 14

Expert Comment

by:existenz2
ID: 17041448
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
 

Author Comment

by:SirReadAlot
ID: 17041454
will try this
0
 
LVL 14

Expert Comment

by:existenz2
ID: 17041457
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
 

Author Comment

by:SirReadAlot
ID: 17041471
but i need that @ parameter

thanks
0
 
LVL 14

Expert Comment

by:existenz2
ID: 17041476
What is the idea of the stored procedure and using "" as a parameter value?
0
 

Author Comment

by:SirReadAlot
ID: 17041493
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
 

Author Comment

by:SirReadAlot
ID: 17041495
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
 
LVL 14

Expert Comment

by:existenz2
ID: 17041513
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
 

Author Comment

by:SirReadAlot
ID: 17041515
I have done this

bit

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

Author Comment

by:SirReadAlot
ID: 17041521
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
 
LVL 14

Accepted Solution

by:
existenz2 earned 500 total points
ID: 17041539
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question