asked on

Quering a CRecordSet

I ran a query on my database looking for a record with the name---- O'Neal this name returns an error cause the " ' " is part of the SQL syntax, my question is how can i get arround this cause my database has a lot of names like this for example De'Abrew and De'Barros etc.
here's my code
CString str = "O'Neal";
m_pSet->Close();
m_pSet->m_strFilter = "[NAME] = '" + str + "'";
m_pSet->Open();
thanks in advance

ASKER CERTIFIED SOLUTION

csalves

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mwebster

ASKER

good point but i'm using Visual C++5 is there another way???

kretzschmar

hi mwebster,

try to double or to triple the char ' when detecting.

i.e. O''Neil or O'''Neil

the sql-parser of your database deletes one or two depending on the database-os you use

meikl

kretzschmar

hi mwebster,

i see you have accept the given answer. have you checked out my comment or is there no interest for any further discussion?

meikl

mwebster

ASKER

i did checked out your comment but it wont be very elegant to use double quotes in a person's name this problem even pops up when i'm searching for an address sorry about that but only csalves seem to be the best answer. i solved the problem using his solution like this
i created a new class called CTrapEdit and subclassed the edit ctrl in my dlgbox traping the '\'' character whenever it's pressed
afx_msg void CTrapEdit::OnChar(UINT nChar,UINT nRepCnt,UINT nFlags)
{
      if(nChar == '\'')
      {
//change it to another character
      }
      CEdit::OnChar(nChar,nRepCnt,nFlags);
}

BEGIN_MESSAGE_MAP(CTrapEdit,CEdit)
      ON_WM_CHAR()
END_MESSAGE_MAP()

and in my dlgBox OnInit function
i did this
m_edit.SubclassDlgItem(IDC_EDIT1,this);

and it worked hope this helps someone else outthere. of course if there's anyother way using visual C++ cuz i'm not very good a the SQL stuff feel free to lemme know
thanx again.

kretzschmar

hi mwebster,

i don't mean to store or to display or to edit the name with double or triple quotes, only temporary to build your filter-option. What do you do with stored names contains the char '. Do you convert them to ´ afterwise? I've checked it out with oracle and ' and ´ are different characters there.

Well, if you beginning with a empty table then your code is ok!

meikl

mwebster

ASKER

yes this is with an empty table i haven't figured out yet how to do it with a table that has the names stored like this already
thanx again