asked on

Displaying specific row in a datagrid control

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

ASKER CERTIFIED SOLUTION

Kelvin McDaniel

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mr_nadger

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.

cquinn

ASKER

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

cquinn

ASKER

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