How can I show records in gridview based in order from most recent Dates?

Donald Symmons 3,066 Reputation points
2023-12-10T16:57:44.45+00:00

I tried to display my record in gridview to show from most recent dates but I keep seeing records of July, 2023 at the top of the gridview, then November, 2023 is below.

I want to show the most recent dates at the top then earlier at the end of the gridview

I am using Stored procedure to fetch the data from table

CREATE PROCEDURE [dbo].[MyDataViewTable]
	     @CreatedBy VARCHAR(50)
	,@PageIndex INT
	,@PageSize INT
	,@RecordCount INT OUT

AS
	BEGIN
    SELECT ROW_NUMBER() OVER(ORDER BY CreatedBy) RowNumber
	        ,Id
			,Receipt_no 
			,email
			,CreatedBy
			,NameUser
			,CreatedDate
    INTO #Temp 
    FROM TemplateTable
	WHERE CreatedBy = @CreatedBy ORDER BY CreatedDate DESC

	SELECT @RecordCount = COUNT(*) FROM #Temp 

	SELECT * FROM #Temp 
    WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1 
   
    DROP TABLE #Temp 

	END

I thought that by using this line will solve the issue

WHERE CreatedBy = @CreatedBy ORDER BY CreatedDate DESC

But it did not work.

Here is my C# for displaying in GridView as well.

private void GetRecordsPage(int pageIndex)
        {
            try
            {
                using (SqlConnection con = new SqlConnection())
                {
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlCommand cmd = new SqlCommand("MyDataViewTable", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
                        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                        cmd.Parameters.AddWithValue("@PageSize", PageSize);
                        cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                        cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                        con.Open();
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                using (SqlDataReader dr = cmd.ExecuteReader())
                                if (dr.HasRows)
                                    {
                                        Template.Visible = true;
                                        GridView1.DataSource = dt;
                                        GridView1.DataBind();
                                    }
                                    else
                                    {
                                        Template.Visible = false;
                                    }
                                int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                                this.PopulatePager(recordCount, pageIndex);
                            }
                        }
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

Please how do I solve this?

Developer technologies | .NET | Other
Developer technologies | ASP.NET | Other
SQL Server | Other
{count} votes

Accepted answer
  1. Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator
    2023-12-10T18:14:31.6566667+00:00

    The direct reason that your sorting is mixed up is that you don't have an ORDER BY in the SELECT that returns data to the client. You have an ORDER BY when you create the temp table, but that's not where you need it. You need one when you select from the temp table.

    So add a

    ORDER BY Id 
    

    to the SELECT from the temp table, and add a DESC in the OVER clause to the row number function.

    You may also be interesting in exploring the OFFSET FETCH options to ORDER BY:

    SELECT *
    FROM   sys.objects
    ORDER  BY name
    OFFSET 100 ROWS FETCH NEXT 20 ROWS ONLY
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Donald Symmons 3,066 Reputation points
    2023-12-10T18:34:34.1966667+00:00

    This works.

    CREATE PROCEDURE [dbo].[MyDataViewTable]
    	@CreatedBy VARCHAR(50)
    	,@PageIndex INT
    	,@PageSize INT
    	,@RecordCount INT OUT
    
    AS
    BEGIN
        SELECT ROW_NUMBER() OVER(ORDER BY CreatedDate DESC) RowNumber
    	        ,Id
    			,Receipt_no 
    			,email
    			,CreatedBy
    			,NameUser
    			,CreatedDate
        INTO #Temp 
        FROM TemplateTable
    	WHERE CreatedBy = @CreatedBy
    
    	SELECT @RecordCount = COUNT(*) FROM #Temp 
    
    	SELECT * FROM #Temp 
        WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1 
       
        DROP TABLE #Temp 
    END
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.