If this query returns no rows, it is probably due to the "select top 0 * from ... " clause.
It probably means that you are trying to retrieve a page which is beyond the the number of available pages, i.e:
totalRowCount - pageSize * pageIndex <= 0
or
that pageSize parameter is 0
Alon.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I tried you query in SQL Query Analyser and it showed no syntax errors, below is the same query with alignment and simplified claused. Is there a syntax error?
Please post the error you get when you execute the query (in Query Analyser, or SqlException text) and then I might see the problem.
select column_list
from
(
select top 0 * from
(
select top 6 column_list from tbl
where (where_clause) order by order_by_clause asc
) [r]
order by order_by_clause desc
) [f]
order by order_by_clause asc
Alon.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
from the inner portion:
" select top 12 [ContactId], [Contacts].[CompanyId], [Contacts].[FirstName], [Contacts].[LastName], [Contacts].[Address1] [Address], [Contacts].[City], [Contacts].[State], [Contacts].[PostCode], [Contacts].[Email], [Contacts].[BusinessPhone], [Contacts].[MobilePhone], [Contacts].[Fax], [Contacts].[Department], [Contacts].[Categories], [lsCustomers].[CustomerName] [CompanyName], [lsContactStatus].[Probability] as [Probability], [lsContactType].[ContactTypeLabel] [ContactType], [Contacts].[Probability] [sort_Probability]
from [Contacts] left join [lsCustomers] on ([Contacts].[CompanyId] = [lsCustomers].[CustomerId]) left join [lsContactStatus] on ([Contacts].[ContactStatus] = [lsContactStatus].[ContactStatusID]) inner join [lsContactType] on ([Contacts].[ContactType] = [lsContactType].[ContactTypeID]) where ([lsContactType].[ContactTypeLabel] = 'Prospect' and [Contacts].[LastName] like '%') order by [Contacts].[Probability] desc "
Produces : Invalid column name 'Probability'.
The full query is :
"select [f].[ContactId], [f].[CompanyId], [f].[FirstName], [f].[LastName], [f].[Address] [Address], [f].[City], [f].[State], [f].[PostCode], [f].[Email], [f].[BusinessPhone], [f].[MobilePhone], [f].[Fax], [f].[Department], [f].[Categories], [f].[CompanyName] [CompanyName], [f].[Probability] [Probability], [f].[ContactType] [ContactType] from ( select top 6 * from ( select top 12 [ContactId], [Contacts].[CompanyId], [Contacts].[FirstName], [Contacts].[LastName], [Contacts].[Address1] [Address], [Contacts].[City], [Contacts].[State], [Contacts].[PostCode], [Contacts].[Email], [Contacts].[BusinessPhone], [Contacts].[MobilePhone], [Contacts].[Fax], [Contacts].[Department], [Contacts].[Categories], [lsCustomers].[CustomerName] [CompanyName], [lsContactStatus].[Probability] [Probability], [lsContactType].[ContactTypeLabel] [ContactType], [Contacts].[Probability] [sort_Probability] from [Contacts] left join [lsCustomers] on ([Contacts].[CompanyId] = [lsCustomers].[CustomerId]) left join [lsContactStatus] on ([Contacts].[ContactStatus] = [lsContactStatus].[ContactStatusID]) inner join [lsContactType] on ([Contacts].[ContactType] = [lsContactType].[ContactTypeID]) where ([lsContactType].[ContactTypeLabel] = 'Prospect' and [Contacts].[LastName] like '%') order by [Contacts].[Probability] desc ) [r] order by [r].[sort_Probability] asc ) [f] order by [f].[sort_Probability] desc"
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
May be it is because one of the tables [lsContactStatus] or [Contacts] does not have a [Probability] column?
The inner query fetches [Probability] from both tables and gives them the same alias. My guess is that one of the tables does not have that column and therefore the error.
If its not the case, try splitting the query into smaller chunks until you pin point the error exactly.
Alon.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Is this feature working?
The following is a quick test:
select [ContactId], [Contacts].[CompanyId], [Contacts].[FirstName], [Contacts].[LastName], [Contacts].[Address1] [Address], [Contacts].[City], [Contacts].[State], [Contacts].[PostCode], [Contacts].[Email], [Contacts].[BusinessPhone], [Contacts].[MobilePhone], [Contacts].[Fax], [Contacts].[Department], [lsCustomers].[CustomerName] [CompanyName], [lsContactStatus].[Probability] [Probability], [lsContactType].[ContactTypeLabel] [ContactType] from ( select top 0 * from ( select top 6 [ContactId], [Contacts].[CompanyId], [Contacts].[FirstName], [Contacts].[LastName], [Contacts].[Address1] [Address], [Contacts].[City], [Contacts].[State], [Contacts].[PostCode], [Contacts].[Email], [Contacts].[BusinessPhone], [Contacts].[MobilePhone], [Contacts].[Fax], [Contacts].[Department], [lsCustomers].[CustomerName] [CompanyName], [lsContactStatus].[Probability] [Probability], [lsContactType].[ContactTypeLabel] [ContactType], [Contacts].[LastName] [sort_LastName] from [Contacts] left join [lsCustomers] on ([Contacts].[CompanyId] = [lsCustomers].[CustomerId]) left join [lsContactStatus] on ([Contacts].[ContactStatus] = [lsContactStatus].[ContactStatusID]) left join [lsContactType] on ([Contacts].[ContactType] = [lsContactType].[ContactTypeID]) where ([Contacts].[LastName] like ' %') order by [Contacts].[LastName] asc ) [r] order by [r].[sort_LastName] desc ) [f] order by [f].[sort_LastName] asc
Note a) the query by itself works fine..
b) the pageing does not seem to replace the table names with the [r] and [f] in the subqueries?
Am I missing how to use this feature?
Thanks...
If this query returns no rows, it is probably due to the "select top 0 * from ... " clause.
It probably means that you are trying to retrieve a page which is beyond the the number of available pages, i.e:
totalRowCount - pageSize * pageIndex <= 0
or
that pageSize parameter is 0
Alon.
No... its a syntax problem, the 0 is not an issue..
I tried you query in SQL Query Analyser and it showed no syntax errors, below is the same query with alignment and simplified claused. Is there a syntax error?
Please post the error you get when you execute the query (in Query Analyser, or SqlException text) and then I might see the problem.
select column_list
from
(
select top 0 * from
(
select top 6 column_list from tbl
where (where_clause) order by order_by_clause asc
) [r]
order by order_by_clause desc
) [f]
order by order_by_clause asc
Alon.
The column prefix 'Contacts' does not match with a table name or alias name used in the query.
As an example
[Contacts].[CompanyId],
will not compile as there is no table [contacts] i.e should be [r].[CompanyId], etc...
I.e will only work if there is no table qualifiers on parms..
Also not sure where sort_LastName came from...
You are right, this was a bug in earlier versions of Sql.Net.
Try downloading the latest version where this bug should be fixed.
Alon.
Still has the following problem:
from the inner portion:
" select top 12 [ContactId], [Contacts].[CompanyId], [Contacts].[FirstName], [Contacts].[LastName], [Contacts].[Address1] [Address], [Contacts].[City], [Contacts].[State], [Contacts].[PostCode], [Contacts].[Email], [Contacts].[BusinessPhone], [Contacts].[MobilePhone], [Contacts].[Fax], [Contacts].[Department], [Contacts].[Categories], [lsCustomers].[CustomerName] [CompanyName], [lsContactStatus].[Probability] as [Probability], [lsContactType].[ContactTypeLabel] [ContactType], [Contacts].[Probability] [sort_Probability]
from [Contacts] left join [lsCustomers] on ([Contacts].[CompanyId] = [lsCustomers].[CustomerId]) left join [lsContactStatus] on ([Contacts].[ContactStatus] = [lsContactStatus].[ContactStatusID]) inner join [lsContactType] on ([Contacts].[ContactType] = [lsContactType].[ContactTypeID]) where ([lsContactType].[ContactTypeLabel] = 'Prospect' and [Contacts].[LastName] like '%') order by [Contacts].[Probability] desc "
Produces : Invalid column name 'Probability'.
The full query is :
"select [f].[ContactId], [f].[CompanyId], [f].[FirstName], [f].[LastName], [f].[Address] [Address], [f].[City], [f].[State], [f].[PostCode], [f].[Email], [f].[BusinessPhone], [f].[MobilePhone], [f].[Fax], [f].[Department], [f].[Categories], [f].[CompanyName] [CompanyName], [f].[Probability] [Probability], [f].[ContactType] [ContactType] from ( select top 6 * from ( select top 12 [ContactId], [Contacts].[CompanyId], [Contacts].[FirstName], [Contacts].[LastName], [Contacts].[Address1] [Address], [Contacts].[City], [Contacts].[State], [Contacts].[PostCode], [Contacts].[Email], [Contacts].[BusinessPhone], [Contacts].[MobilePhone], [Contacts].[Fax], [Contacts].[Department], [Contacts].[Categories], [lsCustomers].[CustomerName] [CompanyName], [lsContactStatus].[Probability] [Probability], [lsContactType].[ContactTypeLabel] [ContactType], [Contacts].[Probability] [sort_Probability] from [Contacts] left join [lsCustomers] on ([Contacts].[CompanyId] = [lsCustomers].[CustomerId]) left join [lsContactStatus] on ([Contacts].[ContactStatus] = [lsContactStatus].[ContactStatusID]) inner join [lsContactType] on ([Contacts].[ContactType] = [lsContactType].[ContactTypeID]) where ([lsContactType].[ContactTypeLabel] = 'Prospect' and [Contacts].[LastName] like '%') order by [Contacts].[Probability] desc ) [r] order by [r].[sort_Probability] asc ) [f] order by [f].[sort_Probability] desc"
May be it is because one of the tables [lsContactStatus] or [Contacts] does not have a [Probability] column?
The inner query fetches [Probability] from both tables and gives them the same alias. My guess is that one of the tables does not have that column and therefore the error.
If its not the case, try splitting the query into smaller chunks until you pin point the error exactly.
Alon.
No there is a Probability column...
i.e "[lsContactStatus].[Probability] as [Probability], " which is correct from the first cut..
But your query tries to get it from contacts which does not have a probability column i.e
" [Contacts].[Probability]".
The only way I could get this to work was to fudge the order by clauses, but this is too hard.
Note this si only a problem with paging..