asked on

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"%>



<%
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
%>



<html>
<head>
<title>MOTOSHOP - Loja de acessórios e vestuá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">

<tr>
<td colspan="3" height="10"></td>
</tr>
<tr>
<td width="160" valign="top">
       
</td>
<td width="9" valign="top"> </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">  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ço</font></a>
            </td>
<td align="right" bgcolor="#f7f7f7" class="tahoma11">
                  <% if rsProdutos.recordcount > 0 then %>
              <%= rsProdutos.recordcount%> Produtos disponíveis  
                  <% 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 %>
                                           
                                           <% '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">
              <%= 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

                   %>  
</div>
            </td>
</tr>
            </form>
<tr>
<td colspan="2"> </td>
</tr>
</table></td>
</tr>
<tr>
<td height="35"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="3"></td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
</table>

<%
rsProdutos.Close()
%>

Any help is greatly appreciated.

ASKER CERTIFIED SOLUTION

DcpKing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial