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

jagguyAsked:
Who is Participating?
 
mikainzConnect With a Mentor Commented:
I think you need brackets
WHERE (RowNumber BETWEEN " & pageNo & " AND " & rowsPerPage & ") and from1='" & from1 & "'"
0
 
É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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.