ODBC Database Queries

[fdsouth]

I've created an application that queries an ODBC database using a class named CConnect. I used the Visual Studio's app wizard to create all the necessary classes so it set up the standard document/view classes. Everything looks fine (I connect to the database ok, the default sql statement runs and returns the entire dataset).

The problem is this. I want to put a data field to get user-input on what to select from the database. In the CView class I created a button that reads from the field, and updates the m_strFilter variable. This should append to the default SQL string and filter out extra data from the initial database connection. When I press the query button, nothing happens. Since the m_strFilter variable is being appended in another class, how do I requery (m_pSet->Requery() ?) and then reload the screen to show the updated results?

 

[vistor]

There are more than one way to approach your query. One
suggested way would be to use a recordsets and DDX controls.
You could additionally use storded procedures

In uses a DDX control (Shown below) when it is setup you can specify the parameters to be used when calling a query or a stored procedure.


CDBTRY3Set::CDBTRY3Set(CDatabase* pdb)
: CRecordset(pdb)
{
//{{AFX_FIELD_INITRECORDSET_NAME)
m_ZIP = _T(""
m_CITY = _T(""
m_STATE = _T(""
m_LAT = _T(""
m_LNG = _T(""
m_nFields = 5;
//}}AFX_FIELD_INIT
m_nDefaultType = snapshot;
m_parm1 = _T(" "
m_nParams = 1; // set for parameter
}

CString CDBTRY3Set::GetDefaultConnect()
{
return _T ODBC;DSN=MSSQLSRVR;UID=XX;PWD=XXXXX;"
}

CString CDBTRY3Set::GetDefaultSQL()
{
// * Call a stored procedure to return a result set *
return _T("{CALL usp_statesrlh ('?')}"
// *

// * Call a result set from a query
return _T("{Select DISTINCT(STATE) from ZIPCODES
ORDER BY STATE ASC}"
// *
}

void CDBTRY3Set:oFieldExchange(CFieldExchange* pFX)
{
pFX->SetFieldType(CFieldExchange:aram);
RFX_Text(pFX, "123",m_parm1);
//{{AFX_FIELD_MAP(CDBTRY3Set)
pFX->SetFieldType(CFieldExchange:utputColumn);
RFX_Text(pFX, _T("[CITY]&quot, m_CITY);
RFX_Text(pFX, _T("[STATE]&quot, m_STATE);
RFX_Text(pFX, _T("[ZIP]&quot, m_ZIP);
RFX_Text(pFX, _T("[LAT]&quot, m_LAT);
RFX_Text(pFX, _T("[LNG]&quot, m_LNG);

//}}AFX_FIELD_MAP

}


Once you hve completed the DDX control setup the system
knows what controls to place the information into. Additionally, you could set up a variable that could be loaded from where and passed to the DDX parameter to execute the query.

 

[fdsouth]

I have already done what you propose. This appears to work fine initially. The problem arises when I try to requery based on a value entered in a text box (and stored as m_parm1). I have setup a requery button in the application's view class that adds the m_parm1 value as a WHERE statment to the default SELECT statement (GetDefaultSQL function). When I press the button, nothing happens. Here's my requery function:

void CMAX_Lot_NumberView::OnQuery() 
{
	m_pSet->Close();
	m_pSet->m_strFilter = "[LOTNUM_70] = '" + m_pSet->m_parm1 + "'";
	m_pSet->Open();
	m_pSet->Requery();
}

What's missing? Do I need to run the DoDataExchange function again to update the screen?