How to setup a datagrid like this one?

[DarkWorlds]

--If this post several times im unsure why. It kept erroring on me.

Well I am finishing a small part of a project, but ive never seen something written up this way. After researching its done really well, but this leave me clueless on how to sort it, mainly because im used to messing with datagrids on load.

		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			if(!IsPostBack)
			{
				hklCopyRight.Text += DateTime.Now.Year.ToString();
				pContentPic.Visible=true;
				objUser.Get_User();
			}

		}

This is the beginning of it really.

private void btnGetResult_Click(object sender, System.Web.UI.ImageClickEventArgs e)

LoadSearchResult();

So a button is hit, and this is called, it sets up the datagrid.

		public void LoadSearchResult()
		{
			string SQL= BuildQuery();
			SqlConnection myConn = new SqlConnection();
			myConn.ConnectionString = 
				System.Configuration.ConfigurationSettings.AppSettings.Get("connString");
		
			SqlCommand myCmd = new SqlCommand(SQL,myConn);
		
			myConn.Open();

			dgResult.DataSource = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
			dgResult.DataBind();
			lblRecords.Text = dgResult.Items.Count.ToString()+ " records found."; 
			
			myConn.Close();	
		}

Well I am finishing a small part of a project, but I’ve never seen something written up this way. After researching it’s done really well, but this leave me clueless on how to sort it, mainly because I’m used to messing with datagrid’s on load.

This is the beginning of it really.


Now the build query is called. No need to show you what it is, as I’m sure you can guess. Its a select statement that is built. So when this happens the table is ready to go.

Now I need to sort it, I am unsure how ill do this. Normally I’m used to the normal setup of a dataview, and having it load the grid on pageload event. I’m so confused on how to sort this, and how to display it. I mean it’s honestly that simple (well not really that simple, the building of the select statement is the most complex)

Please if ya got any tips, articles or anything let me know I will be looking forward to it.

 

[tperri]

Using a Reader, you need to do your sorting in your query. The only thing you can do with a reader is read, forward only.

HTH

 

[DarkWorlds]

Ok well I was going in the right direction to change the select then, so the problem i run into is when i hit the column to sort, the grid is gone. How can i fix this?

 

[jbenson001]

You have to rebind the grid after a sort or paging operation

 

[TipGiver]

You have to rebind the grid after a sort or paging operation"

> Yes. This, among others.. is a new "feature" in 2.0. You don;t have to write any code for paging, sorting etc

 

[jbenson001]

you have to code a rebind in the sort and page methods:
ex. datagrid1.databind()

 

[DarkWorlds]

Doesnt work. I hit search button and the DataGrid comes up. When I hit the column to sord by its gone. I have:

		private void dgResult_Sort(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
		{
			LoadSearchResult(e.SortExpression);
			dgResult.DataBind();
		}

and

		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			if(!IsPostBack)
			{
				hklCopyRight.Text += DateTime.Now.Year.ToString();
				pContentPic.Visible=true;
				objUser.Get_User();
				dgResult.DataBind();
			} 
		}

 

[jbenson001]

You'll have to trace through your code and make sure you are actually getting rows back from the SQL you are running.

 

[DarkWorlds]

Well this

lblRecords.Text = dgResult.Items.Count.ToString()+ " records found.";

as seen above in the code I posted says that all the records are found. Again im very lost :/

 

[DarkWorlds]

This is what I can give ya, not much is being left out. Only things not included are like print functions and other company stuff that does not mess with the results.

		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			if(!IsPostBack)
			{
				hklCopyRight.Text += DateTime.Now.Year.ToString();
				pContentPic.Visible=true;
				objUser.Get_User();
				dgResult.DataBind();
				
			}
			else
			{
				dgResult.DataBind();
			}
		}
		/// <summary>
		/// Bind data retrived from DB table 'AGENT'
		/// </summary>
		public void LoadSearchResult(string orderBy)
		{
			string SQL= BuildQuery(orderBy).ToString();
			SqlConnection myConn = new SqlConnection();
			myConn.ConnectionString = 
				System.Configuration.ConfigurationSettings.AppSettings.Get("connString");
		
			SqlCommand myCmd = new SqlCommand(SQL,myConn);
		
			myConn.Open();

			dgResult.DataSource = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
			dgResult.DataBind();
			lblRecords.Text = dgResult.Items.Count.ToString()+ " records found."; 
			
			myConn.Close();	
		}
        /// <summary>
        /// Build dynamic query statement based on input search criteria
        /// </summary>
        /// <returns></returns>
		public string BuildQuery(string orderBy)
		{
			string SqlStr = "Select * "
				+" From BD_AGENT ";
			
			StringBuilder query = new StringBuilder(SqlStr);
            
			bool IsFirst = true;
            
			if(txtName.Text != "")
			{
				query.Append("Where ");
				string[] strArr = txtAgentName.Text.Split(' ');
				for(int i=0; i<strArr.Length;i++)
				{
					if(i>0)
						query.Append(" and ");
					query.Append("[NAME] Like '%");
					query.Append(CheckText(strArr[i]));
					query.Append("%'");
				}
				IsFirst = false;
			}
           
			if(txtID.Text != "")
			{
				if(IsFirst)
				{
					query.Append("Where [ID] Like '%");
					query.Append(txtAgentNum.Text);
					query.Append("%'");
					
					IsFirst = false;
				}
				else
				{
					query.Append(" And [ID] Like '%");
					query.Append(txtAgentNum.Text);
					query.Append("%'");
				}
			}

			if(rblAgent.SelectedItem.Value == "Active")
				query.Append(" And [TERM_DATE] Is Null");
			else
				query.Append(" And [TERM_DATE] Is Not Null");

			query.Append(" order by " + orderBy.ToString());
            string strTemp=query.ToString();
			return strTemp;

		}


        /// <summary>
        /// Event procedure for 'Search' button
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
 
		private void btnGetResult_Click(object sender, System.Web.UI.ImageClickEventArgs e)
		{  
			if(txtName.Text=="" && txtNum.Text=="" &&
				txtPhone.Text=="")
			{
				lblMsg.Text = "Please specify your search criteria!";
				lblMsg.Visible = true;
				pContentPic.Visible=true;

				lblRecords.Visible = false;
				lblResult.Visible = false;
			}
			else
			{
				//BuildQuery();
				LoadSearchResult("[NAME]");
				lblRecords.Visible = true;
				lblResult.Visible = true;
				lblResult.Text = "Your Search Result";
				
				lblMsg.Visible = false;
			}
		}
       

		private void SortResult(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
		{
			LoadSearchResult(e.SortExpression);
			dgResult.DataBind();
		}

 

[DarkWorlds]

Ok, well this isn’t good, no responses. So I have either confused people, or ticked some people off. Because I don’t think im asking for the imposable or have shot over your expertise.

So let me try it again, basically this is called on a button event and works just fine. The sql is fine and everything is as it should be.

The problem comes in when I want to sort, nothing happens. No errors, no nothing. The original table is gone. LoadSearchResult is being called at the sort event so it should rebind. But the table goes good by, and the text of the sql (lblRecords.Text = SQL) doesn’t change on the page.

I broke up the code to 3 parts, which do all the work. Keep in mind when this is called from a button click event it works just fine, only the sorting doesn’t work. What am I missing?

		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			if(!IsPostBack)
			{
				hklCopyRight.Text += DateTime.Now.Year.ToString();
				pContentPic.Visible=true;
			}
		}


		public void LoadSearchResult(string orderBy)
		{
			string SQL= BuildQuery().ToString()+" ORDER BY "+orderBy;
			lblRecords.Text = SQL;
			SqlConnection myConn = new SqlConnection();
			myConn.ConnectionString = 
				System.Configuration.ConfigurationSettings.AppSettings.Get("connString");
		
			SqlCommand myCmd = new SqlCommand(SQL,myConn);
		
			myConn.Open();

			dgResult.DataSource = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
			dgResult.DataBind();
			//lblRecords.Text = dgResult.Items.Count.ToString()+ " records found."; 
			
			myConn.Close();	
		}		







		protected void dgResult_Sort(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
		{
			LoadSearchResult(e.SortExpression);
		}

 

[dragonwell]

Do you have ViewState enabled?

 

[DarkWorlds]

hmm, I would assume not, lol

 

[DarkWorlds]

Ive been working on this for some time now. I have not gained any headway after a week. Anyone got any tips?

 

[dragonwell]

Make sure ViewState is not disabled for the page or the DataGrid.

[blue]_______________________________________[/blue]