MySQL recordcount error message (E_FAIL)

Hi all:

I have a very strange problem, that i've never seen before using a Mysql DB with asp.
Its a simple e-shop and a relatively simple DB with Products, Brands, product type, product category simple tables.
I was creating a display page with navigation system, so i change the cursors to 3,3,3 (the only way i could get recordcount to work).  It works fine, except with some records from the products table (????)

Try it here:
www.motonline.pt/acessorios/index.asp > select GERAL > select NAVEGAÇÃO E GPS and you can see the error.

any other selection works fine.  These records have all fields fille, like all the others. It hangs on the recordcount operation, and i just dont know why, because it works with every other products.

here is my code:


<%@LANGUAGE="VBSCRIPT"%>

<!--#include file="../Connections/connacessorios.asp" -->

<%
Dim rsProdutos
Dim rsProdutos_numRows
Dim MMColParam1
Dim MMColParam3
Dim qryID 'querystring ID
Dim qryCAT 'querystring CAT
Dim qryART 'querystring ART
Dim qrySORT ''querystring SORT

qryID = request.querystring("ID")
qryCAT = request.querystring("CAT")
qryArt = request.querystring("ART")

if request.querystring("SORT") <> "" then qrySORT = request.querystring("SORT")

'get the categories name

Dim rsCategorias
Dim rsCategorias_numRows

Set rsCategorias = Server.CreateObject("ADODB.Recordset")
rsCategorias.ActiveConnection = MM_connacessorios_STRING
rsCategorias.Source = "SELECT CAT_NOME from tbl_cat WHERE CAT_ID = " & qryCAT & ""
rsCategorias.CursorType = 0
rsCategorias.CursorLocation = 2
rsCategorias.LockType = 1
rsCategorias.Open()

Dim rsArtigos2
Dim rsArtigos2_numRows

'get product type name

Set rsArtigos2 = Server.CreateObject("ADODB.Recordset")
rsArtigos2.ActiveConnection = MM_connacessorios_STRING
rsArtigos2.Source = "SELECT ART_NOME from tbl_artigos WHERE ART_ID = " & qryART & ""
rsArtigos2.CursorType = 0
rsArtigos2.CursorLocation = 2
rsArtigos2.LockType = 1
rsArtigos2.Open()


'this is because there are products for man, and products for motorcycle

if request.querystring("ID") = "" then
MMColParam1 = 2
else
MMColParam1 = 1
end if

MMColParam3 = "MARCA_NOME, PROD_NOME"
if request.querystring("SORT") <> "" then MMColParam3 = request.querystring("SORT")


'get products that correspond the actual request (ID, CAT and ART querystring)

Set rsProdutos = Server.CreateObject("ADODB.Recordset")
rsProdutos.ActiveConnection = MM_connacessorios_STRING
rsProdutos.Source = "SELECT  PROD_ID, PROD_NOME, PROD_PREÇO, PROD_FOTO, PROD_SALDO, PROD_NCLICKS, PROD_ENCOMENDAS, PROD_SEXO, ART_NOME, MARCA_NOME  FROM acessorios.tbl_produtos, tbl_artigos, tbl_marcas  WHERE PROD_MARCA = MARCA_ID AND PROD_ARTIGO = ART_ID AND ART_ID = " & request.QueryString("ART") & " AND PROD_CAT = " & request.querystring("CAT") & " ORDER BY " & MMColParam3 & ""
rsProdutos.CursorType = 3
rsProdutos.CursorLocation = 3
rsProdutos.LockType = 3

'this is because of the navigation system
if  request("Button")= "" then PageNo=1 else PageNo=request("Button")
 
rsProdutos.Open()
rsProdutos.Pagesize = 12
rsProdutos.Cachesize = 12

if not (rsProdutos.EOF and rsProdutos.BOF) then      rsProdutos.AbsolutePage = PageNo
rsProdutos_numRows = 0
%>

<!--#include file="../functions/trimproperly.asp" -->

<html>
<head>
<title>MOTOSHOP - Loja de acess&oacute;rios e vestu&aacute;rio MOTONLINE</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css.css" rel="stylesheet" type="text/css">

