Getting second record where sql query is executed

I am trying to populate my list with task names from a database where priority is equal to an item in mine List<object>

.

The code below fills the list box, but the error is that in my database I have two records with priority 1 and so it only finds the first record and prints it twice. In an attempt to fix the previous error, which was that it displayed two records twice, I added break;

which now only shows the first record that satisfies the sql query.

I do it this way because the user has the ability to sort in priority order, so I get all the priority values ​​and store them in List<object>

, sort them through the bubble sort implementation, and then execute the code below to bring them back to the list box in that order in which the user wants.

So my question is, how can I display all records from my database correctly?

for (int i = 0; i < list.Count; i++)
{
    string sql = "SELECT [Task Name] FROM Tasks WHERE Priority = " + Convert.ToInt32(list[i].GetValue(0));
    using (OleDbCommand cmd = new OleDbCommand(sql, conn))
    {
        using (OleDbDataReader dataReader = cmd.ExecuteReader())
        {
            List<object[]> taskNameList = new List<object[]>();

            if (dataReader.HasRows) //if the table isnt empty
            {
                while (dataReader.Read()) //loop to the end of the database
                {
                    object[] tasks = new object[dataReader.FieldCount]; //object array of same length as the amount of task names in database
                    taskNameList.Add(tasks);
                    for (int j = 0; j <= dataReader.FieldCount - 1; j++)
                    {
                        tasks[j] = dataReader[j]; //fill object array with task names
                    }
                    taskList.Items.AddRange(tasks); //add to list box
                    break;
                }
            }
        }
    }
}

      

+3


source to share


2 answers


I solved this problem by putting an if statement inside a while loop to check if the list field already had the task name before I added it to the list. Below is the code:



  while (dataReader.Read()) //loop to the end of the database
  {
        if (taskList.Items.Contains(dataReader[0]) == false)  //so that it doesn't duplicate records in the list box that satisfy the priority value
        {
            object[] tasks = new object[dataReader.FieldCount]; //object array of same length as the amount of task names in database
            taskNameList.Add(tasks);
            for (int j = 0; j <= dataReader.FieldCount - 1; j++)
            {
                tasks[j] = dataReader[j]; //fill object array with task names
            }
            taskList.Items.AddRange(tasks); //add to list box

        }
  }

      

+1


source


If you want not to return the same name twice, you can add Distinct

to your query.

Also, since you are only returning one column, you should just simply add code to something like:



for (int i = 0; i < list.Count; i++)
{
    string sql = "SELECT Distinct [Task Name] FROM Tasks WHERE Priority = " + Convert.ToInt32(list[i].GetValue(0));
    using (OleDbCommand cmd = new OleDbCommand(sql, conn))
    using (OleDbDataReader dataReader = cmd.ExecuteReader())
    {
        while (dataReader.Read())  
            taskList.Items.Add((string) dataReader[0]); 
    }
}

      

0


source







All Articles