C++ ODBC Update does not work

I am migrating a program written using MFC and C++ to use ODBC instead of OLEDB. There is some code that edits data from a Microsoft Access database and I have migrated that to SQL and have edited the code to use CRecordset instead of CDaoRecordset. However, when I call the update function nothing happens. No exceptions are thrown, it simply does not perform the update. The return status of the update function is always 'false'.

I am a novice C++ programmer, so please be gentle.

Could someone please show me what I am missing?
int CdbFunctions::UpdateColorLimits(int	arg_local, CString TagName, BOOL ColorCoded, double UpperRed, double LowerRed, double UpperYellow, double LowerYellow)
{
	//CDaoDatabase dbHMI ;					// Database object to open DB
	CDatabase dbHMI ;					// Database object to open DB Changed to use SQL 11/07/2008
	//CVarInfoSet dbVarInfo ;					// VarInfo Table
	CVarInfo dbVarInfo(&dbHMI) ;					// VarInfo Table Changed to use SQL 11/10/2008
	char SQLText[256] ;						// SQL String
	int ret_status = 1 ;					// initialize ret status to success
	bool boolUpdateStatus = 0;	
 
 
	try
	{
		//***********************************************************************
		// open the database
 
		CHMIApp* MyApp = (CHMIApp*)AfxGetApp() ;
		if( arg_local )
			//dbHMI.Open( MyApp->m_strDbPath, FALSE, FALSE ) ;
 
			//***********************************************************
			// Changed to use SQL server 09/26/2008
			dbHMI.Open(NULL, FALSE, FALSE, _T("Driver={SQL Native Client};Server=hbffs1;Database=hminfo;Uid=root;Pwd=omittedintentionally"));
			//***********************************************************
 
		else
		{
			// Change the database to point to master
			//dbVarInfo.SetDatabaseName( MyApp->m_strMstrDbPath ) ;
			//dbHMI.Open( MyApp->m_strMstrDbPath, FALSE, FALSE ) ;
 
			//***********************************************************
			// Changed to use SQL server 09/26/2008
			dbHMI.Open(NULL, FALSE, FALSE, _T("Driver={SQL Native Client};Server=hbffs1;Database=hminfo;Uid=root;Pwd=omittedintentionally"));
			//***********************************************************
 
		}
	}
	catch( CDBException *e  )
	{
		TimedMessage(e->m_strError );	
		ret_status = 0 ;
		return ret_status ;
	}
 
	try
	{
		//***********************************************************************
		// set up SQL statement to select the record for this optioncode
		
		TagName.Replace("'", "''");
		sprintf( SQLText, "Select * from hbffs1.hminfo.dbo.VarInfo WHERE TagName = \'%s\'", TagName ) ;
 
		//***********************************************************************
		// create a dynaset record - perform the query and get into our variable
		dbVarInfo.Open(dbOpenDynaset, SQLText, dbConsistent);
	}
	catch( CDBException *e  )
	{
		TimedMessage(e->m_strError );	
		dbHMI.Close() ;
		ret_status = 0 ;
		return ret_status ;
	}
 
	try
	{
		//***********************************************************************
		// if the ISBOF function isn't zero - we got an empty recordset, so 
		// return an error code
 
		if ( dbVarInfo.IsBOF() != 0 )
		{
			dbVarInfo.Close() ;
			dbHMI.Close() ;
			return -1;
		}
 
		dbVarInfo.Edit() ;
		dbVarInfo.m_ColorCoded = ColorCoded ;
		dbVarInfo.m_UpperRedLimit = UpperRed ;
		dbVarInfo.m_LowerRedLimit = LowerRed ;
		dbVarInfo.m_UpperYellowLimit = UpperYellow ;
		dbVarInfo.m_LowerYellowLimit = LowerYellow ;
		boolUpdateStatus = dbVarInfo.Update() ;
 
	}
	catch( CDBException *e  )
	{
		TimedMessage(e->m_strError );	
		ret_status = 0 ;
	}
 
	//***********************************************************************
	// close the dynasets
	
	dbVarInfo.Close() ;
 
	//***********************************************************************
	// close the database
 
	dbHMI.Close() ;
 
	return ret_status ;
 
} //UpdateColorLimits

Open in new window

rcg112355Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

itsmeandnobodyelseCommented:
Is the CVarInfo derived from CDaoRecordset?

Did you bind the columns of the info table to the CVarInfo class?

If no you should use the wizard to do that, cause it is much work to do that manually.
0
rcg112355Author Commented:
I did use the wizard to create CVarInfo. It is derived from CRecordset.

Here is the code snippet that the wizard generated.
// VarInfo.h : Implementation of the CVarInfo class
 
 
 
// CVarInfo implementation
 
// code generated on Monday, November 10, 2008, 8:45 AM
 
#include "stdafx.h"
#include "VarInfo.h"
IMPLEMENT_DYNAMIC(CVarInfo, CRecordset)
 
