Board index » cppbuilder » quote marks in SQL

quote marks in SQL


2004-04-30 09:31:18 AM
cppbuilder63
Just a quick one, probably more suited to the .database groups but they seem
a bit stagnent :-)
I have an application that links up to an Access Database via ADO and an
ODBC connection. I hate the Data Aware VCL's so I've coded my application
to load-up the various information that I need into convensional VCL's i.e.
TEditBox etc. No problems. When the contents of the Edit Box changes, I
set a save flag then prompt the use to save when they navigate away from the
record. When it somes to actually saving the changes, I contruct the
required SQL myself, something like this:
qu1->SQL->Clear();
qu1->SQL->Add("UPDATE Repairs");
squ1->SQL->Add("SET Recieved_Mod_Strike = " +
edxRecievedModStrike3->Text
+ ", Purchase_Order = '" + edxPurchaseOrder3->Text
+ "', Foreign_Supplier_Code = '" +
edxCaseNumber3->Text + "'"
.......
- all text fields are enclosed in single speech marks!
Everything works fine, until I noticed that if I enter a single speed mark '
the SQL fails a throws an exception, because it's geting confused between
the quote mark that I use to enclose the text values, and the quote mark
that is appearing in the edit box.
Does anyone have any bright ideas on how to resolve this, appear from some
kind of block on entering speech marks into the edit boxes!
Thanks in advance,
Mike C
 
 

Re:quote marks in SQL

Quote
Does anyone have any bright ideas on how to resolve this, appear from some
kind of block on entering speech marks into the edit boxes!
Normally, you just need to escape the single quotes. In most cases this
means using 2 single quotes:
Jimmy's car
becomes:
Jimmy''s car
Note: its 2 single quotes, not 1 double quote.
Mark Finkle
 

Re:quote marks in SQL

Mike Collins wrote:
Quote
Everything works fine, until I noticed that if I enter a single speed mark '
the SQL fails a throws an exception, because it's geting confused between
the quote mark that I use to enclose the text values, and the quote mark
that is appearing in the edit box.

Does anyone have any bright ideas on how to resolve this, appear from some
kind of block on entering speech marks into the edit boxes!
You could simply double all single ticks found in a TEdit.
Then if you retrieve from the database make single ticks from doubles.
Don't know if this works. You could easily try.
You could also eplace it with a special string.
What you could try too is escaping the tick.
Convert "Jack's hat" to "Jack\'s hat".
But I think this might not work. I remenber that some years ago
someone had the same problem. Don't remember the solution. But
did you google already ?
Quote
Thanks in advance,
?
Hans.
 

{smallsort}

Re:quote marks in SQL

Hans Galema < XXXX@XXXXX.COM >wrote:
Quote
[...] What you could try too is escaping the tick.
Convert "Jack's hat" to "Jack\'s hat".
I never did find a solution to this myself so I just used a
character replace method when saving strings that replaces the
offending single quote with the character found under the tilde
( ~ ) on the keyboard ( ` ).
No one has ever complained but if it's ever a problem, it's an
easy proposition to add another method when reading strings to
convert it back.
~ JD
 

Re:quote marks in SQL

"Mike Collins" < XXXX@XXXXX.COM >wrote:
Quote
[...] squ1->SQL->Add("SET Recieved_Mod_Strike = " +
edxRecievedModStrike3->Text
+ ", Purchase_Order = '" + edxPurchaseOrder3->Text
+ "', Foreign_Supplier_Code = '" +
edxCaseNumber3->Text + "'"
Just to demonstrate what I think is cleaner and easier to code
and maintain:
AnsiString MySql;
MySql.sprintf("SET Recieved_Mod_Strike = %s, Purchase_Order = '%s', Foreign_Supplier_Code = '%s'",
edxRecievedModStrike3->Text,
edxPurchaseOrder3->Text,
edxCaseNumber3->Text );
squ1->SQL->Add( MySql );
~ JD