?
Solved

Counting records in a table

Posted on 2003-03-18
12
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
12 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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 Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 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