I got it! Lot of thanks!

[Rally A.]

Thanks Jim!



"Jim Edgar" <djedgar@home.com> wrote:
>
>"Rally A." <rally@pacific.net.ph> wrote:
>
>Rally -
>
>You probably need to double the apostrophe:
>
>don't --> don''t
>
>Use the replace() function to replace each
>single apostrophe with two apostrophes.
>
>Jim Edgar
>
>>I am having a problem in inserting string with apostrope character like
>"don't"
>>and other similar words.
>>
>>I am using visual basic 6.0 and Access 97.
>>
>>Have you been in this situation?
>>
>>Thanks,
>>
>>Rally
>>
>

[Michael Culley]

But what about square brackets, dots, !'s etc? (i think these fail + there
are probably more)

Mike

"Rally A." <rally@pacific.net.ph> wrote:
>
>Thanks Jim!
>
>
>
>"Jim Edgar" <djedgar@home.com> wrote:
>>
>>"Rally A." <rally@pacific.net.ph> wrote:
>>
>>Rally -
>>
>>You probably need to double the apostrophe:
>>
>>don't --> don''t
>>
>>Use the replace() function to replace each
>>single apostrophe with two apostrophes.
>>
>>Jim Edgar
>>
>>>I am having a problem in inserting string with apostrope character like
>>"don't"
>>>and other similar words.
>>>
>>>I am using visual basic 6.0 and Access 97.
>>>
>>>Have you been in this situation?
>>>
>>>Thanks,
>>>
>>>Rally
>>>
>>
>

[Shawn K. Hall]

Mike,

> But what about square brackets, dots, !'s etc? (i think these fail +
there
> are probably more)

Not if they're correctly between the quote's you are using to
encapsulate them.

Everything between " and " (or ' and ') is part of the string. If you
need to use the character that you are using to encapsulate (either an
apostrophe or a quote) you may double it within the string. Replacing
1 with 2 is a nice alternative to risking an error, but it can be
dangerous if the user has actually entered more than one
intentionally. If you have the option to manipulate the data at your
whim, it is better if you can use a character that is not within the
typical display set. For example, you could replace an apostrophe with
a Chr(12) (a "Form Feed" character). You can almost guarantee that
this value will not occur naturally within a string.

HTH,

Shawn K. Hall
Programmer / Analyst
*Please* post/respond in the newsgroups!
http://i.am/shawnkhall

[Jim Edgar]

Shawn --

>Replacing 1 with 2 is a nice alternative to risking an error, but it can be
> dangerous if the user has actually entered more than one
> intentionally.

How is this dangerous? I use Oracle instead of Access so I'm not totally
up to speed with the original post. However, in my case if a user types
in two single quotes (apostrophes) I replace them with four single quotes
and it is written to the db as two single quotes:

User types: don''t ' two apostrophes
Formatted: don''''t ' four apostrophes
Saved as: don''t

This allows the db to store the values exactly as the user types them.

Jim Edgar

[Michael Culley]

You are right, I tested this and these characters do not fail as I said they
did.

But I was sure that there was more than just the ' character that was a problem.
The | character also fails.

Mike


"Shawn K. Hall" <shawnkhall@iname.com> wrote:
>Mike,
>
>> But what about square brackets, dots, !'s etc? (i think these fail +
>there
>> are probably more)
>
>Not if they're correctly between the quote's you are using to
>encapsulate them.
>
>Everything between " and " (or ' and ') is part of the string. If you
>need to use the character that you are using to encapsulate (either an
>apostrophe or a quote) you may double it within the string. Replacing
>1 with 2 is a nice alternative to risking an error, but it can be
>dangerous if the user has actually entered more than one
>intentionally. If you have the option to manipulate the data at your
>whim, it is better if you can use a character that is not within the
>typical display set. For example, you could replace an apostrophe with
>a Chr(12) (a "Form Feed" character). You can almost guarantee that
>this value will not occur naturally within a string.
>
>HTH,
>
>Shawn K. Hall
>Programmer / Analyst
>*Please* post/respond in the newsgroups!
>http://i.am/shawnkhall
>
>
>

[Shawn K. Hall]

Jim,

If you only have one interface to your data that might be tolerable,
but there is more to consider/understand than simply how the
application stores and interprets the stored data.

1) If you ever have anyone edit the data from within the database
directly or via another interface (via ASP or PHP) then you are asking
for trouble.
2) If you have a large database and will be storing a great deal of
quotes/apostrophe's (like for storing quotes) you can alleviate adding
another character to the stored data by using an alternative "single"
character instead of doubling up.
3) You may alleviate some in-place memory problems (the application
needing to find a larger available memory block to store a longer
string) if you replace the value with a single character instead of
doubling up. This is faster simply because it does not need to find a
longer string block each time a replace occurs. I haven't *tested*
this, but if you'd like me to validate my assumptions with a test, I'd
be happy to. I can almost guarantee that it is slower based on
variable (re-)allocation than replacing it with a lesser-used
character like a vertical tab.
4) If you perform any recursive analysis of the content it is easily
conceiveable that you could have difficulty with dealing with
replacing these values.

Regards,

Shawn K. Hall
Programmer / Analyst
*Please* post/respond in the newsgroups!
http://i.am/shawnkhall


"Jim Edgar" <djedgar@home.com> wrote in message
news:3914597f$1@news.devx.com...
>
> How is this dangerous? I use Oracle instead of Access so I'm not
totally
> up to speed with the original post. However, in my case if a user
types
> in two single quotes (apostrophes) I replace them with four single
quotes
> and it is written to the db as two single quotes:
>
> User types: don''t ' two apostrophes
> Formatted: don''''t ' four apostrophes
> Saved as: don''t
>
> This allows the db to store the values exactly as the user types
them.
>
> Jim Edgar

