Apostrophe error while retrieving the record from the database

Discussion in 'ASP General' started by Bhavna, Jul 19, 2006.

  1. Bhavna

    Bhavna Guest

    I am using a Replace function to replace single quotes with double when
    submitting a text field in the database i.e. Replace (q, "'", "' ' ")
    which works fine. When I retrieve the field from the database which has
    apostrophe I am getting 'Object expected' error message. Is there a
    way to fix this?


    Thanks,
    Bhavna
    Bhavna, Jul 19, 2006
    #1
    1. Advertising

  2. Bhavna

    Bhavna Guest

    Bhavna wrote:
    > I am using a Replace function to replace single quotes with double when
    > submitting a text field in the database i.e. Replace (q, "'", "' ' ")
    > which works fine. When I retrieve the field from the database which has
    > apostrophe I am getting 'Object expected' error message. Even though I am checking in the code for apostrophe. For e.g. strOutput = Replace(strOutput, "'", "'")
    > Is there a way to fix this?
    >
    >
    > Thanks,
    > Bhavna
    Bhavna, Jul 19, 2006
    #2
    1. Advertising

  3. Bhavna

    Bhavna Guest

    Bhavna wrote:
    > I am using a Replace function to replace single quotes with double when
    > submitting a text field in the database i.e. Replace (q, "'", "' ' ")
    > which works fine. When I retrieve the field from the database which has
    > apostrophe I am getting 'Object expected' error message. Is there a
    > way to fix this?
    >
    >
    > Thanks,
    > Bhavna


    When I am retrieving the records from the db I am checking for
    apostrophe. For e.g. strOutput = Replace(strOutput, "'", "'").
    Still I am getting Object expected error message
    Bhavna, Jul 19, 2006
    #3
  4. Have you tried using chr() instead?
    strOutput = Replace(strOutput, chr(34), chr(39))
    ..
    "Bhavna" <> wrote in message
    news:...
    >
    > Bhavna wrote:
    > > I am using a Replace function to replace single quotes with double when
    > > submitting a text field in the database i.e. Replace (q, "'", "' ' ")
    > > which works fine. When I retrieve the field from the database which has
    > > apostrophe I am getting 'Object expected' error message. Is there a
    > > way to fix this?
    > >
    > >
    > > Thanks,
    > > Bhavna

    >
    > When I am retrieving the records from the db I am checking for
    > apostrophe. For e.g. strOutput = Replace(strOutput, "'", "&apos;").
    > Still I am getting Object expected error message
    >
    Michael Kujawa, Jul 19, 2006
    #4
  5. Bhavna

    Bhavna Guest

    One thing I don't understand anytime I am replacing anything with
    apostrophe it's throwing an error. Any help is appreciated.


    Thanks,
    Bhavna
    Bhavna, Jul 20, 2006
    #5
  6. Bhavna wrote:
    > I am using a Replace function to replace single quotes with double
    > when submitting a text field in the database i.e.


    What database? Type and version please. Never ask a database-related
    question without revealing this information up front. It is almost
    always relevant.

    > Replace (q, "'", "'
    > ' ") which works fine. When I retrieve the field from the database
    > which has apostrophe I am getting 'Object expected' error message. Is
    > there a way to fix this?
    >
    >

    Write a small page (a repro page) that contains the bare minimum amount
    of code that shows how to reproduce the problem and post it here. Also
    post instructions on how to create any database objects that are needed
    to run the reproduce the problem. We should be able to run your repro
    page in our environment and see the problem occurring.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 20, 2006
    #6
  7. Example

    dim namex
    namex="Mc'Donalds"
    Select name from table where name='" & namex & "'"

    the ' in Mc'Donalds terminates the value to Mc and
    will generate an error because of the extra ' generated by & "'"

    This way of doing recordset queries is open to SQL
    injection, so do not use it, it is just an example





    "Bhavna" <> wrote in message
    news:...
    > One thing I don't understand anytime I am replacing anything with
    > apostrophe it's throwing an error. Any help is appreciated.
    >
    >
    > Thanks,
    > Bhavna
    >
    Michael Kujawa, Jul 20, 2006
    #7
  8. Bhavna

    Patrice Guest

    What about showing us some code ?

    Also not sure but my understanding is that you change this particular
    character both when storing and retrieving values ? If yes this is IMO a bad
    approach as the character is not correctly stored in the DB which could be
    not really convenient.

    Depending on your DB the real problem is that the quote such as in 'O'Hara'
    is seen as if it were the end marker of the string. Replacing by a double
    quote will fix the problem 'O''hara'?
    This will store O'Hara in the DB, you have the correct char in the DB and
    you don't have any change to do when retrieving the value.

    Another option is to use parameters instead of building SQL queries as
    text...

    Don't know but it lloks like that this problem could actually hide a design
    problem you may want to check....

    --
    Patrice

    "Bhavna" <> a écrit dans le message de news:
    ...
    > One thing I don't understand anytime I am replacing anything with
    > apostrophe it's throwing an error. Any help is appreciated.
    >
    >
    > Thanks,
    > Bhavna
    >
    Patrice, Jul 20, 2006
    #8
  9. Bhavna

    Guest

    ple help me anyone

    i am using ms access database

    tqty is text fields

    rsCount.open "Select Sum(TQty) as temp from tbl_style where IPomsID="&
    cdbl(request("IPomsID")),con,2,3

    when i am exeuting query it gives below error

    Error Type:
    Microsoft JET Database Engine (0x80040E07)
    Data type mismatch in criteria expression.


    rsCount.open "Select Sum(TQty) as temp from tbl_style where IPomsID="&
    cdbl(request("IPomsID")),con,2,3
    Bhavna wrote:
    > I am using a Replace function to replace single quotes with double when
    > submitting a text field in the database i.e. Replace (q, "'", "' ' ")
    > which works fine. When I retrieve the field from the database which has
    > apostrophe I am getting 'Object expected' error message. Is there a
    > way to fix this?
    >
    >
    > Thanks,
    > Bhavna
    , Jul 21, 2006
    #9
  10. Bhavna

    Mike Brind Guest

    wrote:
    > ple help me anyone
    >
    > i am using ms access database
    >
    > tqty is text fields
    >
    > rsCount.open "Select Sum(TQty) as temp from tbl_style where IPomsID="&
    > cdbl(request("IPomsID")),con,2,3
    >
    > when i am exeuting query it gives below error
    >
    > Error Type:
    > Microsoft JET Database Engine (0x80040E07)
    > Data type mismatch in criteria expression.



    Please don't append your question to other threads unless they are
    relevant to that thread. Start a new thread instead.

    Change TQty to a numeric field. You can't add text fields up as if
    they are numbers.

    --
    Mike Brind
    Mike Brind, Jul 21, 2006
    #10
  11. wrote:
    > ple help me anyone
    >
    > i am using ms access database
    >
    > tqty is text fields
    >
    > rsCount.open "Select Sum(TQty) as temp from tbl_style where IPomsID="&
    > cdbl(request("IPomsID")),con,2,3


    If tqty is text, you cannot apply the Sum aggregate function to a text
    field. What are you expecting this query to return? From the name of the
    variable, rsCount, perhaps you are trying to find out how many records exist
    with the specified lPomsID? If so, you need to use the Count function
    instead of the Sum function. If so, reply and let us know because you need
    to be made aware of how Count works.

    Nothing to do with your problem but, hopefully you've done some validation
    before attempting to run this statement so you don't get an error if
    request("IPomsID") does not contain a number. This validation should be done
    in your server-side code, before you even open your connection object. Do
    not depend on client-side validation. Always validate user inputs in your
    server-side code.

    Also, it is a very bad practice to not specify the collection from which you
    wish to retrieve the "IPomsID" variable. Never use request("IPomsID").
    Always be explicit:: either request.form("IPomsID") or
    request.querystring("IPomsID").

    Further points to consider:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as
    they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jul 21, 2006
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SnVzdGlu?=

    Retrieving Record Key while creating the record.

    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    613
    =?Utf-8?B?SnVzdGlu?=
    Oct 5, 2004
  2. Craig
    Replies:
    1
    Views:
    1,877
    =?Utf-8?B?SmFtZXM=?=
    Jan 25, 2006
  3. RCITGuy
    Replies:
    1
    Views:
    252
    Joe Kaplan \(MVP - ADSI\)
    Jun 30, 2005
  4. Guest
    Replies:
    1
    Views:
    238
    Alex G
    Jan 9, 2004
  5. Jeanne
    Replies:
    3
    Views:
    216
    Daniel
    Jun 30, 2003
Loading...

Share This Page