asked on

Quick and easy way to know if SQL table has any records?

A form in my vb.net program needs to know if the LocInv table has any records. I have been using this code:

sSQL = "SELECT COUNT(*) FROM LocInv" 'check to see if this file is in use...
sSQLresult = RunSQL_Select(sSQL, True)
If Val(sSQLresult) > 0 Then
pbC1_UseInv = True
Else
pbC1_UseInv = False
End If

The problem is that LocInv has almost 100,000 records, and that is taking a few seconds to 'count' on my (slow) portable device. So I need to know if there is an easy way to code this to more quickly determine whether or not the LocInv table has any records in it--without having to do a 'count' command. Note that this is the compact framework environment, meaning I have a limited number of commands available to me. TIA

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sasllc

ASKER

When I tried to use these in my program I got numerous syntax errors. I'm guessing that it is due to a combination of this program being developed in the compact framework environment with its numerous restrictions, plus my lack of knowledge.

But it occurred to me that I might be able to try and read the first record of the file to see if I was already at the end of file--meaning it is empty. This code worked for me:

sSQL = "SELECT Bale FROM LocInv"

cmd = New SqlCeCommand(sSQL, cn)
cmd.CommandType = CommandType.Text
dtr = cmd.ExecuteReader(CommandBehavior.Default)

CkEOF = dtr.Read()
If CkEOF = False Then 'if false, then there are no more records to read...
pbC1_UseInv = False
Else
pbC1_UseInv = True
End If