Solved

cant select records with where clause

Posted on 2008-10-06
6
204 Views
Last Modified: 2012-05-05
I cant select records based on a where clause and row limit.
I want the first 5 rows with where clause from1='x'. What happens is that this checks rows 1 and 5 and from that selects any that have from1='x'. so i could have 0 to 5 only .




command.CommandText = "  SELECT * FROM (  SELECT email.* " & _
      " , ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber]" & _
      " FROM email) sq WHERE RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & " and to1='x'"

Open in new window

0
Comment
Question by:jagguy
  • 3
  • 2
6 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22648479
have you tried to set your where clause in the inner query:

command.CommandText = "  SELECT * FROM (  SELECT email.* " & _
      " , ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber]" & _
      " FROM email WHERE from1='x') sq WHERE RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & " and to1='x'"
0
 
LVL 8

Expert Comment

by:mikainz
ID: 22648483

Try
WHERE (RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & ") and to1='x'"
0
 

Author Comment

by:jagguy
ID: 22648487
sorry this is the code

 command.CommandText = "  SELECT * FROM (  SELECT email.* " & _
         " , ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber]" & _
         " FROM email) sq WHERE RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & " and from1='" & from1 & "'"
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 8

Accepted Solution

by:
mikainz earned 500 total points
ID: 22648497
I think you need brackets
WHERE (RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & ") and from1='" & from1 & "'"
0
 

Author Comment

by:jagguy
ID: 22648503
ok this works


command.CommandText = "  SELECT * FROM (  SELECT email.* " & _
      " , ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber]" & _
      " FROM email WHERE from1='x') sq WHERE RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & " and to1='x'

 WHERE (RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & ") and to1='x'"
and this doesnt?
0
 

Author Comment

by:jagguy
ID: 22648516
this is best option

 command.CommandText = "  SELECT * FROM (  SELECT email.* " & _
          " , ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber]" & _
          " FROM email WHERE from1='x') sq WHERE RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
AJAX pass along a variable 3 46
Returning a value from a class function to a partial view in MVC5 7 28
IIF in access query 19 23
SQL Recursion 6 16
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question