[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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

0
rcg112355
Asked:
rcg112355
  • 2
  • 2
1 Solution
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now