• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

select top 10 < records count

Hi

i have this SQl statement

sql = select top 10 * from products where promo='Yes'

This sql is returning results if at if has found at least 10 records bu
it returns an error if records found are less than 10

any one has fix ?

thx
0
humer2000
Asked:
humer2000
1 Solution
 
nurbekCommented:
this should return top 10 records

maybe the there is another missing part in your code?
0
 
nurbekCommented:
it should not give  error if the records less than 10
0
 
fruhjCommented:
please post the error message for us
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!

 
coopzzCommented:
whats the error?  show it.
what db you using, calling code?

0
 
cristy56Commented:
u can add another sql to check if there contains more than 10 records in your database first, if so, then use back this one.


dim sql0
sql0 = select count(*) as tmp from products where promo='Yes'

dim no_of_records
no_of_records = sql0("tmp")

if no_of_records >= 10 then
    sql = select top 10 * from products where promo='Yes'
else
    sql = select top no_of_records * from products where promo='Yes'
end if


Regards,
Cristy
0
 
humer2000Author Commented:
the error is :

error '80020009'
0
 
nurbekCommented:
which line?

what is the code there?
0
 
frrfCommented:
nothing wrong with the sql statement, it is error from your asp code, maybe you didn't handle null properly,.
anyway, please show us the asp code, then we can debug there.
0
 
humer2000Author Commented:
here is the code
as i told you the error is only error '80020009'
no lines specified



Sub Top5manual
dim conn, sql, rs, products, i

if getconfig("xTopTopSalesdynamic")<>"" then
sql="select top "&clng(getconfig("xTopTopSalesdynamic"))&" * from products where bestof='oui' and  ( hide=0 or hide is null ) "
else
sql="select * from products where bestof='oui' and ( hide=0 or hide is null ) "
end if
      
shopopendatabase conn
      
set rs=conn.execute(sql)
'      
response.write "<table border=0 cellpadding=2 cellspacing=2>"
response.write "<tr valign=top>"
'      
do while not rs.eof
            for i=1 to clng(getconfig("xTopTopSalesdynamic"))
      
      response.write"<td>"
      '===========================================
      produit (rs("catalogID")) , "TOPSALES"
      '===========================================
      response.write "</td>"            
      
             if (i + 1) mod TopSellerProductsPerRow = 0 then response.write "</tr><tr>"
      
      rs.movenext
      next
loop
      
response.write "</tr>"
response.write "</table>"
rs.close
set rs=nothing
shopclosedatabase conn    
end sub
0
 
humer2000Author Commented:
here is the new code
now i have an error

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'sql0'

/topsales.asp, line 59



Sub Top5manual
      If getconfig("Xlogonrequired")="Yes" then
      If GetSess("Login")= "" then exit sub
      end if
dim conn, sql, rs, products, i
'
dim sql0
sql0 = "select count(*) as tmp from products where bestof ='oui' "
'
dim no_of_records
no_of_records = sql0("tmp")  (this is line 59)
'
response.write "total records : " & no_of_records
'
if getconfig("xTopTopSalesdynamic")<>"" then
      if no_of_records >= clng(getconfig("xTopTopSalesdynamic")) then
          sql = "select top "& clng(getconfig("xTopTopSalesdynamic")) &" * from products where bestof='oui' "
      else
          sql = "select top "& no_of_records &" * from products where bestof='oui' "
      end if
else
sql="select * from products where bestof='oui' "
end if
'
Sql=sql & " and ( hide=0 or hide is null ) "
'

shopopendatabase conn
      
set rs=conn.execute(sql)
'      
response.write "<table border=0 cellpadding=2 cellspacing=2>"
response.write "<tr valign=top>"
'      
do while not rs.eof
            for i=1 to clng(getconfig("xTopTopSalesdynamic"))
      
      response.write"<td>"
      '===========================================
      produit (rs("catalogID")) , "TOPSALES"
      '===========================================
      response.write "</td>"            
      
             if (i + 1) mod TopSellerProductsPerRow = 0 then response.write "</tr><tr>"
      
      rs.movenext
      next
loop
      
response.write "</tr>"
response.write "</table>"
rs.close
set rs=nothing
shopclosedatabase conn    
end sub
0
 
nurbekCommented:
what getconfig("xTopTopSalesdynamic")  returns?


if getconfig("xTopTopSalesdynamic")<>"" then
sql="select top "&clng(getconfig("xTopTopSalesdynamic"))&" * from products where bestof='oui' and  ( hide=0 or hide is null ) "
else
sql="select * from products where bestof='oui' and ( hide=0 or hide is null ) "
end if

'or in order to make sure try like this
'sql="select top 10 * from products where bestof='oui' and  ( hide=0 or hide is null ) "
0
 
humer2000Author Commented:
it returns a number
in my case it returns 10
0
 
nurbekCommented:
your own code seems to be ok (use your own code)


in which line do you get error?

shopopendatabase conn
     
set rs=conn.execute(sql)
0
 
thefritterfatboyCommented:
Your "for i = 1 to..." line is looping through records that aren't there. You've selected the correct amount of records from your database - you don't need to loop through the numbers. Just loop through ALL the results like this:


i = 1
do while not rs.eof
i = i + 1
     response.write"<td>"
     '===========================================
     produit (rs("catalogID")) , "TOPSALES"
     '===========================================
     response.write "</td>"          
     
           if i mod TopSellerProductsPerRow = 0 then response.write "</tr><tr>"
     
     rs.movenext
loop
0
 
nurbekCommented:
yes, thefritterfatboy  points to you :)

humer2000 :
you need to check whether rs.eof or not while in For .. Loop

do while not rs.eof
          for i=1 to clng(getconfig("xTopTopSalesdynamic"))
     If Not rs.Eof Then
     response.write"<td>"
     '===========================================
     produit (rs("catalogID")) , "TOPSALES"
     '===========================================
     response.write "</td>"          
     rs.movenext
     Else
       'print blank cells
       response.write"<td></td>"
     End If
           if (i + 1) mod TopSellerProductsPerRow = 0 then response.write "</tr><tr>"  

     next
loop
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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