CRecordset fails to retrieve fileds when only one record is in the recordset .SOLVED ??
-
As suggested in Database forum, I am reposting this here. I am having an issue with retrieving fields from MFC CRecordset. I am using GetFieldValue( index, strText ); Works fine until the opened recordset contains initially only one record. Than I get the first field OK, but then I get an error saying basically that the field has already been retrieved. Here it the error: Error: GetFieldValue operation failed on field %d. Data already fetched for this field. It seem that the field index is not being advanced.I stepped thru the GetFieldValue, but cannot find the actuall source for the error routine. If I do MoveFirst on this one record record set , I get an error that I am attempting to move past the beginning of the recordset.That would be expected. I am using VC 6.0 MFC with standard CRecordset and Access database with ODBC. So far Google is no help. Any pointers would be greatly appreciated. Thanks for your help. Vaclav Addendum I am trying to use DoDataExchange to see if it behaves differently. I need recode to do that. SOLVED ??? Solved by checking for number of records and when it is 1 I add a new record. recordset->AddNew(); Funny part - checking the count again it counts 1 , not two! Kluge , but it works. Sure would like to know why it fails initially. Vaclav
-
As suggested in Database forum, I am reposting this here. I am having an issue with retrieving fields from MFC CRecordset. I am using GetFieldValue( index, strText ); Works fine until the opened recordset contains initially only one record. Than I get the first field OK, but then I get an error saying basically that the field has already been retrieved. Here it the error: Error: GetFieldValue operation failed on field %d. Data already fetched for this field. It seem that the field index is not being advanced.I stepped thru the GetFieldValue, but cannot find the actuall source for the error routine. If I do MoveFirst on this one record record set , I get an error that I am attempting to move past the beginning of the recordset.That would be expected. I am using VC 6.0 MFC with standard CRecordset and Access database with ODBC. So far Google is no help. Any pointers would be greatly appreciated. Thanks for your help. Vaclav Addendum I am trying to use DoDataExchange to see if it behaves differently. I need recode to do that. SOLVED ??? Solved by checking for number of records and when it is 1 I add a new record. recordset->AddNew(); Funny part - checking the count again it counts 1 , not two! Kluge , but it works. Sure would like to know why it fails initially. Vaclav
Vaclav_Sal wrote:
It seem that the field index is not being advanced.
There is nothing in the code you have shown where an attempt is made to advance the index value.
Unrequited desire is character building. OriginalGriff I'm sitting here giving you a standing ovation - Len Goodman
-
Vaclav_Sal wrote:
It seem that the field index is not being advanced.
There is nothing in the code you have shown where an attempt is made to advance the index value.
Unrequited desire is character building. OriginalGriff I'm sitting here giving you a standing ovation - Len Goodman
True, but I did state that the code works untill only one record is INITIALLY in the recordset. So here it is, mind that it is a code under construction. short nFields = pRecordset->GetODBCFieldCount( ); //pRecordset->MoveFirst( ); while( !pRecordset->IsEOF( ) ) { //pRecordset->DoFieldExchange(PDX); buffer = strText.GetBuffer(256); // insert dummy item to list if(!pList->InsertItem(iItem,buffer,NULL)) { // TRACE("\n buffer %s ",buffer); }; // set item text for( short index = 0; index < nFields; index++ ) { This is where it fails pRecordset->GetFieldValue( index, strText ); This is where it fails and the index is 1 during the failure if(!strText.CompareNoCase("48")) this is retruned with index = 0 TRACE("\n"); TRACE("\nstrText %s ", strText); buffer = strText.GetBuffer(256); pList->SetItemText(iItem,index,buffer); TRACE("\n item %i field %i buffer %s ",iItem , index , buffer ); strText =""; } pRecordset->MoveNext( ); iItem++; }
-
Vaclav_Sal wrote:
It seem that the field index is not being advanced.
There is nothing in the code you have shown where an attempt is made to advance the index value.
Unrequited desire is character building. OriginalGriff I'm sitting here giving you a standing ovation - Len Goodman
FYI found the reason - there is no data found! This is the routine reporting it. Observe the lack of correct code for nFieldIndex - 1! Now I need to find out why - the data is there! long PASCAL CRecordset::GetData(CDatabase* pdb, HSTMT hstmt, short nFieldIndex, short nFieldType, LPVOID pvData, int nLen, short nSQLType) { UNUSED(nSQLType); long nActualSize; RETCODE nRetCode; // Retrieve the column in question AFX_ODBC_CALL(::SQLGetData(hstmt, nFieldIndex, nFieldType, pvData, nLen, &nActualSize)); // Ignore data truncated warnings for long data if (nRetCode == SQL_SUCCESS_WITH_INFO) { #ifdef _DEBUG CDBException e(nRetCode); if (afxTraceFlags & traceDatabase) { CDBException e(nRetCode); // Build the error string but don't send nuisance output to TRACE window e.BuildErrorString(pdb, hstmt, FALSE); // If not a data truncated warning on long var column, // then send debug output if ((nSQLType != SQL_LONGVARCHAR && nSQLType != SQL_LONGVARBINARY) || (e.m_strStateNativeOrigin.Find(_afxDataTruncated) < 0)) { TRACE1("Warning: ODBC Success With Info on field %d.\n", nFieldIndex - 1); e.TraceErrorMessage(e.m_strError); e.TraceErrorMessage(e.m_strStateNativeOrigin); } } #endif // _DEBUG } else if (nRetCode == SQL_NO_DATA_FOUND) { NICE Mr Bill TRACE0("Error: GetFieldValue operation failed on field %d.\n"); TRACE1("\tData already fetched for this field.\n", nFieldIndex - 1); NICE Mr Bill AfxThrowDBException(nRetCode, pdb, hstmt); } else if (nRetCode != SQL_SUCCESS) { TRACE1("Error: GetFieldValue operation failed on field %d.\n", nFieldIndex - 1); AfxThrowDBException(nRetCode, pdb, hstmt); } return nActualSize; }
-
FYI found the reason - there is no data found! This is the routine reporting it. Observe the lack of correct code for nFieldIndex - 1! Now I need to find out why - the data is there! long PASCAL CRecordset::GetData(CDatabase* pdb, HSTMT hstmt, short nFieldIndex, short nFieldType, LPVOID pvData, int nLen, short nSQLType) { UNUSED(nSQLType); long nActualSize; RETCODE nRetCode; // Retrieve the column in question AFX_ODBC_CALL(::SQLGetData(hstmt, nFieldIndex, nFieldType, pvData, nLen, &nActualSize)); // Ignore data truncated warnings for long data if (nRetCode == SQL_SUCCESS_WITH_INFO) { #ifdef _DEBUG CDBException e(nRetCode); if (afxTraceFlags & traceDatabase) { CDBException e(nRetCode); // Build the error string but don't send nuisance output to TRACE window e.BuildErrorString(pdb, hstmt, FALSE); // If not a data truncated warning on long var column, // then send debug output if ((nSQLType != SQL_LONGVARCHAR && nSQLType != SQL_LONGVARBINARY) || (e.m_strStateNativeOrigin.Find(_afxDataTruncated) < 0)) { TRACE1("Warning: ODBC Success With Info on field %d.\n", nFieldIndex - 1); e.TraceErrorMessage(e.m_strError); e.TraceErrorMessage(e.m_strStateNativeOrigin); } } #endif // _DEBUG } else if (nRetCode == SQL_NO_DATA_FOUND) { NICE Mr Bill TRACE0("Error: GetFieldValue operation failed on field %d.\n"); TRACE1("\tData already fetched for this field.\n", nFieldIndex - 1); NICE Mr Bill AfxThrowDBException(nRetCode, pdb, hstmt); } else if (nRetCode != SQL_SUCCESS) { TRACE1("Error: GetFieldValue operation failed on field %d.\n", nFieldIndex - 1); AfxThrowDBException(nRetCode, pdb, hstmt); } return nActualSize; }
I have changed the CString in pRecordset->GetFieldValue( index, varValue ); to CDBVariant varValue. Same problem - only first field is retrieved. Identical record is retrieved when different SQl is used and more than one record are retrieved. So the data is there. The first field is AutoNumber in Access and the rest of fields are Text. I may have to insert dummy record to make this play.
-
As suggested in Database forum, I am reposting this here. I am having an issue with retrieving fields from MFC CRecordset. I am using GetFieldValue( index, strText ); Works fine until the opened recordset contains initially only one record. Than I get the first field OK, but then I get an error saying basically that the field has already been retrieved. Here it the error: Error: GetFieldValue operation failed on field %d. Data already fetched for this field. It seem that the field index is not being advanced.I stepped thru the GetFieldValue, but cannot find the actuall source for the error routine. If I do MoveFirst on this one record record set , I get an error that I am attempting to move past the beginning of the recordset.That would be expected. I am using VC 6.0 MFC with standard CRecordset and Access database with ODBC. So far Google is no help. Any pointers would be greatly appreciated. Thanks for your help. Vaclav Addendum I am trying to use DoDataExchange to see if it behaves differently. I need recode to do that. SOLVED ??? Solved by checking for number of records and when it is 1 I add a new record. recordset->AddNew(); Funny part - checking the count again it counts 1 , not two! Kluge , but it works. Sure would like to know why it fails initially. Vaclav
Vaclav_Sal wrote:
It seem that the field index is not being advanced.I stepped thru the GetFieldValue,
but cannot find the actuall source for the error routine.Have you looked at
CRecordset::GetData()indbcore.cpp?Vaclav_Sal wrote:
If I do MoveFirst on this one record record set , I get an error that I am attempting to move past the beginning of the recordset.That would be expected.
So do you need to call
IsBOF()first?"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
-
Vaclav_Sal wrote:
It seem that the field index is not being advanced.I stepped thru the GetFieldValue,
but cannot find the actuall source for the error routine.Have you looked at
CRecordset::GetData()indbcore.cpp?Vaclav_Sal wrote:
If I do MoveFirst on this one record record set , I get an error that I am attempting to move past the beginning of the recordset.That would be expected.
So do you need to call
IsBOF()first?"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
-
No, I just did it to test stuff. Actually, the "solution" is to do AddNew to any recordset without doing any checking if there is only one record in recordset.Real hack.
Vaclav_Sal wrote:
No, I just did it to test stuff.
Did what?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
-
Vaclav_Sal wrote:
No, I just did it to test stuff.
Did what?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
-
True, but I did state that the code works untill only one record is INITIALLY in the recordset. So here it is, mind that it is a code under construction. short nFields = pRecordset->GetODBCFieldCount( ); //pRecordset->MoveFirst( ); while( !pRecordset->IsEOF( ) ) { //pRecordset->DoFieldExchange(PDX); buffer = strText.GetBuffer(256); // insert dummy item to list if(!pList->InsertItem(iItem,buffer,NULL)) { // TRACE("\n buffer %s ",buffer); }; // set item text for( short index = 0; index < nFields; index++ ) { This is where it fails pRecordset->GetFieldValue( index, strText ); This is where it fails and the index is 1 during the failure if(!strText.CompareNoCase("48")) this is retruned with index = 0 TRACE("\n"); TRACE("\nstrText %s ", strText); buffer = strText.GetBuffer(256); pList->SetItemText(iItem,index,buffer); TRACE("\n item %i field %i buffer %s ",iItem , index , buffer ); strText =""; } pRecordset->MoveNext( ); iItem++; }
Just so I understand what is going on I am going to put comments in the code. Tell me if I am wrong. And this will work until you get to the last row or does it fail at the first row?
/**
* This starts the master loop for all rows of included recordset
*/
while( !pRecordset->IsEOF( ) )
{
//pRecordset->DoFieldExchange(PDX);
buffer = strText.GetBuffer(256);
// insert dummy item to list
if(!pList->InsertItem(iItem,buffer,NULL))
{
// TRACE("\n buffer %s ",buffer);
};// set item text /\*\* \* This will loop through row in recordset and get its value \*/ for( short index = 0; index < nFields; index++ ) {This is where it fails
pRecordset->GetFieldValue( index, strText );
This is where it fails and the index is 1 during the failureif(!strText.CompareNoCase("48")) this is retruned with index = 0 TRACE("\\n"); TRACE("\\nstrText %s ", strText); buffer = strText.GetBuffer(256); pList->SetItemText(iItem,index,buffer); TRACE("\\n item %i field %i buffer %s ",iItem , index , buffer ); strText =""; } /\*\* \* This moves down the row \*/ pRecordset->MoveNext( ); iItem++; } -
Just so I understand what is going on I am going to put comments in the code. Tell me if I am wrong. And this will work until you get to the last row or does it fail at the first row?
/**
* This starts the master loop for all rows of included recordset
*/
while( !pRecordset->IsEOF( ) )
{
//pRecordset->DoFieldExchange(PDX);
buffer = strText.GetBuffer(256);
// insert dummy item to list
if(!pList->InsertItem(iItem,buffer,NULL))
{
// TRACE("\n buffer %s ",buffer);
};// set item text /\*\* \* This will loop through row in recordset and get its value \*/ for( short index = 0; index < nFields; index++ ) {This is where it fails
pRecordset->GetFieldValue( index, strText );
This is where it fails and the index is 1 during the failureif(!strText.CompareNoCase("48")) this is retruned with index = 0 TRACE("\\n"); TRACE("\\nstrText %s ", strText); buffer = strText.GetBuffer(256); pList->SetItemText(iItem,index,buffer); TRACE("\\n item %i field %i buffer %s ",iItem , index , buffer ); strText =""; } /\*\* \* This moves down the row \*/ pRecordset->MoveNext( ); iItem++; }It fails when the recordset has only one record / row after it is first open. It works fine when the recordset has more than one record / rows and it will retrieve them all. I cannot run it now because it is a part of other code. But if you look at the MFC thread you will note where exactly it reports the trouble. Basically it does not see the data, but the error message is misleading due to silly MS coding error ( wrong / missing paramaters in TRACE). Sorry, some of the stuff I am reffering to is in this thread. Your comments are OK. Keep in mind it only fails as I mentioned above. The loops are OK.