I got it! Lot of thanks!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: I got it! Lot of thanks!

  1. #1
    Rally A. Guest

    I got it! Lot of thanks!


    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
    >>

    >



  2. #2
    Michael Culley Guest

    Re: I got it! Lot of thanks!


    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
    >>>

    >>

    >



  3. #3
    Shawn K. Hall Guest

    Re: I got it! Lot of thanks!

    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




  4. #4
    Jim Edgar Guest

    Re: I got it! Lot of thanks!


    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



  5. #5
    Michael Culley Guest

    Re: I got it! Lot of thanks!


    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
    >
    >
    >



  6. #6
    Shawn K. Hall Guest

    Re: I got it! Lot of thanks!

    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




  7. #7
    Michael Culley Guest

    Re: I got it! Lot of thanks!


    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

    >
    >



  8. #8
    Shawn K. Hall Guest

    Re: I got it! Lot of thanks!


    "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




  9. #9
    Mike Culley Guest

    Re: I got it! Lot of thanks!


    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
    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center