displaying the number of records found

[sthmpsn1]

I have a page where I want to list the number of records found. my recordset is called dbrs2 and I am using the following code.

<input type=&quot;text&quot; name=&quot;recordcount&quot; size=&quot;2&quot; maxlength=&quot;2&quot; onFocus=&quot;showAnswers()&quot; value=&quot;<%=dbrs2.RecordCount%>&quot;>

it is displaying -1 in the text box when 3 would be the correct answer. How do I fix this?

 

[BBirkett]

Are you opening your recordset with
Set RS = DataConnection.Execute(sql) ???

Or are you using RS.Open(sql, DataConnection) ???

I can't remember which one, but one will give you the correct results. Brett Birkett B.Comp
Systems Analyst

 

[sthmpsn1]

I am opening with

<%dbrs2.open (&quot;Select * From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ), dbconn, 0, 1 %>

 

[BBirkett]

If you want a quick solution, you could create another recordset just for getting the recordcount....

sql = &quot;SELECT Count(*) As NumRecs From Timesheet where userID = .... etc;&quot;

Set RSCount = DataConn.Execute(sql)

Response.Write RSCount(&quot;NumRecs&quot

You would still need the query you already have with dbrs2, but would just add this recordset to your code, with the sole purpose of getting the record count. I would also be interested to know why that RecordCount property only works sometimes!! I've read it, and can't remember it But the above would get you going if you are in a hurry. Brett Birkett B.Comp
Systems Analyst

 

[JuanitaC]

You most likely have the wrong cursor type. If you are using the default, it doesn't support the RecordCount property. I can't remember which ones do support the RecordCount property off hand, but Link9 has written an excellent FAQ on the Recordset properties. Check it out.

faq333-618

I think it is either the adOpenKeyset or adOpenDynamic that you should try.

 

[sthmpsn1]

so how would I write this. This is all new too me. Here is what I have and it errors out. So I know I am writing it wrong.

<% If (Request.Form(&quot;employee&quot <> &quot;&quot and (Request.Form(&quot;month&quot <> &quot;&quot Then %>
<%dbrs2.open (&quot;Select * From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ), dbconn, 0, 1 %>
<%sql = &quot;SELECT Count(*) As NumRecs From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ) , dbconn, 0, 1 %>
<%Set RSCount = DataConn.Execute(sql)%>
<%Response.Write RSCount(&quot;NumRecs&quot%>

<input type=&quot;text&quot; name=&quot;employee1&quot; value=&quot;<%=Request.Form(&quot;employee&quot%>&quot;>
<input type=&quot;text&quot; name=&quot;month1&quot; value=&quot;<%=Request.Form(&quot;month&quot%>&quot;>
<table width=&quot;89%&quot; border=&quot;1&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot;>
<tr>
<td>
<div align=&quot;center&quot;><b>Month</b></div>
</td>
<td>
<div align=&quot;center&quot;><b>Date</b></div>
</td>
<td>
<div align=&quot;center&quot;><b>Hours</b></div>
and so on

 

[BBirkett]

This part is wrong:

<%sql = &quot;SELECT Count(*) As NumRecs From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ) , dbconn, 0, 1 %>

It should be

<% sql = &quot;SELECT Count(*) As NumRecs From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; %>

Then you can do your

<%Set RSCount = DataConn.Execute(sql)%>

etc

Brett Birkett B.Comp
Systems Analyst

 

[JuanitaC]

Here is where you are opening your recordset:

dbrs2.open (&quot;Select * From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ), dbconn, 0, 1

Change the 0 to 2 for adOpenDynamic

dbrs2.open (&quot;Select * From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ), dbconn, 2, 1

Then you should be able to get your recordcount with:
Response.write(&quot;Count is &quot; & dbrs2.RecordCount & &quot;<br>&quot

If that works, then you can get rid of this:

<%sql = &quot;SELECT Count(*) As NumRecs From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ) , dbconn, 0, 1 %>
<%Set RSCount = DataConn.Execute(sql)%>
<%Response.Write RSCount(&quot;NumRecs&quot%>

 

[BBirkett]

JuanitaC,
Have you tested that 2, 1 thing? I just tried it on some of my asp scripts, and I can't get it to show me the recordcount. Cant get any combination of Types, and Locations to work for that matter!!

Hardest property in the world to get working! Brett Birkett B.Comp
Systems Analyst

 

[JuanitaC]

I have used it in the past, but I don't have the code with me, so I can't double check it, and I can't remember exactly what it is off the top of my head... but I do know it is possible I don't really like the &quot;declare everything on one line&quot; syntax though... I forget which order things are supposed to be in. I prefer this syntax (from Link9's FAQ):

dim rs
set rs = server.createobject (“ADODB.Recordset”)
rs.activeconnection = con
rs.cursortype = 2 'adOpenDynamic
rs.cursorlocaton = 3 'adUseClient
rs.locktype = 3 'adLockOptimistic
rs.open “SELECT * FROM myLittleTable”

 

[JuanitaC]

OK... I looked up the problem on 4GuysFromRolla, and they suggest using 3 (adOpenStatic), 3 (adUseClient) -- not 2, 1

So

dbrs2.open (&quot;Select * From Timesheet where userID = '&quot; & request.form(&quot;employee&quot& &quot;' and month = '&quot; & request.form(&quot;month&quot& &quot;'&quot; ), dbconn, 3, 3

should work. Here's the link to the 4GuysFromRolla FAQ:

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=139

 

[joeoz]

Just a thought. You could change direction here a bit and have a pretty good solution. I recently came across the article below (courtesy of someone elses recent post) and have been convinced to use the GetRows() method. If you used this mehtod then you would have your results in an array and could just grab the length of the array.

http://www.learnasp.com/learn/whygetrows.asp