Query returns results 5 times

Question

Hi, I have a weird problem. My query in C#/ASP.NET returns results 5 times. I tried brakepoint-ing but I can't find the error. I have 2 related tables. One table loads on PAGE_LOAD and when the user click on a cell, it shows the content from another table related to that cell. It's very simple.

    //PAGE LOAD
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbpath + "/secure_user/data/data.mdb");
        OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT Project,Manager,Customer,Deadline FROM projects WHERE Username='" + uname + "'", myConnection);
        DataTable table = new DataTable();
        adapter.Fill(table);
        adapter.Dispose();
        GridView1.DataSource = table;
        GridView1.DataBind();
    }
}

It loads projects table to the GridView. Now when I click a certain project, it displays more information about that project:

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
    GridViewRow row = GridView1.SelectedRow;
    Label1.Text = row.Cells[1].Text;

    OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbpath + "/secure_user/data/data.mdb");
    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT tasks.Task,tasks.Priority,tasks.Done,taska.Hours FROM projects,tasks WHERE tasks.Username='" + uname + "' AND tasks.Project='" + Label1.Text + "'", myConnection);
    DataTable table = new DataTable();
    adapter.Fill(table);
    adapter.Dispose();
    GridView2.DataSource = table;
    GridView2.DataBind();
    GridView2.Visible = true;
}

It displays with no error, but it does 5 times no matter what project I select from GridView1, it always displays GridView2 (second table) content 5 times in a row. What could be the problem?

Answer 1

Looks like you have something wrong in your query. Try using INNER JOIN instead of ,.

Instead of this:

SELECT tasks.Task, tasks.Priority, tasks.Done, tasks.Hours
FROM projects, tasks
WHERE tasks.Username='" + uname + "' AND tasks.Project='" + Label1.Text + "'

Try this:

SELECT tasks.Task, tasks.Priority, tasks.Done, tasks.Hours
FROM projects INNER JOIN tasks ON projects.ID = tasks.ProjectID --> may not be correct depends on your table structure
WHERE tasks.Username='" + uname + "' AND tasks.Project='" + Label1.Text + "'

Another thing: building a SQL query like that is prone to SQL Injection attack.

Answer 2

Could be that this query is returning multiple records; can you list primary keys in the used tables

Answer 3

In your second query you have a join, of sorts. You never return anything in the SELECT from the projects table, but it is referenced in the query. I'm guessing that you have 5 projects.

Also, you are injecting data into the query. This is bad as it becomes very easy for an attacker to mount a SQL Injection attack against your code and database, especially as you are using data directly from controls. You should consider using at least parameterised queries.

Answer 4

You try adding to your query DISTINCT clause. Because if it is not presented, your query did a cartesian product.

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT DISTINCT tasks.Task,tasks.Priority,tasks.Done,taska.Hours FROM projects,tasks WHERE tasks.Username='" + uname + "' AND tasks.Project='" + Label1.Text + "'", myConnection);

Answer 5

You are doing cross join between the projects table and the tasks table, so you are joining every project with each task for the project that you selected. As you have five projects you get each task five times.

Use a join to specify the relation between the projects table and the tasks table:

OleDbDataAdapter adapter = new OleDbDataAdapter(
   "SELECT tasks.Task,tasks.Priority,tasks.Done,taska.Hours "+
   "FROM projects "+
   "INNER JOIN tasks ON tasks.Project = projects.Project "+
   "WHERE projects.Username='" + uname + "' AND projects.Project='" + Label1.Text + "'", myConnection);

Note:
Notice that I used the Username field in the projects table rather than the tasks table. Either you have redundancy in the tables, or the fields mean different things. If some other user can add tasks to your project you would need a condition for the tasks.Username field also if you only want to see the tasks that you added yourself.