Solved

Displaying specific row in a datagrid control

Posted on 2010-11-17
4
700 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now