Query displaying duplicate Rows HELP!!

Question

User-672449838 posted

This query is displaying duplicate Rows in my parent gridView and I have tried many different things and cannot seem to get it to work. I need to Display only 1 row per CourseID

Below is a screenshot showing multiple rows per courseID. The way my table is laid out is that there are many textBooks per Course so when textBooks show up with that CourseID this query is creating a new row in my Parent table. If i expand upon the row with the "plus" image, all textBooks show up I just need to remove the duplicate Rows in my Course table and it is something with this query that is causing it.

https://gyazo.com/057b955d5aae1e1254c9f4a0edd5b77a

string query = "select * from Course join textBooks on textBooks.CourseID = Course.CourseID  ";

Answer 1

User-1952463932 posted

I think you may have your SQL statement logic backwards depending on what it is you are trying to display.  Are you trying to display Courses that have Textbooks, or the Textbooks for each Course.  I suspect your are trying to do the latter in which case your statement should be something more akin to:

select Course.CourseName, textBooks.* from textBooks inner join Course on Course.CourseID = textBooks.CourseID

Answer 2

User-672449838 posted

When I try that, Nothing displays when I test it. I understand what you are saying but for some reason it doesnt, load the gridview. and I receive an error for cID not found which is the auto increment PK field on my Parent Course table

   protected void Page_Load(object sender, EventArgs e)
        {
            if (!HttpContext.Current.User.Identity.IsAuthenticated)
            {
                
            }
            if (!Page.IsPostBack)
            {
                //i'm using a datatable for storing all the data 
                DataTable dTable = new DataTable();
                string query = "select Course.CourseTitle, textBooks.* from textBooks inner join Course on Course.CourseID = textBooks.CourseID ";

                //wrapping in 'using' means the connection is closed an disposed when done 
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["HUTDMSConnectionString"].ToString()))
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
                {
                    try
                    {
                        //fill the datatable with the contents from the database 
                        adapter.Fill(dTable);
                    }
                    catch
                    {
                    }
                }

                //save the datatable into a viewstate for later use 
                ViewState["allBooks"] = dTable;

                GridView1.DataSource = dTable;
                GridView1.DataBind();
            }


        }

Answer 3

User-1952463932 posted

The first thing to try is to use SQL Management Studio to try the query directly with the database and look at the results.  Confirm that you are getting the correct results.  Let me know what you get.

Answer 4

User283571144 posted

Hi Norque,

This query is displaying duplicate Rows in my parent gridView and I have tried many different things and cannot seem to get it to work. I need to Display only 1 row per CourseID

According to your description, I find the relationship between Course table and textBooks table is 1 to *.

It means one course may have multi textbooks.

So when you use join keyword in your SQL query, you will get multi duplicate rows, because each record has different textbook.

Could you please tell me why you use join key word in this query string?

In my opinion, I will firstly select the course table record and fill the selected table into gridview.

select * from Course 

When you click the "plus" image, in its click event, I will select textBooks table according the select row’s courseID and show the result.

Select * from textBooks where courseID = xxxxrow.id

Best Regards,

Brando