Just A Very Simple Listing Problem.

Gah, can't find it anywhere... :/

Page 1 of 1

11 Replies - 2223 Views - Last Post: 08 January 2003 - 12:50 PM Rate Topic: -----

#1 Antisoft  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 71
  • Joined: 20-June 02

Just A Very Simple Listing Problem.

Posted 04 November 2002 - 12:12 PM

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 :D

Thanks in advance...
/Antisoft

Is This A Good Question/Topic? 0
  • +

Replies To: Just A Very Simple Listing Problem.

#2 SlashRaid  Icon User is offline

  • Dream.In.Force

Reputation: 1
  • View blog
  • Posts: 2,421
  • Joined: 21-January 02

Re: Just A Very Simple Listing Problem.

Posted 04 November 2002 - 12:24 PM

What about RecordCount?

[edit]:Putting ASP to Work ~ Web Savant[/edit]
Was This Post Helpful? 0
  • +
  • -

#3 Antisoft  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 71
  • Joined: 20-June 02

Re: Just A Very Simple Listing Problem.

Posted 04 November 2002 - 01:55 PM

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.

Was This Post Helpful? 0
  • +
  • -

#4 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: Just A Very Simple Listing Problem.

Posted 04 November 2002 - 05:03 PM

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 :)
Was This Post Helpful? 0
  • +
  • -

#5 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Just A Very Simple Listing Problem.

Posted 04 November 2002 - 07:33 PM

Yeah, for sure it's the cursor type. I'm sure either adOpenForwardOnly or Dynamic will allow .Recordcount to be supported.
Was This Post Helpful? 0
  • +
  • -

#6 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: Just A Very Simple Listing Problem.

Posted 04 November 2002 - 09:59 PM

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. :)
Was This Post Helpful? 0
  • +
  • -

#7 Antisoft  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 71
  • Joined: 20-June 02

Re: Just A Very Simple Listing Problem.

Posted 05 November 2002 - 08:17 AM

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
Was This Post Helpful? 0
  • +
  • -

#8 Antisoft  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 71
  • Joined: 20-June 02

Re: Just A Very Simple Listing Problem.

Posted 28 November 2002 - 10:34 AM

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

Would be great!
Thx!
Was This Post Helpful? 0
  • +
  • -

#9 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Just A Very Simple Listing Problem.

Posted 28 November 2002 - 03:07 PM

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"
Was This Post Helpful? 0
  • +
  • -

#10 Antisoft  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 71
  • Joined: 20-June 02

Re: Just A Very Simple Listing Problem.

Posted 28 November 2002 - 04:28 PM

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!
Was This Post Helpful? 0
  • +
  • -

#11 Garstor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 09-October 02

Re: Just A Very Simple Listing Problem.

Posted 07 January 2003 - 01:04 PM

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? :o )

Regards,
Was This Post Helpful? 0
  • +
  • -

#12 Antisoft  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 71
  • Joined: 20-June 02

Re: Just A Very Simple Listing Problem.

Posted 08 January 2003 - 12:50 PM

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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1