locked
Gridview select different rows for different page open RRS feed

  • Question

  • User-1123701243 posted

    Hello

    I have a Gridview that shows records from a database table containing apprx. 300,000 records.

    This page is opened by many times from different viewers. 

    I found that the gridview shows the same records with the same sequence for all users. 

    I want to show different records for all users. I don't want to show the same records for all users.

    That is all . Thank you.

    Sunday, March 3, 2019 5:30 AM

All replies

  • User-2054057000 posted

    You can use the ROW_NUMBER() method of SQL to create different orders based on different columns

    Select * From (Select ROW_NUMBER() Over (Id) AS 'RowNum',* From TableName)t
    Select * From (Select ROW_NUMBER() Over (Id Desc) AS 'RowNum',* From TableName)t
    Select * From (Select ROW_NUMBER() Over (Names) AS 'RowNum',* From TableName)t
    Select * From (Select ROW_NUMBER() Over (Names Desc) AS 'RowNum',* From TableName)t

    So I just show 4 different queries. You can simply use a stored procedure that accepts 2 values 'column name' and 'order' and then you generate different ordered queries.

    Sunday, March 3, 2019 6:13 AM
  • User-1123701243 posted

    Thanks yogiyogi

    Actually I want to show different records on every open, even on page refresh.

    The above select you sent is for MSSQL , while I am using MySQL.

    I appreciate your care.

    Sunday, March 3, 2019 6:22 AM
  • User-2054057000 posted

    There may be some little syntax difference since you are using mysql see MySQL ROW_NUMBER Function

    The logic will remain the same. You create a stored procedure that accepts 2 input parameters -  'column name' and 'order' and then you generate different ordered queries.

    See stored procedures tutorial.

    The stored procedure structure should be something like this:

    CREATE PROCEDURE [dbo].[sp_MyProc]
    @ColumnName Varchar(50),
    @Order Varchar(10)
    AS
    Set @Query = 'Select * From (Select ROW_NUMBER() Over ('+@ColumnName + ' ' + @Order +') AS 'RowNum',* From TableName)t'
    EXEC sp_executesql @query

    GO;

    Finally from your C# you will execute this stored prodcedure by passing different value for @ColumnName @order for different users.

    Sunday, March 3, 2019 2:44 PM
  • User-893317190 posted

    Hi human2x,

    If you are not going to show all records of your table, you could use a random number to let your record start with  random index using limit query.

    Below is my code. the first number after limit is the starting line number, the second is the  number of rows your want to show.

     if (!IsPostBack)
                {
                    int start = new Random().Next(6); //use a random, max value is 6
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter("select * from student limit @start,1", constr))
                    {
    
                        adapter.SelectCommand.Parameters.AddWithValue("start", start);
                        DataTable table = new DataTable();
                        adapter.Fill(table);
                        GridView1.DataSource = table;
                        GridView1.DataBind();
                    }
                }
       <asp:GridView ID="GridView1" runat="server"></asp:GridView>

    The result.

    Best regards,

    Ackerly Xu

    Monday, March 4, 2019 4:21 AM