?
Solved

select top 10 < records count

Posted on 2005-02-28
15
Medium Priority
?
263 Views
Last Modified: 2008-02-01
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
Comment
Question by:humer2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 13

Expert Comment

by:nurbek
ID: 13425171
this should return top 10 records

maybe the there is another missing part in your code?
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13425186
it should not give  error if the records less than 10
0
 
LVL 12

Expert Comment

by:fruhj
ID: 13425398
please post the error message for us
0
Independent Software Vendors: 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!

 
LVL 11

Expert Comment

by:coopzz
ID: 13425405
whats the error?  show it.
what db you using, calling code?

0
 
LVL 5

Expert Comment

by:cristy56
ID: 13427314
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
 
LVL 1

Author Comment

by:humer2000
ID: 13427722
the error is :

error '80020009'
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13427755
which line?

what is the code there?
0
 
LVL 5

Expert Comment

by:frrf
ID: 13427880
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
 
LVL 1

Author Comment

by:humer2000
ID: 13427969
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
 
LVL 1

Author Comment

by:humer2000
ID: 13428083
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
 
LVL 13

Expert Comment

by:nurbek
ID: 13428275
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
 
LVL 1

Author Comment

by:humer2000
ID: 13428297
it returns a number
in my case it returns 10
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13428342
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
 
LVL 10

Accepted Solution

by:
thefritterfatboy earned 2000 total points
ID: 13428364
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
 
LVL 13

Expert Comment

by:nurbek
ID: 13428793
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

800 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