 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Mike Collins
Guest
|
 Posted: Fri Apr 30, 2004 1:31 am Post subject: quote marks in SQL |
 |
|
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
|
|
| Back to top |
|
 |
Mark Finkle
Guest
|
| Posted: Fri Apr 30, 2004 3:29 am Post subject: 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
|
|
| Back to top |
|
|
Hans Galema
Guest
|
| Posted: Fri Apr 30, 2004 9:02 am Post subject: 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.
|
|
| Back to top |
|
|
JD
Guest
|
| Posted: Fri Apr 30, 2004 10:59 am Post subject: Re: quote marks in SQL |
|
|
Hans Galema <dontusethis (AT) dontusethis (DOT) nl> 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
|
|
| Back to top |
|
|
JD
Guest
|
| Posted: Fri Apr 30, 2004 11:13 am Post subject: Re: quote marks in SQL |
|
|
"Mike Collins" <mikec (AT) kr8 (DOT) co.uk> 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
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
