record count shows value -1

[neenaprasad]

I have to take values from two tables with single sql statement.


SQl = SELECT MemAccounts.MemberID,COUNT(MemAccounts.MemberID) AS Total, Membership.First_Name, Membership.Last_Name FROM MemAccounts, Membership WHERE MemAccounts.IntroduceeID <> 0 AND Membership.IsAgent = 1 AND MemAccounts.MemberID = Membership.MemberID AND MemAccounts.TransDate BETWEEN '" &x_FromDate& "' AND '" &x_ToDate& "' GROUP BY MemAccounts.MemberID, Membership.First_Name, Membership.Last_Name ORDER BY Total DESC


i got the correct output in Query Analyser but while running the form there is no output. Record Count is -1.(its not counting the records)

[idsanjeev]

Hi neenaprasad
you already know that if your sql statement is run then problems in scripts so please post that or attached file for find out whats is the problems

Thanks
Regards

[neenaprasad]

Set x_FromDate = request.QUERYSTRING("x_FromDate")
If isdate(x_fromDate) then
x_FromDate = CDate(x_FromDate) 'Request.Form("x_FromDate")
end if

Set x_ToDate = request.QUERYSTRING("x_ToDate")
If isdate(x_ToDate) then
x_ToDate = CDate(x_ToDate)'Request.Form("x_FromDate")
End If

If x_FromDate = "" Then
'x_FromDate = DateTime.Today;
'x_ToDate = ""
x_FromDate = Month(Now()) & "/" & "1" & "/" & Year(Now())
x_ToDate = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
End If


StrSql = "SELECT MemAccounts.MemberID,COUNT(MemAccounts.MemberID) as Total, Membership.First_Name, Membership.Last_Name FROM MemAccounts, Membership WHERE MemAccounts.IntroduceeID <> 0 AND Membership.IsAgent = 1 AND MemAccounts.MemberID = Membership.MemberID AND MemAccounts.TransDate BETWEEN '" &x_FromDate& "' AND '" &x_ToDate& "' GROUP BY MemAccounts.MemberID, Membership.First_Name, Membership.Last_Name ORDER BY Total DESC "

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open StrSql, conn, 1, 2
nTotalRecs = rs.RecordCount

%>
<%=x_FromDate%>
<%=x_ToDate%>
<%=rs.RecordCount%>


In query analyser it is getting the correct output but in asp form record count is -1. If i remove the count from the above query the output is correct but MemberID is repeating. I need to count out how many times the MemberID occured in the MemAccounts table satisfying the condition..

Can u help me??

[DrBunchman]

Hi neenaprasad,

You don't need to use a recordset object recordcount property because you've already included a count in your query. You've assigned the name Total to your count in the query so simply output that:

<%=rs("Total")%>

The reason that the recordcount is returning -1 is because of the type of cursor you are using with your recordset. The default is adOpenForwardOnly which does not allow the recordcount property. For a fuller explanation of the cursor and how you can change it (very easy to do!) try

http://www.w3schools.com/ado/ado_ref_recordset.asp

or

http://www.google.co.uk/search?hl=en...nG=Search&meta=

I hope this helps,

Dr B

[jhardman]

I have to take values from two tables with single sql statement.


SQl = SELECT MemAccounts.MemberID,COUNT(MemAccounts.MemberID) AS Total, Membership.First_Name, Membership.Last_Name FROM MemAccounts, Membership WHERE MemAccounts.IntroduceeID <> 0 AND Membership.IsAgent = 1 AND MemAccounts.MemberID = Membership.MemberID AND MemAccounts.TransDate BETWEEN '" &x_FromDate& "' AND '" &x_ToDate& "' GROUP BY MemAccounts.MemberID, Membership.First_Name, Membership.Last_Name ORDER BY Total DESC


i got the correct output in Query Analyser but while running the form there is no output. Record Count is -1.(its not counting the records)

Do you have any indication that you are connecting to the db? What is your connection string? Do you get any error when you connect?

Jared

[DrBunchman]

This thread was reposted (with a lot more detail it has to be said) by the OP here:

http://www.thescripts.com/forum/thread777769.html

Dr B

[chirag1989]

Have u set your cursortype ?
just try out this

rs.open SQl,con,3

i got the correct output in Query Analyser but while running the form there is no output. Record Count is -1.(its not counting the records)[/quote]