|
Home > Archive > IIS ASP > July 2006 > Apostrophe error while retrieving the record from the database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Apostrophe error while retrieving the record from the database
|
|
| Bhavna 2006-07-20, 7:22 pm |
| 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 2006-07-20, 7:22 pm |
|
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 2006-07-20, 7:22 pm |
|
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
| |
| Michael Kujawa 2006-07-20, 7:22 pm |
| Have you tried using chr() instead?
strOutput = Replace(strOutput, chr(34), chr(39))
..
"Bhavna" <bhavnabakshi@hotmail.com> wrote in message
news:1153333216.502373.282150@s13g2000cwa.googlegroups.com...
>
> Bhavna wrote:
>
> 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 2006-07-20, 7:22 pm |
| 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] 2006-07-20, 7:22 pm |
| 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.
| |
| Michael Kujawa 2006-07-20, 7:22 pm |
| 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" <bhavnabakshi@hotmail.com> wrote in message
news:1153417515.072588.95900@b28g2000cwb.googlegroups.com...
> 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 2006-07-20, 7:22 pm |
| 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" <bhavnabakshi@hotmail.com> a écrit dans le message de news:
1153417515.072588.95900@b28g2000cwb.googlegroups.com...
> One thing I don't understand anytime I am replacing anything with
> apostrophe it's throwing an error. Any help is appreciated.
>
>
> Thanks,
> Bhavna
>
| |
| v.keerthik@gmail.com 2006-07-21, 7:23 pm |
| 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
| |
| Mike Brind 2006-07-21, 7:23 pm |
|
v.keerthik@gmail.com 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
| |
| Bob Barrows [MVP] 2006-07-21, 7:23 pm |
| v.keerthik@gmail.com 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...2e36562fee7804e
Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:
Access:
http://www.google.com/groups?hl=en&...FTNGP12.phx.gbl
http://groups.google.com/groups?hl=...ftngp13.phx.gbl
--
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"
|
|
|
|
|