Counter til records

Er det noget i denne stil du søger:

counter = 0
Set objRS = objConn.Execute("SELECT * FROM ShopProductCategory ORDER BY Category")
do while not objRS.eof
...
...
  counter = counter + 1
  objRS.MoveNext
loop
response.write "Der var "& counter &" resultater"

det var også mit umiddelbare gæt, og virker fint hvis der kun er en record i ShopProductCategory. Jeg har to tabeller i min database. En der hedder ShopProductCategory, som indeholder: CId og Category. Derudover har jeg en anden tabel der hedder Products, som indeholder PId, CId, Productname .... Price. Her er en relation til CId i ShopProductCategory. Det jeg ønsker er at den skal liste alle records i ShopProductCategory, hvor den efter hver enkel record sætter (etTal) efter, som er det antal records fra Products hvor relationen CId passer sammen.

Så er jeg med. Så kan du bruge denne:

<%
Set objRS = objConn.Execute("SELECT ShopProductCategory.Cid, ShopProductCategory.Category, COUNT(Products.Pid) AS antal FROM ShopProductCategory INNER JOIN Products ON Products.CId = ShopProductCategory.Cid GROUP BY ShopProductCategory.Cid, ShopProductCategory.Category")
do while not objRS.eof %>

<tr>
  <td><strong><a href="detail.asp?id=<%=objRS("CId")%>"><%=objRS("Category")%> (<%=objRS("antal")%>)</a></strong></td>
</tr>
<%
  objRS.MoveNext
loop %>

Jeg takker og afprøver straks løsningen

hmm jeg får en fejl:

Set objRS = objConn.Execute("SELECT ShopProductCategory.Cid, ShopProductCategory.Category, ShopProductCategory.Comment COUNT(ShopProducts.Pid) AS antal FROM ShopProductCategory INNER JOIN ShopProducts ON ShopProducts.CId = ShopProductCategory.Cid GROUP BY ShopProductCategory.Cid, ShopProductCategory.Category")


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-4.0.18-standard]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(ShopProducts.Pid) AS antal FROM ShopProductCategory INNER

/shop.asp, line 23

Du mangler et komma før count, og Comment skal også på GROUP BY listen:

Set objRS = objConn.Execute("SELECT ShopProductCategory.Cid, ShopProductCategory.Category, ShopProductCategory.Comment, COUNT(ShopProducts.Pid) AS antal FROM ShopProductCategory INNER JOIN ShopProducts ON ShopProducts.CId = ShopProductCategory.Cid GROUP BY ShopProductCategory.Cid, ShopProductCategory.Category, ShopProductCategory.Comment")

smid lige et svar :)

.o) <-- One Eyed Jack