Solved

cant select records with where clause

Posted on 2008-10-06
6
198 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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility

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

Author Comment

by:jagguy
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Accepted Solution

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

Author Comment

by:jagguy
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now