Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Counting records in a table

Posted on 2003-03-18
12
Medium Priority
?
323 Views
Last Modified: 2012-05-04
I simply want to run a messagebox if there are no records in a table. I could only think to count the records and if the value was zero, then run the msgbox, but all attempts were unsuccessful. Any help would be much appreciated.
0
Comment
Question by:Richardofyork
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 1

Accepted Solution

by:
may_f_24 earned 100 total points
ID: 8158488
will you upload your attempts?
what is the Database you are useing and which language?

try this:
select count (*) as records_count
from table_name;

...
if (records_count == 0)
{
  //display the message box
}
0
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 100 total points
ID: 8158665
Counting records to see if any exist is extremely inefficient - you don't CARE how many there are, you just want to know if there is at least one.  For a large table, it might take the DBMS a while to count the records.

Something like this would be better:

record_exists = 0
SELECT 1 INTO record_exists  // FROM DUAL - if using Oracle
WHERE EXISTS (SELECT 1 FROM table_name);


0
 
LVL 6

Expert Comment

by:DrTech
ID: 8158694
Which database are you using?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:a_ro_no
ID: 8159012
if you are using ms access then:
create a button named checkForZeros
in the OnClickEvent
Private Sub checkForZeros_Click()

0
 
LVL 3

Expert Comment

by:a_ro_no
ID: 8159075
If you are using ms Access:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT yourField FROM yourTable")
rs.FindFirst "yourField = [whatever]"
rs.Close
set rs = Nothing
   
0
 
LVL 3

Expert Comment

by:a_ro_no
ID: 8159121
oh.... i forgot
place your messagebox after your findfirst
if rs.NoMatch = true
     MsgBox "Not found a zero Value"
else
     MsgBox "Empty Value in record " & rs.Absolutposition
0
 
LVL 34

Expert Comment

by:arbert
ID: 8159297
Don't use any of the Find methods (if were talking about access).

Simply do a SELECT TOP 1 * from TABLE

If not rs.eof or rs.bof then you have records--otherwise you don't.

Brett
0
 
LVL 34

Expert Comment

by:arbert
ID: 8718262
What's the status of this question?
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10342103
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: may_f_24 {http:#8158488}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0
 
LVL 15

Expert Comment

by:andrewst
ID: 10342130
I don't think counting all the records in a table to check if it is empty is at all efficient.  It could take minutes on a large table, when all that is needed is to find 1 record to prove that it isn't empty.
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10342689
No, you're right.  I had gone with the first answer that would work (even though it's inefficient)

Mod,
As this is a better way to do things, it wasn't first so I'll change my recommendation to
split points between may_f_24 and andrewst

Regards
Walt

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question