Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

cant select records with where clause

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
jagguy
Asked:
jagguy
  • 3
  • 2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
mikainzCommented:

Try
WHERE (RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & ") and to1='x'"
0
 
jagguyAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mikainzCommented:
I think you need brackets
WHERE (RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & ") and from1='" & from1 & "'"
0
 
jagguyAuthor Commented:
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
 
jagguyAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now