Hi, does anyone know how to perform paging and sorting datagrid where the sql query involve more than one table? For example, when using equijoin. I am having no problem if there is one and only database table. Any suggestion?

Thanks.

Not sure what you mean. If you combine your results into one table through SQL, stored procedure and use a dataset, is not a problem. Maybe you can share your code and describe wht you mean.

jim

Sorry, I am not good enough in expressing what I think. Now, I will try to express using example.

Consider the following query:

SELECT * FROM myDBTable1;

The above query is quite straight forward where it involves only one database table. When I bind the data (all column from myTable1) to DataGrid, and I set AllowSorting="true" and AllowPaging="true". With few lines of statement, both sorting and paging is working when I click on the DataGrid.

Now, the problem which I face. Looking at the query below again:

SELECT myDBTable1.*
FROM myDBTable1, myDBTable2
WHERE myDBTable1.ID = myDBTable2.ID
and
myDBTable1.Age = 24;

So, it is very obvious that the query above is involving two tables with a 'JOIN'. Once the data is fecthed from database and bind it to DataGrid, I will have problem in paging and sorting. It means that whenever I click on the column name on DataGrid with an attempt to sort the page, I will get some sort of error such like "ambiguous column" something like that. It is the same also if I click on the page number, error "index must >= 0" or something else.

I am looking for many solution, but still can't solve the problem. If you have "Beginning ASP.NET 1.1 with Visual C#.NET 2003", please look at page 258-261. I am following the example from there, with one different, where the book mentions only how to sort and paging DataGrid with a query which involves a single DB.

For sure I will share my coding, please let me know if you really want to look at it, I don't want to post it now because I am talking too much already. :)

Thanks for reply.

Hi hichuah77
I am not one of the Junior or senior members of the Wrox forum nor an SQL guru but I thought I must comment on your Topic.
Your SQL Statement is not quite correct? In the SELECT statement you select all fields from myBDTable1 but nothing from myBDTable2 then in the WHERE statement you compare the ID fields from both tables but it doesn't have a select for myBDTable2.ID so the total SQL statement is invalid!??
Can this be the source of the problem?
Best regards,

Edward Stephen

The SQL is correct, however I suggest re-writing it using the new ANSI standard:

Select myDBTable1.*
From myDBTable1
INNER JOIN myDBTable2 ON myDBTable1.ID = myDBTable2.ID
Where
myDBTable1.age = 24

However I do not feel the SQL is the problem. Are you getting data back, if so, how many rows. I feel this may be a data issue.

Thanks for the reply.

Ya, i don't think sql query is the problem. In fact, I am still try to figure out why this happen. Maybe my coding skill is not that good yet. So, I will try some other way else to see what is going wrong.