[Michael Culley]

2) If you have a large database and will be storing a great deal of
>quotes/apostrophe's (like for storing quotes) you can alleviate adding
>another character to the stored data by using an >alternative "single"
>character instead of doubling up.

You don't add another character to the data, you just add the character to
the string when you are running a query. The data in the database keeps the
single quote as it should. This code works find:

strX="John O'Grady"
rs("FullName")=strX

This code will find the above record

rs.Open "SELECT * FROM tblABC WHERE FullName = 'John O''Grady'"

>I can almost guarantee that it is slower based on
>variable (re-)allocation than replacing it with a lesser-used
>character like a vertical tab.

This is most likely true, but in comparison to opening a recordset it will
be completely insignificant - even more true on a remote data source. Also,
don't you have to check for quotes every time you save every field and change
them to chr$(12), hence this method will be slower for saving.

I also disagree with this from a maintenance point of view. This is gauranteed
to confuse any maintenance programmer and any one who has to do anything
with your data in the future.

Michael Culley

"Shawn K. Hall" <shawnkhall@iname.com> wrote:
>Jim,
>
>If you only have one interface to your data that might be tolerable,
>but there is more to consider/understand than simply how the
>application stores and interprets the stored data.
>
>1) If you ever have anyone edit the data from within the database
>directly or via another interface (via ASP or PHP) then you are asking
>for trouble.
>2) If you have a large database and will be storing a great deal of
>quotes/apostrophe's (like for storing quotes) you can alleviate adding
>another character to the stored data by using an alternative "single"
>character instead of doubling up.
>3) You may alleviate some in-place memory problems (the application
>needing to find a larger available memory block to store a longer
>string) if you replace the value with a single character instead of
>doubling up. This is faster simply because it does not need to find a
>longer string block each time a replace occurs. I haven't *tested*
>this, but if you'd like me to validate my assumptions with a test, I'd
>be happy to. I can almost guarantee that it is slower based on
>variable (re-)allocation than replacing it with a lesser-used
>character like a vertical tab.
>4) If you perform any recursive analysis of the content it is easily
>conceiveable that you could have difficulty with dealing with
>replacing these values.
>
>Regards,
>
>Shawn K. Hall
>Programmer / Analyst
>*Please* post/respond in the newsgroups!
>http://i.am/shawnkhall
>
>
>"Jim Edgar" <djedgar@home.com> wrote in message
>news:3914597f$1@news.devx.com...
>>
>> How is this dangerous? I use Oracle instead of Access so I'm not
>totally
>> up to speed with the original post. However, in my case if a user
>types
>> in two single quotes (apostrophes) I replace them with four single
>quotes
>> and it is written to the db as two single quotes:
>>
>> User types: don''t ' two apostrophes
>> Formatted: don''''t ' four apostrophes
>> Saved as: don''t
>>
>> This allows the db to store the values exactly as the user types
>them.
>>
>> Jim Edgar
>
>

[Shawn K. Hall]

"Michael Culley" <m_culley@one.net.au> wrote in message
news:3916873f$1@news.devx.com...
>
> 2) If you have a large database and will be storing a great deal of
> >quotes/apostrophe's (like for storing quotes) you can alleviate
adding
> >another character to the stored data by using an >alternative
"single"
> >character instead of doubling up.
>
> You don't add another character to the data, you just add the
character to
> the string when you are running a query. The data in the database
keeps the
> single quote as it should. This code works find:
>
> strX="John O'Grady"
> rs("FullName")=strX
>
> This code will find the above record
>
> rs.Open "SELECT * FROM tblABC WHERE FullName = 'John O''Grady'"

That's fine if you're doing a search. I guess you don't use INSERT
INTO ... VALUES? With this type of SQL statement converting/checking
each field can be quite a hassle, but still a necessity.


> >I can almost guarantee that it is slower based on
> >variable (re-)allocation than replacing it with a lesser-used
> >character like a vertical tab.
>
> This is most likely true, but in comparison to opening a recordset
it will
> be completely insignificant - even more true on a remote data
source. Also,
> don't you have to check for quotes every time you save every field
and change
> them to chr$(12), hence this method will be slower for saving.

Assuming you're going to run Chr$(), yes. You can create a constant
within a typelib so it isn't affected with VB's messy character
definition limitations.

> I also disagree with this from a maintenance point of view. This is
gauranteed
> to confuse any maintenance programmer and any one who has to do
anything
> with your data in the future.
>
> Michael Culley

Documentation.

Anyway, it's not the best answer all the time, it's just another
option. You code with bound recordsets all the time, most probably on
a server. When you deal with dynamic generation of records locally for
an end-user database the memory footprint is very important. One app I
wrote several months ago would essentially iterate random pages from a
web server and attempt to download anything it found, it was running
across a 32k connection and downloading in the tens of thousands of
files (MSKB), it would then store this information within an access
database. While storing the information within the database I was
using INSERT INTO for the easiest out-of-process record add. There was
about a 7% (I haven't the actual benchmarks anymore) difference in
attempting to store the information if I passed the contents of the
file (HTML) to replace quotes with vertical tabs, instead of double
quotes. In the case of HTML, where there's a great deal of quotes, it
made a difference. Downloading that much info at that speed it really
made a difference. I can't say it will make a difference for anyone
else, but it has for me.

Regards,

Shawn K. Hall
Programmer / Analyst
*Please* post/respond in the newsgroups!
http://i.am/shawnkhall

[Mike Culley]

Shawn,

I guess you don't use INSERT
>INTO ... VALUES?

No, I don't personally like this method, but that is just my opinion.

>Assuming you're going to run Chr$(), yes.

But what I mean is that you still have to search EVERY string you save for
a quote and replace it with a character 12, whether you define it in a type
library or not. Also you have to do this for every record you retieve. Basically,
you have to do this every time you touch data in some way, instead of just
when you run an SQL string.

What if someone stores binary data? Do you write special code for this situation?

>Documentation.

Sure, but that still means they have to read the documentation, usually after
they find the problem anyway. Either way it would be an additional hassle.

But the real reason I disagree with this method is that your data should
be an exact representaion of your data, not modified. This makes it impossible
to paste data out of a database, you always have to write code for this.
For example, you can create pivot tables in excel that read directly from
SQL Server/Access/ODBC database. But with the chr 12 in there you could not.

Michael Culley



"Shawn K. Hall" <shawnkhall@iname.com> wrote:
>
>"Michael Culley" <m_culley@one.net.au> wrote in message
>news:3916873f$1@news.devx.com...
>>
>> 2) If you have a large database and will be storing a great deal of
>> >quotes/apostrophe's (like for storing quotes) you can alleviate
>adding
>> >another character to the stored data by using an >alternative
>"single"
>> >character instead of doubling up.
>>
>> You don't add another character to the data, you just add the
>character to
>> the string when you are running a query. The data in the database
>keeps the
>> single quote as it should. This code works find:
>>
>> strX="John O'Grady"
>> rs("FullName")=strX
>>
>> This code will find the above record
>>
>> rs.Open "SELECT * FROM tblABC WHERE FullName = 'John O''Grady'"
>
>That's fine if you're doing a search. I guess you don't use INSERT
>INTO ... VALUES? With this type of SQL statement converting/checking
>each field can be quite a hassle, but still a necessity.
>
>
>> >I can almost guarantee that it is slower based on
>> >variable (re-)allocation than replacing it with a lesser-used
>> >character like a vertical tab.
>>
>> This is most likely true, but in comparison to opening a recordset
>it will
>> be completely insignificant - even more true on a remote data
>source. Also,
>> don't you have to check for quotes every time you save every field
>and change
>> them to chr$(12), hence this method will be slower for saving.
>
>Assuming you're going to run Chr$(), yes. You can create a constant
>within a typelib so it isn't affected with VB's messy character
>definition limitations.
>
>> I also disagree with this from a maintenance point of view. This is
>gauranteed
>> to confuse any maintenance programmer and any one who has to do
>anything
>> with your data in the future.
>>
>> Michael Culley
>
>Documentation.
>
>Anyway, it's not the best answer all the time, it's just another
>option. You code with bound recordsets all the time, most probably on
>a server. When you deal with dynamic generation of records locally for
>an end-user database the memory footprint is very important. One app I
>wrote several months ago would essentially iterate random pages from a
>web server and attempt to download anything it found, it was running
>across a 32k connection and downloading in the tens of thousands of
>files (MSKB), it would then store this information within an access
>database. While storing the information within the database I was
>using INSERT INTO for the easiest out-of-process record add. There was
>about a 7% (I haven't the actual benchmarks anymore) difference in
>attempting to store the information if I passed the contents of the
>file (HTML) to replace quotes with vertical tabs, instead of double
>quotes. In the case of HTML, where there's a great deal of quotes, it
>made a difference. Downloading that much info at that speed it really
>made a difference. I can't say it will make a difference for anyone
>else, but it has for me.
>
>Regards,
>
>Shawn K. Hall
>Programmer / Analyst
>*Please* post/respond in the newsgroups!
>http://i.am/shawnkhall
>
>
>