locked
List view question to randomize in vb.net RRS feed

  • Question

  • User-1578974752 posted

    Below code is the List view .Appreciate the Help

    Randomization is working properly with this code . But As each page have 5 questions, when I click page 2 another 5 questions must show. But now some questions are repeating in page 1 ,2 and 3.Randomization happening on each page. How can I adjust it to 15 questions so that first page questions will not repeat in page 2 or 3

    Protected Sub ListView1_PagePropertiesChanged(sender As Object, e As EventArgs)

    Button1.Visible = True

      End Sub

    Tuesday, October 23, 2018 3:23 AM

Answers

  • User409696431 posted

    With an SQLDataSource and the DataPager control, each time a new page of data in displayed, all of the data is re-queried from the database. This is by design.  Once all of the data has been returned, the ListView selectively displays part of the entire set of data, based on the StartRowIndex and MaximumRows values.  In your case, since you are pseudo-randomizing the data in the query, that means a different order of data is returned each time, and you have no way to control if the same result appears more than once, on different pages.   Any custom paging that also uses the DataPager also involves re-querying the database each page, yielding the same problem in your case.

    You need to change your approach.  One possibility: Put three ListViews on the page, and hide/show them as appropriate.  Query the database once and fill a DataTable.  Split the DataTable into three, and bind each one to the appropriate ListView.  An example of binding three listviews to a split datatable is below (C#):

    public partial class ListView : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            BindListView();
        }
        private void BindListView()
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;
            using (System.Data.SqlClient.SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "SELECT TOP 15 [Question],[Name],[Path],[CorrectAnswer],[Option1], [Option2], [Option3], [Option4], [Option5], [QuestDetailID], [QuestionMasterID] FROM [QuestionDetail] WHERE ([QuestionMasterID] = @QuestionMasterID) ORDER BY NEWID()";
                    cmd.Connection = con;
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        sda.Fill(dt);  //dt contains all 15 rows
                        List<DataTable> theTables = SplitTable(dt, 5); //Split the data into tables with 5 rows
                        ListView1.DataSource = theTables[0];
                        ListView1.DataBind();
                        ListView2.DataSource = theTables[1];
                        ListView2.DataBind();
                        ListView3.DataSource = theTables[2];
                        ListView3.DataBind();
                    }
                }
            }
        }
        private static List<DataTable> SplitTable(DataTable originalTable, int batchSize)
        {
            List<DataTable> tables = new List<DataTable>();
            int i = 0;
            int j = 1;
            DataTable newDt = originalTable.Clone();
            newDt.TableName = "Table_" + j;
            newDt.Clear();
            foreach (DataRow row in originalTable.Rows)
            {
                DataRow newRow = newDt.NewRow();
                newRow.ItemArray = row.ItemArray;
                newDt.Rows.Add(newRow);
                i++;
                if (i == batchSize)
                {
                    tables.Add(newDt);
                    j++;
                    newDt = originalTable.Clone();
                    newDt.TableName = "Table_" + j;
                    newDt.Clear();
                    i = 0;
                }
            }
            return tables;
        }
    }

    Convert to VB.net and add error handling as appropriate.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 23, 2018 7:20 AM
  • User753101303 posted

    Hi,

    For now you take 15 random questions but you run the query again when going to the next page so you are drawing random questions again and again rather than once for all for this user. I would take 15 questions once for all, likely by inserting them into a table for this user. Then I would just work with list that has been established once for all.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 23, 2018 8:31 AM

All replies

  • User409696431 posted

    With an SQLDataSource and the DataPager control, each time a new page of data in displayed, all of the data is re-queried from the database. This is by design.  Once all of the data has been returned, the ListView selectively displays part of the entire set of data, based on the StartRowIndex and MaximumRows values.  In your case, since you are pseudo-randomizing the data in the query, that means a different order of data is returned each time, and you have no way to control if the same result appears more than once, on different pages.   Any custom paging that also uses the DataPager also involves re-querying the database each page, yielding the same problem in your case.

    You need to change your approach.  One possibility: Put three ListViews on the page, and hide/show them as appropriate.  Query the database once and fill a DataTable.  Split the DataTable into three, and bind each one to the appropriate ListView.  An example of binding three listviews to a split datatable is below (C#):

    public partial class ListView : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            BindListView();
        }
        private void BindListView()
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;
            using (System.Data.SqlClient.SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "SELECT TOP 15 [Question],[Name],[Path],[CorrectAnswer],[Option1], [Option2], [Option3], [Option4], [Option5], [QuestDetailID], [QuestionMasterID] FROM [QuestionDetail] WHERE ([QuestionMasterID] = @QuestionMasterID) ORDER BY NEWID()";
                    cmd.Connection = con;
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        sda.Fill(dt);  //dt contains all 15 rows
                        List<DataTable> theTables = SplitTable(dt, 5); //Split the data into tables with 5 rows
                        ListView1.DataSource = theTables[0];
                        ListView1.DataBind();
                        ListView2.DataSource = theTables[1];
                        ListView2.DataBind();
                        ListView3.DataSource = theTables[2];
                        ListView3.DataBind();
                    }
                }
            }
        }
        private static List<DataTable> SplitTable(DataTable originalTable, int batchSize)
        {
            List<DataTable> tables = new List<DataTable>();
            int i = 0;
            int j = 1;
            DataTable newDt = originalTable.Clone();
            newDt.TableName = "Table_" + j;
            newDt.Clear();
            foreach (DataRow row in originalTable.Rows)
            {
                DataRow newRow = newDt.NewRow();
                newRow.ItemArray = row.ItemArray;
                newDt.Rows.Add(newRow);
                i++;
                if (i == batchSize)
                {
                    tables.Add(newDt);
                    j++;
                    newDt = originalTable.Clone();
                    newDt.TableName = "Table_" + j;
                    newDt.Clear();
                    i = 0;
                }
            }
            return tables;
        }
    }

    Convert to VB.net and add error handling as appropriate.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 23, 2018 7:20 AM
  • User753101303 posted

    Hi,

    For now you take 15 random questions but you run the query again when going to the next page so you are drawing random questions again and again rather than once for all for this user. I would take 15 questions once for all, likely by inserting them into a table for this user. Then I would just work with list that has been established once for all.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 23, 2018 8:31 AM
  • User-1578974752 posted

    There are only 15 question in the table for each test.5 in each page

    Tuesday, October 23, 2018 9:25 AM
  • User409696431 posted

    Neither reply to your question assumed there were more than 15.

    Both replies told you your current method was querying the database repeatedly for each set of 5 from a randomized 15, and that meant there could be duplicates, so you should only query it once and work with one set of data.

    My reply included code for one approach to do that.

    Tuesday, October 23, 2018 9:33 AM
  • User-1578974752 posted

    Hi PatriceSc

    Solution you mentioned is fastest .Now it is working

    Wednesday, October 24, 2018 1:13 AM