MFC : searching a database for the second time

[da_cobra]

Hello,

Let me first explain what I'm trying to do :
I have a main dialogbox with a list of all the ppl in my database. When I click on 1 of them, their address appears in a textbox beneath it.

How am I trying to do it :
First I make sure that the ID of that person is in the List control.
When I click on that person I retrieve the ID from the Listcontrol.
Then I pass that ID onto a function that searches the database for that ID, like this :

CString strFilter  = "SELECT * FROM Personen WHERE ID LIKE '" ;
strFilter += strID  ;
strFilter += "'" ;		
	
m_pDB->Close() ;
m_pDB->Open(AFX_DB_USE_DEFAULT_TYPE, strFilter) ;

after that I just retrieve the address from the record that I found.

Problem :
When I click the first time on a person, everything works. But then when I click on a different person, my textbox stays the same. (I think the record just stays the same). I already tried a :

CString strFilter  = "SELECT * FROM Personen" ;

m_pDB->Close() ;
m_pDB->Open(AFX_DB_USE_DEFAULT_TYPE, strFilter) ;

What am I still missing here?

thanx in advance for any help!

[GCDEF]

Is m_pDB a databse?

I assume this is ODBC. You should be using CRecordsets, and their members m_strFilter and Requery. Opening and closing a database over and over is a very inefficent way of going about things.

[sudheeratwork]

refresh your recordset with Requery
or
could it be likely that u r passing the same strID all the time....

[da_cobra]

m_pDB is a pointer to a CRecordset yes
and it is ODBC

I already tried a Requery AND I'm sure that I don't pass the same strID too

[Bob Sheep]

You should be using m_strFilter not your own CString object called strFilter.

[da_cobra]

what exactly do you mean? Is there somewhere a good example on how to use a SQL query on the net?

[GCDEF]

what exactly do you mean? Is there somewhere a good example on how to use a SQL query on the net?

Read the CRecordset documentation in MSDN. It explains it pretty well.

[da_cobra]

I just read the MSDN and came up with this solution (but the same problem again )

HRESULT CPersonen::ZoekID(int nID)
{
	CString strID ;						// needed to convert the ID

	m_pDB->m_strFilter = "" ;			// reset filter

	if (!m_pDB->CanRestart())
		return S_FALSE ;				// Unable to requery
	else
		if (!m_pDB->Requery())
			return S_FALSE ;			// requery failed
		else
			{
	
				strID.Format("%d", nID) ;			// convert ID to CString

				CString strFilter  = "ID = " ;
				strFilter += strID  ;
		
				m_pDB->m_strFilter = strFilter ;
	
				MessageBox(NULL, m_pDB->m_strFilter, "Filter", MB_OK) ;

				if (!m_pDB->CanRestart())
					return S_FALSE ;				// Unable to requery
				else
					if (!m_pDB->Requery())
						return S_FALSE ;			// requery failed
					else
						return S_OK ;
			}
}

this is the function that I use to search for an ID :
First I set the strFilter to "", so I get all my records.
Then I set the strFilter to "ID = number"

Now when I click the first time on a name I call this function and a message box appears with the strFilter which says "ID = 1" (everything correct!)
BUT when I check the current record, it's ID is -842150451!!!
Anyway, now when I press a button "more..." I get a dialog box with more info on that person, when I click OK on that dialog box I return to my main dialog box (with the textbox with the address in it)
Now when I select another person, the correct address is showed again, when I select another person, it stays the same address again (untill I open/close that 2nd dialogbox)

so to make a long story short
I pass on the right filter! But the same record is shown (or the very first time, a totally wrong record is shown (ID = -842150451))
I hope my explanation makes sense...

[GCDEF]

Why are you doing the initial Requery with m_strFilter = "" but not doing anything with the results? That call accomplishes nothing but to waste time.

What are you looking at to determine the current record's ID?

[da_cobra]

Correct me if I do not understand exactly what happens, but if I do a requery on a recordset with a filter then I only get those records with comply to that filter right?
so I first need to "get" all the records again (with m_strFilter="") before I can search for a given record, or do I have this all wrong?!?

I reply later on how I get the Id from the current record, because I found something which is not good for me

edit : I found the solution to the first problem of this thread!
I still have a question left (besides the one above)
if I want to show all names who start with an "a" for example, why doesn't this work :

m_strFilter = "name = 'a%'"

[GCDEF]

Correct me if I do not understand exactly what happens, but if I do a requery on a recordset with a filter then I only get those records with comply to that filter right?
so I first need to "get" all the records again (with m_strFilter="") before I can search for a given record, or do I have this all wrong?!?

I reply later on how I get the Id from the current record, because I found something which is not good for me

edit : I found the solution to the first problem of this thread!

You have it all wrong. There's no need to do the first query.

What was the problem?

[da_cobra]

It was a stupid mistake from my side.
I have to write an application with a new class for the database.
when I create that class for the first time I pass a pointer of the recordset to this class and I load all the fields into the member variables of that class.
But each time I searched for an ID, I didn't reload the fields in my class. A stupid mistake on which I searched a whole week for

Can you still answer that last question on how to search for names who begin with an "a" for example?

m_strFilter = "name = 'a%'" doesn't work
and what if I want to work with numbers and "OR" like this :
strFilter = "Naam = '1%' OR Naam = '2%' OR Naam = '3%' " ;
strFilter += "OR Naam = '4%' OR Naam = '5%' OR Naam = '6%' OR Naam = '7%' " ;
strFilter += "OR Naam = '8%' OR Naam = '9%' OR Naam = '0%'" ;


edit : oh, before I forget, thx for helping me, you put me on the right track by asking how I got the ID from the recordset (the first time I got it fromm the class, which wasn't updated yet)

[GCDEF]

Change = to LIKE

It still doesn't seem like you're using the CRecordset correctly. Do you have member variables set up and RFX data exchange mechanisms in place?

[da_cobra]

it works now!

thx alot


What do you mean by : "Do you have member variables set up and RFX data exchange mechanisms in place?"

In my self made class I have some member variables like : m_strName and I import the field Name through the passed on Recordset (m_pSet)

so :
1) I create my class : CPersons database ;
2) I pass on the recordset : database.GetRecordset(m_pSet)
3) everytime I change a record I reload the member variables with the fields from the recordset : m_strName = m_pSet->strName

[GCDEF]

What you're doing sounds very nonstandard. If you use class wizard to create a CRecordset, it'll give you member variables corresponding to the database elements. They're automatically updated for you through RFX (Recordset Field Exchange) functions. I would recommend using them rather than trying to reinvent the wheel and make more work for yourself.

What I'm asking is how do you transfer the data between the database and your variables. Are you doing the SQLBInd and SQLFetch statements yourself?