Using CDatabase to retrieve the number of records available...

Hi,

I have an application that uses CDatabase and CRecordset.

I connect to the database using  
   db.Open(.....)

And send the SQL query using
   CRecordset rs(&db);
   rs.Open("SELECT * FROM DATA WHERE SERIALNO='123';");

Now, I can use...
   for( ; ! rs.IsEOF(); rs.MoveNext() )
   {
      .....
   }

To retrieve the data, but is it possible to determine how many records have been generated for the specified query?

GetRecordCount() appears to get only the number of records that have been accessed?

Also, if there are no records to view, I can use CDatabase::ExecuteSQL(...) but assuming the query was an update, how do I find out how many records have been affected???

Thanks for any help,

James
LVL 4
James AtkinSenior Principle Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pete LongTechnical ConsultantCommented:
Hi gsazeides,
Outlook Is Blocking Attachments

Go to the menu Tools > Options and click on the Security tab then uncheck the box
"Do not allow attachments to be saved or opened that could potentially be a virus"

If that Dosnt Work...

Quit Outlook 2000 if it is running.
Click Start, and then click Run.
In the Open box, type regedit, and then click OK.
Verify that the following key exists. If it does, go to step 5.
HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Outlook\Security

If the key path does not exist, create the key path. To create the key path, locate and then select the following registry key:
HKEY_CURRENT_USER\Software\Microsoft

Click the Edit menu, click New, and then click Key.
Type Office, and then press the ENTER key.
Click the Edit menu, click New, and then click Key.
Type 9.0, and then press the ENTER key.
Click the Edit menu, click New, and then click Key.
Type Outlook, and then press the ENTER key.
Click the Edit menu, click New, and then click Key.
Type Security, and then press the ENTER key.
Click the Edit menu, click New, and then click String Value.
Type the following name for the new value:Level1Remove


Press the ENTER key.
Right-click the new string value name, and then click Modify.
Type the extension of the file type that you want to allow access to from Outlook 2000 as follows:.exe

To specify multiple file types, use the following format:

.exe; .com
When you are finished, click OK.
Exit Registry Editor.
Restart your computer.

*****Links*****

Why does it block and what is level1 and level2?
http://www.outlookexchange.com/articles/CherryBeado/malware.asp

Outlook 2000Sp3, Outlook2002, Outlook2003
http://www.slipstick.com/outlook/esecup/getexe.htm

This will do it for you in XP
http://www.devhood.com/tools/tool_details.aspx?tool_id=89

Cannot Open E-Mail Attachments in Outlook Express After You Install SP1:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q329570 

OLEXP: Using Virus Protection Features in Outlook Express 6:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;291387


*****Blocked files are*****

The 38 file types automatically blocked by Outlook are as follows:

.ade      Microsoft Access project extension
.adp     Microsoft Access project
.bas      Microsoft Visual Basic class module
.bat      Batch file
.chm     Compiled HTML Help file
.cmd     Microsoft Windows NT Command Script
.com     Microsoft MS-DOS program
.cpl       Control Panel extension
.crt       Security certificate
.exe      Program
.hlp       Help file
.hta       HTML program
.inf        Setup  Information
.ins       Internet Naming Service
.isp       Internet Communication settings
.js         JScript file
.jse       Jscript Encoded Script file
.lnk       Shortcut
.mda     Microsoft Access add-in program
.mdb    Microsoft Access program
.mde     Microsoft Access MDE database
.mdz     Microsoft Access wizard program
.msc     Microsoft Common Console Document
.msi      Microsoft Windows Installer package
.msp     Windows Installer patch
.mst      Visual Test source files
.pcd     Photo CD image or Microsoft Visual Test compiled script
.pif       Shortcut to MS-DOS program
.reg      Registration entries
.scr       Screen saver
.sct       Windows Script Component
.shs      Shell Scrap Object
.url       Internet shortcut
.vb       VBScript file
.vbe      VBScript Encoded Script file
.vbs      VBScript file
.wsc     Windows Script Component
.wsf      Windows Script file
.wsh     Windows Script Host Settings file

PeteL
0
Pete LongTechnical ConsultantCommented:
wrong Q sorry

PL
0
AndyAinscowFreelance programmer / ConsultantCommented:
Does this work?

rs.MoveLast();
int iNumRecs = rs.GetRecordCount();
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

James AtkinSenior Principle Software EngineerAuthor Commented:
Tried that, and I still get 1 as a response (the query returns 5)
0
Roshan DavisCommented:
What abt this

CRecordset rs(&db);
rs.Open("SELECT COUNT(*) FROM DATA WHERE SERIALNO='123';");

Rosh :)
0
AndyAinscowFreelance programmer / ConsultantCommented:
Are you getting the recordset after the update query?
If you do loop through
int i = 0;
rs.MoveFirst();
while(!rs.IsEOF())
{
  rs.MoveNext();
  i++;
}

does i now equal the GetRecordCount() ?
0
James AtkinSenior Principle Software EngineerAuthor Commented:
Andy,  that is the way I currently do it - and use MoveFirst to get back to the 1st record.
Problem is that with a big query, this can use an excessive amount of time...

Roshmon, interesting idea - I assume by both these responses, the CRecordset/CDatabase does not hold this information so it is not directly available?
0
Roshan DavisCommented:
Yes,

MSDN Says

Caution   The record count is maintained as a “high water mark” — the highest-numbered record yet seen as the user moves through the records. The total number of records is only known after the user has moved beyond the last record. For performance reasons, the count is not updated when you call MoveLast. To count the records yourself, call MoveNext repeatedly until IsEOF returns nonzero. Adding a record via CRecordset:AddNew and Update increases the count; deleting a record via CRecordset::Delete decreases the count.

Rosh :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndyAinscowFreelance programmer / ConsultantCommented:
Roshmons idea with the count function is probably best for you - it should be more efficient than a MoveFirst, MoveNext loop
0
James AtkinSenior Principle Software EngineerAuthor Commented:
One final thing...

Do you know if it is possible to find out how many rows have been affected using the ExecuteSQL function of CDatabase?
I guess the answer is no, but I thought I'd ask...
0
James AtkinSenior Principle Software EngineerAuthor Commented:
Never mind...

Created the following function to do it.  I call this before and after the INSERT or DELETE calls to verify (with a reasonable amount of certainty) that the record has been added/deleted...

long CMyClass::GetTotalNoOfRecordsInTable(CDatabase &TheDatabase, CString TableName)
{            
   CRecordset rs(&TheDatabase);
   CString SQLQuery;
   SQLQuery.Format("SELECT COUNT(*) FROM %s;", TableName);
   rs.Open(SQLQuery);
   long RecordCount = atol((CString)rs.Field("COUNT(*)"));
   return RecordCount;
}

Appears to do what I am after - although I do think Microsoft need a rocket placed somewhere obscure to have functionality such as this missing from the MFC!!!

Thanks again for the help,

James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.