</head>
<body>
<table width="777" border="0" cellspacing="0" cellpadding="0">
  <!--#include file="barra_nav_top.asp" -->
  <tr>
    <td colspan="3" height="10"></td>
  </tr>
  <tr>
    <td width="160" valign="top">
        <!--#include file="left_column.asp" -->
    </td>
    <td width="9" valign="top">&nbsp;</td>
    <td width="608" valign="top">
      
      <table width="100%"  border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td height="11" colspan="2" ></td>
      </tr>
      <tr>
        <td height="30" colspan="2" class="style1" >
              <%= "<font color=""#000000"">" & ucase(rsCategorias("CAT_NOME")) & " ></font><font color=""#609000""> " & ucase(rsArtigos2("ART_NOME")) & "</font>" %>
            </td>
      </tr>
      <tr>
        <td height="40" align="left" valign="middle" bgcolor="#f7f7f7" class="tahoma11">
            <span class="tahoma11gray2">&nbsp;&nbsp;Ordenar por:</span>
            <a href="produtos.asp?ID=<%= qryID%>&CAT=<%= qryCAT%>&ART=<%= qryART%>&SORT=MARCA_NOME,PROD_NOME"><font color=#000000>Marca</font></a> |
            <a href="produtos.asp?ID=<%= qryID%>&CAT=<%= qryCAT%>&ART=<%= qryART%>&SORT=PROD_NOME"><font color=#000000>Produto</font></a> |
            <a href="produtos.asp?ID=<%= qryID%>&CAT=<%= qryCAT%>&ART=<%= qryART%>&SORT=PROD_PREÇO,MARCA_NOME,PROD_NOME"><font color=#000000>Pre&ccedil;o</font></a>
            </td>
        <td align="right" bgcolor="#f7f7f7" class="tahoma11">
                  <% if rsProdutos.recordcount > 0 then %>
                <%= rsProdutos.recordcount%> Produtos disponíveis&nbsp;&nbsp;
                  <% end if %>
            </td>
      </tr>
      <tr>
        <td colspan="2" align="left"><hr size="1" noshade></td>
      </tr>
      <tr>
        <td colspan="2" align="left">
            
            <table width="100%"  border="0" cellspacing="0" cellpadding="0">
             <%
              startrw2 = 0
              endrw2 = HLooper3__index
              numberColumns2 = 4
              numrows2 = 3
              while((numrows2 <> 0) AND (Not rsProdutos.EOF))
                  startrw2 = endrw2 + 1
                  endrw2 = endrw2 + numberColumns2
             %>
          <tr>
            <td height="6"></td>
            <td colspan="3"></td>
            <td></td>
          </tr>
          <tr>
              <%
              While ((startrw2 <= endrw2) AND (Not rsProdutos.EOF))
              %>
            <td align="left" width="25%" >
                  <table width="147" border="0" cellspacing="2" cellpadding="0" class="solidblock" height="135">
              <tr>
                <td width="40" rowspan="2" valign="top" align="center" height="43">
                          <% if rsProdutos("PROD_SEXO") = 2 then %>
                          <img src="images/female.gif">
                                <% if rsProdutos("PROD_SALDO") <> 0 then %>
                                <img src="images/tag_save.gif">
                                <% else %>
                                      <% if Cint(rsProdutos.Fields.Item("PROD_NCLICKS").Value) > 500 then %>
                                      <img src="images/tag_hot.gif">
                                      <% 'else %>
                                            <%' if rsProdutos("DATA_INS") > (date() - 30) then %>
                                            <!--<img src="images/tag_new.gif">-->
                                            <% 'end if %>
                                      <% end if %>
                                <% end if %>
                          <% end if %>
                        </td>
                <td width="102" height="33" colspan="2" align="right" valign="middle" class="arial11boldcyan">
                        <% strProdAcess = (rsProdutos("MARCA_NOME") & " " & (replace(rsProdutos("PROD_NOME"), "<font color=red>", "")))
                        response.write DoTrimProperly((strProdAcess), 26, 1, 1, "")
                        %>
                        </td>
                </tr>
              <tr>
                <td height="10" colspan="2" class="tahoma11gray" align="right">
                        <%
                        strEspaco = instr(rsProdutos("ART_NOME")," ")
                        if strEspaco = 0 then
                        response.write (rsProdutos("ART_NOME"))
                        else
                        response.write (left(rsProdutos("ART_NOME"), (instr(rsProdutos("ART_NOME")," "))))
                        end if
                        %>
                        </td>
                </tr>
                    <tr>
                     <td colspan="3">
                     <table width="100%"  border="0" cellspacing="0" cellpadding="0">
                   <tr>
                     <td width="54%" height="70" rowspan="4" align="center" valign="top">  
                               <img src="sendbinary.asp?path=<%=((Server.URLEncode(Server.Mappath("prod_images"))) & "\" & rsProdutos.Fields.Item("PROD_FOTO").Value)%>&width=60">
                               </td>
                     <td height="14"></td>
                   </tr>
                   <tr>
                   <td class="style1" align="center" height="22">
                           <u><%= FormatCurrency((rsProdutos.Fields.Item("PROD_PREÇO").Value)) %></u>
                           </td>
                 </tr>
                 <tr>
                   <td width="64" height="17"><a href="confirmprod.asp?PROD_ID=<%= rsProdutos("PROD_ID")%>"><img src="images/but_comprar.gif" width="64" height="16" border="0"></a></td>
                 </tr>
                 <tr>
                   <td height="17"><a href="proddetail.asp?PROD_ID=<%= rsProdutos("PROD_ID")%>"><img src="images/but_detalhes.gif" width="64" height="15" border="0"></a></td>
                 </tr>
               </table>
                     </td>
                    </tr>
            </table>
                  </td>
                        <% if (startrw2 mod 4 <> 0) then %>
                        <td width="8" colspan="3"></td>
                        <% end if %>
                  <%
                  startrw2 = startrw2 + 1
                  rsProdutos.MoveNext()
                  Wend
                  %>
                  <% if rsProdutos.recordcount < 4 then %>
                  <%
                  tds = 4 - int(rsProdutos.recordcount)
                  for i = 1 to tds
                  %>
                  <td width="25%"></td>
                  <% next
                  end if %>
          </tr>
              <%
              numrows2=numrows2-1
              Wend
              %>
        </table>
            
            </td>
      </tr>
        <form name="form2" method="post" action="produtos.asp?ID=<%= qryID%>&CAT=<%= qryCAT%>&ART=<%= qryART%><% if qrySORT <> "" then response.write "&SORT=" & qrySORT%>">
      <tr>
        <td colspan="2" height="6"></td>
      </tr>
      <tr valign="top">
        <td colspan="2" ><hr size="1" noshade></td>
        </tr>
      <tr bgcolor="#f7f7f7">
        <td width="38%" class="tahoma11">
            &nbsp;&nbsp;<%= rsProdutos.Pagesize%><span class="tahoma11gray2"> Itens de </span><%= rsProdutos.Recordcount%></span></td>
        <td width="37%" height="20">
            <div align="right">
                  <span class="tahoma11">Pág.</span>      
              <% dim rsProdutos_total
                       rsProdutos_total = rsProdutos.Pagecount
                         For i = 1 to rsProdutos_total %>
                         <% if cint(i) = cint(PageNo) then
                         classbtn = "buttonpressed"
                         else
                         classbtn = "button"
                         end if %>                
                         <input type="submit"  value="<%= i%>" name="Button" class="<%= classbtn%>">
                         <%
                         if(i <> rsProdutos_total) then Response.Write("<font color=""#CECECE"">|</font>")
                         next
                         
                    %>&nbsp;&nbsp;
            </div>
            </td>
      </tr>
            </form>
      <tr>
        <td colspan="2">&nbsp;</td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td height="35">&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td colspan="3"><!--#include file="footer.asp" --></td>
  </tr>
  <tr>
    <td colspan="3">&nbsp;</td>
  </tr>
</table>


<%
rsProdutos.Close()
%>



Any help is greatly appreciated.

cbr600Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DcpKingCommented:
Get ASP to print your query instead of running it. I think you'll find that you're not enclosing a parameter within the right number of quotes andticks (  " and '  ).

For example, I think that you should have something like

MMColParam3 = " 'MARCA_NOME, PROD_NOME' "

and not

MMColParam3 = "MARCA_NOME, PROD_NOME"

hth

Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.