Solved

MySQL recordcount error message (E_FAIL)

Posted on 2004-10-27
657 Views
Last Modified: 2012-08-14
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.

0
Question by:cbr600
    1 Comment
     
    LVL 16

    Accepted Solution

    by:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now