Solved

Displaying specific row in a datagrid control

Posted on 2010-11-17
4
702 Views
Last Modified: 2012-05-10
I am writing a system in which a user must select a project from a list (Datagrid) before doing anything else.  To make the system easire for them I am storing their currently selected project in a database table, and want that prjoect to be the default project when they next log in.

I have all of the code working to set and retrieve the selected project, but want the datagrid to be set to the right page and have the default project row selected.

I have tried looping through the DataKeys collection of the datagrid in the DataBound event to find the row that has the matching data key, but it seems that the Datakeys collection only contains the rows for the currently displayed page of data.  I have to manually jump to the relevant page before teh default project row is selected.  At this point, the default ProjectID has been stored in a Session variable.  I do not want to filter the grid to show only the previously selected project, but need to show all projects so they can select a different one if they want.

How do I get the grid to automatically jump to the correct page and select the correct row?

protected void grdProject_DataBound(object sender, EventArgs e)
    {
  

if (Convert.ToInt32(Session["ProjectID"]) > 0)
        {
            //this.grdProject.SelectedIndex = this.grdProject.DataKeys
            int iloop = 0;
            foreach (DataKey dkProject in this.grdProject.DataKeys)
            {
                if (Convert.ToInt32(dkProject.Value) == Convert.ToInt32(Session["ProjectID"]))
                {
                    grdProject.SelectedIndex = iloop;
                    break;
                }
                iloop++;
            }
        }
}

Open in new window

0
Comment
Question by:cquinn
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
azarc3 earned 250 total points
ID: 34154765
Unfortunately, you've hit the nail on the head. Both the DataGrid and GridView controls are aware of only the data that is currently "visible" page. You're going to have to calculate which page to show and then set your SelectedIndex. The good news is that this is really easy to do.

1. You'll need to figure out where your selected project is in that set of data; ideally, this will happen before you actually bind the data to grdProject. In psuedocode, this would be...
var desiredIndex = 0;
foreach(row in {your_datasource})
{
    if(row.ProjectID == Session["ProjectID"]) break;
    desiredIndex++;
}


2. Figure out which page the desired row is on...
var desiredPage = (int)(desiredIndex / grdProject.PageSize);


3. Do your normal DataBind(), the set the desired page.
grdProject.Datasource = {your_datasource};
grdProject.DataBind();
grdProject.CurrentPageIndex = desiredPage;

4. Set your selected row using the code you already have.

That should do it.
0
 
LVL 7

Expert Comment

by:mr_nadger
ID: 34157136
there are adaptations of gridview controls which use letters for pagination rather than page numbers.
(see http://www.dotnetcurry.com/ShowArticle.aspx?ID=339)

Perhaps using this approach, with the default program's name picking the default letter's page would be more intuitive to users.
0
 
LVL 15

Author Comment

by:cquinn
ID: 34162573
I have done it the following way, which I think is more efficient that looping through the whole data set to find the required item's position:

1.  The grid is bound to a SQL stored procedure that returns a row number using the Stored procedure spGetUserProjects:

2. I then use a second stored procedure,(spGetUserLastProjectItem),  additionally passing in the Project ID - this uses a Common Table Expression to return the particular row, including the row number, of the specific project.  Only one row will be returned, so the forEach loop is probably superfluous.

3. I then call the procedure FindProjectInGrid to move to the specified page and row in the grid.  There should only ever be one row returned, so the foreach loop is superfluous.

//1.  Grid View datasource Stored proc:
ALTER Procedure [dbo].[spGetUserProjects]
	 -- Input parameters
	@UserID smallint,
	@Type int = 0
As
Begin

	SELECT     
		Code
		,Description
		,[StartDate]
		,[EndDate]
		,ProjectID
		,ProjType
		,ROW_NUMBER() OVER (ORDER BY Code,Description) as RowNumber 
		
	FROM         
	vwUserProjects

	WHERE     
		(UserId = @userId)
	AND	 
		ProjType
	=  
	 
	(CASE @Type
			WHEN 0 Then 1 -- end date is greater than today - current projects 
			WHEN 1 Then -1  -- end date is on or before today - historical
			ELSE	--Select them all
				ProjType 
		end
	)

	Order By Code
		,Description

End

//2.  Stored proc to retrieve the specific row for the project
ALTER Procedure [dbo].[spGetUserLastProjectItem]
	 -- Input parameters
	@UserID smallint,
	@ProjectID int,
	@Type int = 0
As
Begin

;WITH ProjList
AS
(	SELECT     
		Code
		,Description
		,[StartDate]
		,[EndDate]
		,ProjectID
		,ProjType
		,ROW_NUMBER() OVER (ORDER BY Code,Description) as RowNumber 
		
	FROM         
	vwUserProjects

	WHERE     
		(UserId = @userId)
	AND	 
		ProjType
	=  
	 
	(CASE @Type
			WHEN 0 Then 1 -- end date is greater than today - current projects 
			WHEN 1 Then -1  -- end date is on or before today - historical
			ELSE	--select them all
				ProjType 
		end
	)
    ) 
Select * from Projlist where ProjectID = @ProjectID
End

//3.  Routine to find the specific page and row in the grid

 protected void FindProjectInGrid()
    {
        try
        {
            int iPagePos = -1;
            int iPage = 0;
            string connectString = System.Configuration.ConfigurationManager.AppSettings.Get("AbacusConnection");
            SqlConnection con = new SqlConnection(connectString);
            con.Open();
            SqlDataAdapter daProj = new SqlDataAdapter("[spGetUserLastProjectItem]", con);
            daProj.SelectCommand.CommandType = CommandType.StoredProcedure;
            daProj.SelectCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt16(Session["UserID"]);
            daProj.SelectCommand.Parameters.Add("@ProjectID", SqlDbType.Int).Value = Convert.ToInt16(Session["ProjectID"]);
            daProj.SelectCommand.Parameters.Add("@Type", SqlDbType.Int).Value = this.rblProjectType.SelectedIndex;


            DataTable dtProj = new DataTable();
            daProj.Fill(dtProj);
            foreach (DataRow row in dtProj.Rows)    //should only be one row
            {
                iPagePos = (Convert.ToInt32(row["RowNumber"]) % grdProject.PageSize) - 1;
                iPage = (Convert.ToInt32(row["RowNumber"]) / grdProject.PageSize);
            }

            grdProject.PageIndex = iPage;
            grdProject.SelectedIndex = iPagePos;
            grdProject.DataBind();
            dtProj.Dispose();
            daProj.Dispose();
            con.Close();
            con.Dispose();
        }
        catch (Exception ex)
        {
            errorList.Clear();
            errorList.Add(System.Environment.NewLine + ex.Message);
            validationErrors = (string[])errorList.ToArray(typeof(string));
            ErrorList1.DisplayErrors("Please correct the following:", validationErrors);
            // this.pnlMain.Enabled = false;
            this.pnlErrors.Visible = true;
        }
    }

Open in new window

0
 
LVL 15

Author Closing Comment

by:cquinn
ID: 34162578
You gave me the bones of the solution, so thanks for that, but I hope I have come up with a slightly more elegant solution
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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