"SELECT Count" giver for mange resultater.
Hej!Jeg har en asp kode, som jeg bruger til at udskrive fra en SQL database, når der bliver søgt på et bogstav (f.eks. id=a eller id=b). Den lister altså resultaterne af et search på poster i databasen, som starter med a eller b (id). Problemet er, at den finder flere resultater end der er i databasen! Den skriver simpelthen 2,???? gange flere resultater, end der er i databasen. Dette er jo et problem mht. paging. Der er ca. et par tusind resultater til hvert bogstav...
Den der løser problemet modtager 125 point.
Her er koden:
<%
'Først skal vi have antallet af poster
sql = "SELECT Count(*) As Antal From tblC where left(Ku,1) = '"&request("id")&"' or left(Ku,1) = '"&Ucase(request("id"))&"'"
Set objRs2 = forbind.execute(sql)
response.write objrs2("Antal")
intRecords = int(objRs2("Antal"))
objRs2.Close
Set objRs2 = Nothing
'Variabler til query´et
intPage = Request.QueryString("page")
if intPage = "" OR not isNumeric(intPage) then
intPage = int(1)
else
intPage = int(intPage)
end if
intPageSize = int(50)
intStart = intPage*intPageSize
intStart = intStart-intPageSize
intPages = int(intRecords/intPageSize)+1
Set objRs = Forbind.Execute("Select Count(ku) As Antal, ID, ku, al, ge, ov From tblC where left(ku,1) = '"&request("id")&"' or left(ku,1) = '"&Ucase(request("id"))&"' group by ku LIMIT " & intStart & ", " & intPageSize & " ;")
if objRs.EOF then
Response.Write ""
else
Do while not objRs.EOF
If blnBGColor = True Then
strBGColor = "#B0C4DE"
blnBGColor = False
Else
strBGColor = "#D9E0EC"
blnBGColor = True
End If
%>
<% if objRs("Antal") <> 1 then%>
<tr bgcolor="<%=strBGColor%>" style="cursor:hand" onMouseOver=TG(this,"#FFFFFF") onMouseout=TG(this,'') onclick="window.location='Liste.asp?ku=<%=objRs("ku")%>&FrontSearch=ku&Searchtext=<%=Soegeord%>'" >
<%else%>
<tr bgcolor="<%=strBGColor%>" style="cursor:hand" onMouseOver=TG(this,"#FFFFFF") onMouseout=TG(this,'') onclick="window.location='visanmeldelse.asp?id=<%=objRs("id")%>&FrontSearch=ku&Searchtext=<%=Soegeord%>'" >
<%end if%>
<td class="menu" width="5"> </td>
<td class="menu" width="40%"> <b><%=objrs("ku")%></b></td>
<% if objRs("Antal") <> 1 then%>
<td class="menu" width="40%"> <b><%=objRs("Antal")%> cd'er</b></td>
<%else%>
<td class="menu" width="40%"> <%=objrs("al")%></td>
<%end if%>
<%
if objrs("ge") = "" then
ge = "-"
else
sql2 = "select * from ge where Id = "&objrs("ge")&""
set rs2 = forbind.execute(sql2)
ge = rs2("ge")
set rs2 = Nothing
end if
%>
<% if objRs("Antal") = 1 then%>
<td class="menu" width="15%"> <%=ge%></td>
<td class="menu" width="5%"> <% if objrs("ov") <> "" then%><%=objrs("ov")%><%else%>-<%end if%></td>
<%else%>
<td class="menu" width="15%"> </td>
<td class="menu" width="5%"> </td>
<%end if%>
</tr>
<%
objRs.MoveNext
Loop
end if
%>
</table>
<table width=100%><tr><td>
<%
'Udskriver en "sideliste"
For i = 1 to intPages
if intPages = 1 then
else
if i = intPage then
Response.Write "<b>" & i & "</b> "
else
Response.Write "<a href=""ku-search.asp?id="&request.querystring("id")&"&page=" & i & """>"
Response.Write i
Response.Write "</a> "
end if
end if
next
%>
</td>
</tr>
</table>
<%
objRs.Close
Set objRs = Nothing
%>