CVarInfo::CVarInfo(CDatabase* pdb)
	: CRecordset(pdb)
{
	m_TagName = L"";
	m_DataType = L"";
	m_KeyType = 0;
	m_EngUnits = L"";
	m_MinValue = 0.0;
	m_MaxValue = 0.0;
	m_ColorCoded = FALSE;
	m_UpperRedLimit = 0.0;
	m_UpperYellowLimit = 0.0;
	m_LowerRedLimit = 0.0;
	m_LowerYellowLimit = 0.0;
	m_CalculatedVariable = 0;
	m_L1TagName = L"";
	m_FormatFile = L"";
	m_Title = L"";
	m_Head1 = L"";
	m_Head2 = L"";
	m_Head3 = L"";
	m_Head4 = L"";
	m_MatCode = 0;
	m_ProcType = 0;
	m_NumRecs = 0;
	m_AVGFlag = FALSE;
	m_YInterval = 0.0;
	m_PlotColor = 0;
	m_BGColor = 0;
	m_LineType = 0;
	m_UpCntlAvg = 0.0;
	m_ExpCntlAvg = 0.0;
	m_LowCntlAvg = 0.0;
	m_UpCntlRange = 0.0;
	m_ExpCntlRange = 0.0;
	m_LowCntlRange = 0.0;
	m_DataFormat = L"";
//	m_SSMA_TimeStamp;
//	m_nFields = 35;
	m_nFields = 34;
	m_nDefaultType = dynaset;
}
//#error Security Issue: The connection string may contain a password
// The connection string below may contain plain text passwords and/or
// other sensitive information. Please remove the #error after reviewing
// the connection string for any security related issues. You may want to
// store the password in some other form or use a different user authentication.
CString CVarInfo::GetDefaultConnect()
{
	return _T("DRIVER=SQL Server;SERVER=hbffs1;UID=root;PWD=root1;DATABASE=hminfo");
}
 
CString CVarInfo::GetDefaultSQL()
{
	return _T("[dbo].[VarInfo]");
}
 
void CVarInfo::DoFieldExchange(CFieldExchange* pFX)
{
	pFX->SetFieldType(CFieldExchange::outputColumn);
// Macros such as RFX_Text() and RFX_Int() are dependent on the
// type of the member variable, not the type of the field in the database.
// ODBC will try to automatically convert the column value to the requested type
	RFX_Text(pFX, _T("[TagName]"), m_TagName);
	RFX_Text(pFX, _T("[DataType]"), m_DataType);
	RFX_Int(pFX, _T("[KeyType]"), m_KeyType);
	RFX_Text(pFX, _T("[EngUnits]"), m_EngUnits);
	RFX_Single(pFX, _T("[MinValue]"), m_MinValue);
	RFX_Single(pFX, _T("[MaxValue]"), m_MaxValue);
	RFX_Bool(pFX, _T("[ColorCoded]"), m_ColorCoded);
	RFX_Single(pFX, _T("[UpperRedLimit]"), m_UpperRedLimit);
	RFX_Single(pFX, _T("[UpperYellowLimit]"), m_UpperYellowLimit);
	RFX_Single(pFX, _T("[LowerRedLimit]"), m_LowerRedLimit);
	RFX_Single(pFX, _T("[LowerYellowLimit]"), m_LowerYellowLimit);
	RFX_Int(pFX, _T("[CalculatedVariable]"), m_CalculatedVariable);
	RFX_Text(pFX, _T("[L1TagName]"), m_L1TagName);
	RFX_Text(pFX, _T("[FormatFile]"), m_FormatFile);
	RFX_Text(pFX, _T("[Title]"), m_Title);
	RFX_Text(pFX, _T("[Head1]"), m_Head1);
	RFX_Text(pFX, _T("[Head2]"), m_Head2);
	RFX_Text(pFX, _T("[Head3]"), m_Head3);
	RFX_Text(pFX, _T("[Head4]"), m_Head4);
	RFX_Int(pFX, _T("[MatCode]"), m_MatCode);
	RFX_Int(pFX, _T("[ProcType]"), m_ProcType);
	RFX_Int(pFX, _T("[NumRecs]"), m_NumRecs);
	RFX_Bool(pFX, _T("[AVGFlag]"), m_AVGFlag);
	RFX_Single(pFX, _T("[YInterval]"), m_YInterval);
	RFX_Long(pFX, _T("[PlotColor]"), m_PlotColor);
	RFX_Long(pFX, _T("[BGColor]"), m_BGColor);
	RFX_Long(pFX, _T("[LineType]"), m_LineType);
	RFX_Single(pFX, _T("[UpCntlAvg]"), m_UpCntlAvg);
	RFX_Single(pFX, _T("[ExpCntlAvg]"), m_ExpCntlAvg);
	RFX_Single(pFX, _T("[LowCntlAvg]"), m_LowCntlAvg);
	RFX_Single(pFX, _T("[UpCntlRange]"), m_UpCntlRange);
	RFX_Single(pFX, _T("[ExpCntlRange]"), m_ExpCntlRange);
	RFX_Single(pFX, _T("[LowCntlRange]"), m_LowCntlRange);
	RFX_Text(pFX, _T("[DataFormat]"), m_DataFormat);
//	RFX_Binary(pFX, _T("[SSMA_TimeStamp]"), m_SSMA_TimeStamp);
 
}
/////////////////////////////////////////////////////////////////////////////
// CVarInfo diagnostics
 
#ifdef _DEBUG
void CVarInfo::AssertValid() const
{
	CRecordset::AssertValid();
}
 
void CVarInfo::Dump(CDumpContext& dc) const
{
	CRecordset::Dump(dc);
}
#endif //_DEBUG

Open in new window

0
rcg112355Author Commented:
By the way, I just manually commented out the m_SSMA_TimeStamp column as a troubleshooting exercise.
0
itsmeandnobodyelseCommented:
You did not check the return of the 'Open'. Note, there are different CRecordset::Open. I always used the one which only passed the type of recordset. The where clause and sort clause I set by assigning to the m_strFilter and m_strSort members of CRecordset. I never used a 'select *' in the Open statement. IMO, that could not work as your CRecordset derived class already was well-defined so that the baseclass could build a well-formed select statement with all columns bound in the correct way. By passing 'select *' you turn that down and the columns now were unbound.

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
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.