Apostrophe error while retrieving the record from the database

[Bhavna]

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]

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]

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

 

[Michael Kujawa]

Have you tried using chr() instead?
strOutput = Replace(strOutput, chr(34), chr(39))
..

 

[Bhavna]

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


Thanks,
Bhavna

 

[Bob Barrows [MVP]]

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.

 

[Michael Kujawa]

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

 

[Patrice]

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

 

[v.keerthik]

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

 

[Mike Brind]

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.

 

[Bob Barrows [MVP]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]