[Antisoft]

I've searched every resource (or atleast all the ones I hade the patience to search through) for a way to list how many records there are in my table. I've built a database in Access and want to list how many movies there are.

I can't find a single command to where I retreive this number.
Please help me... I can see in Access that I have 300+ movies but how can I extract that number? Gosh... plz help

Thanks in advance...
/Antisoft

[SlashRaid]

What about RecordCount?

[edit]:Putting ASP to Work ~ Web Savant[/edit]

[Antisoft]

It doesn't work... I added the variable and it keeps returning -1... and I know for a fact that this isn't correct...

This is my code:

<!-- #include virtual="adovbs.inc" -->
<%
	Set RS = Server.CreateObject("ADODB.Recordset")
	Set Connection = Server.CreateObject("ADODB.Connection")

	Connection.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\filmer.mdb"
	SQL = "SELECT * From filmer ORDER BY titel"
        Set RS = Connection.Execute(sql)
        TotalRecs = RS.recordcount
%>

<% response.write TotalRecs %> movies in the database.

[klewlis]

I believe this is due to your cursor type. I'm not sure what the proper one should be in this case because I never use that type of recordset, but it might be worth checking into. (I *think* it should be adOpenForwardOnly :)

[Amadeus]

Yeah, for sure it's the cursor type. I'm sure either adOpenForwardOnly or Dynamic will allow .Recordcount to be supported.

[klewlis]

Amadeus, on Nov 4 2002, 09:33 PM, said:

Yeah, for sure it's the cursor type. I'm sure either adOpenForwardOnly or Dynamic will allow .Recordcount to be supported.

hey I'm surprised I was right. :P

I use a different type of connection and recordset system and have never really learned the other way, so I never have to deal with cursor types. :)

[Antisoft]

Amadeus, on Nov 4 2002, 08:33 PM, said:

Yeah, for sure it's the cursor type. I'm sure either adOpenForwardOnly or Dynamic will allow .Recordcount to be supported.

Hum... you mean this with dynamic?

Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("filmer.mdb")

I tried that, still returns -1 movies in database.
I don't know what u mean with adOpenForwardOnly. I'm sorta new to ASP (ok, very new).
Hope u have time to explain further.
Thx /Antisoft

[Antisoft]

I still haven't figured it out... and I still don't know what u mean with adOpenForwardOnly.... plz help.

Would be great!
Thx!

[Amadeus]

Antisoft, on Nov 4 2002, 04:55 PM, said:

It doesn't work... I added the variable and it keeps returning -1... and I know for a fact that this isn't correct...

This is my code:

<!-- #include virtual="adovbs.inc" -->
<%
	Set RS = Server.CreateObject("ADODB.Recordset")
	Set Connection = Server.CreateObject("ADODB.Connection")

	Connection.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\filmer.mdb"
	SQL = "SELECT * From filmer ORDER BY titel"
        Set RS = Connection.Execute(sql)
        TotalRecs = RS.recordcount
%>

<% response.write TotalRecs %> movies in the database.

Try this instead of using the Connection.Execute statement

RS.Open = "SELECT * From filmer ORDER BY titel",Connection, 3,2
response.write RS. RecordCount

Using RecordCount is just a matter of getting the cursor types right. You see where I've got the 3 and 2? Those are constants representing cursortypes. The constants can vary depending on the system that you are using. I'm sure we can find the right ones for you.

If you can't be bothered to mess around with it, however, another simple solution is to run a small loop. Once you've got a recordset, do the following.

i = 1
RS.MoveFirst
Do until RS.EOF
   i = i + 1
   RS.MoveNext
Loop
response.write "Total records = " & i

Or, if your database system supports it, you can do a select statement like the following:
"SELECT COUNT(*) as total FROM filmer"

[Antisoft]

Great!
The only thing that I got to work was the small loop thingie...
I hade to make another recordset because when I did the loop from the same recordset as the one displaying the titles of the films it just didn't display the names. Anyhow it works.

Thx!

[Garstor]

You are getting a -1 because the value of .RecordCount is unknown.

To get an accurate count with a dynamic recordset you must move to the last record. It stands to reason that you will have to move back to the beginning if you still need to move through and perform some sort of processing on the rows.

If all you are interested in is the number of records then you are better served by changing your original query to something like:

SELECT COUNT(*) FROM Filmer

Another thing to consider when working with a database resultset is to check if any records exist before trying to process them:

IF recordset.BOF AND recordset.EOF THEN
  MsgBox "No records exist!"
ELSE
  '* Do your processing here
END IF

I'm strictly a VB / SQL Server kind of guy and have never used ASP or ASP.NET so you may have to tinker with this code to make it work for you. Switching to SQL Server would let you use stored procedures which gives you much greater power (but who can afford MSSS? )

Regards,

[Antisoft]

Yeah that helped with understanding the .RecordCount think, thanks.
But, however I do want to list all the records in the database at the same time so changing my query to that won't work and the reason why I didn't choose to make it display "No records exist!" is coz I knew there were and will always be records in that particular database. Just made it simple coz it had to be fast.

The method I'm using now is slowing it down a bit but works just fine so I'll stick with that but I could change the query on my second connection (coz I made two connections to make it work) to what you suggested but I want to find a way to make all of the above (displaying and counting the records) in one recordset coz that has to be much faster.

Thx anyway for explaining the .RecordCount thing with SELECT COUNT(*).
/Antisoft