quotation marks in text fields

[SteveG]

I'm using an sql INSERT statement to add text data to an Access database. If
the text includes an embedded quotation mark, the text is truncated at that
point - without an error. This only happens when I am hosted on Windows 2003
sever. When hosted on Windows 2000 it works fine.

Is there something similar to using the Replace function to add a 2nd
apostrophe (eg: Replace(textstream,"'","''") to avoid errors with embedded
apostrophes?

Thanks, Steve G.

[Ray at]

Yes, exactly what you're getting at. To "escape" apostrophes in SQL, you
double them.


sData = Request.Form("textfield")
sSQL = "INSERT INTO myTable (col) VALUES ('" & Replace(sData, "'", "''") &
"')"

Ray at home

"SteveG" <steveg1@tampabay.rr.com> wrote in message
news:LanFc.15752$IX4.1289343@twister.tampabay.rr.c om...

> I'm using an sql INSERT statement to add text data to an Access database.

If

> the text includes an embedded quotation mark, the text is truncated at

that

> point - without an error. This only happens when I am hosted on Windows

2003

> sever. When hosted on Windows 2000 it works fine.
>
> Is there something similar to using the Replace function to add a 2nd
> apostrophe (eg: Replace(textstream,"'","''") to avoid errors with embedded
> apostrophes?
>
> Thanks, Steve G.
>
>

[Bob Barrows [MVP]]

SteveG wrote:

> I'm using an sql INSERT statement to add text data to an Access
> database. If the text includes an embedded quotation mark, the text
> is truncated at that point - without an error. This only happens when
> I am hosted on Windows 2003 sever. When hosted on Windows 2000 it
> works fine.

Really? Delimiter problems should not depend on the OS being used ...

>
> Is there something similar to using the Replace function to add a 2nd
> apostrophe (eg: Replace(textstream,"'","''") to avoid errors with
> embedded apostrophes?
>
> Thanks, Steve G.

Switch to using saved parameter queries and never worry about delimiters
again. Just Google for posts by me containing those words for details.

Bob Barrows

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

[SteveG]

Bob,

Thanks for the response. I've never used QueryDef or saved parameter
queries. I've now read most of the related articles on google and am still
unsure how to code it in ASP. M/S KBase article # 142938 comes closest but I
don't think the example is for ASP. Can you point me to a KB article or one
of yours that might help?

Steve G.
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23nKtKRPYEHA.1224@TK2MSFTNGP09.phx.gbl...

> SteveG wrote:

> > I'm using an sql INSERT statement to add text data to an Access
> > database. If the text includes an embedded quotation mark, the text
> > is truncated at that point - without an error. This only happens when
> > I am hosted on Windows 2003 sever. When hosted on Windows 2000 it
> > works fine.

>
> Really? Delimiter problems should not depend on the OS being used ...
>

> >
> > Is there something similar to using the Replace function to add a 2nd
> > apostrophe (eg: Replace(textstream,"'","''") to avoid errors with
> > embedded apostrophes?
> >
> > Thanks, Steve G.

>
> Switch to using saved parameter queries and never worry about delimiters
> again. Just Google for posts by me containing those words for details.
>
> Bob Barrows
>
> --
> 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]]

Several of these posts include instructions on how to run the queries from
ASP.

[url]http://www.google.com/groups?as_q=saved%20parameter%20query&safe=images& ie=UTF-8&as_uauthors=Bob%20Barrows&lr=&hl=en[/url]

Bob Barrows

SteveG wrote:

> Bob,
>
> Thanks for the response. I've never used QueryDef or saved parameter
> queries. I've now read most of the related articles on google and am
> still unsure how to code it in ASP. M/S KBase article # 142938 comes
> closest but I don't think the example is for ASP. Can you point me
> to a KB article or one of yours that might help?
>
> Steve G.
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:%23nKtKRPYEHA.1224@TK2MSFTNGP09.phx.gbl...

>> SteveG wrote:

>>> I'm using an sql INSERT statement to add text data to an Access
>>> database. If the text includes an embedded quotation mark, the text
>>> is truncated at that point - without an error. This only happens
>>> when
>>> I am hosted on Windows 2003 sever. When hosted on Windows 2000 it
>>> works fine.

>>
>> Really? Delimiter problems should not depend on the OS being used ...
>>

>>>
>>> Is there something similar to using the Replace function to add a
>>> 2nd apostrophe (eg: Replace(textstream,"'","''") to avoid errors
>>> with embedded apostrophes?
>>>
>>> Thanks, Steve G.

>>
>> Switch to using saved parameter queries and never worry about
>> delimiters again. Just Google for posts by me containing those words
>> for details.
>>
>> Bob Barrows
>>
>> --
>> 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"

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

[SteveG]

Bob,

Those were excellent. Once I found one that explained that they were plain
ol' Access queries with parameters, I was off and running. They are really
easier once you understand them. The good news is that they work, the bad
new is I still have my problem. I should have paid more heed to your
original questioning of a Win 2003 problem. Turns out that the data
truncation is occurring while passing hidden form fields from one page to
another. I take the form input in on one page, display the data on a 2nd
page and then pass it to a 3rd page (via hidden form fields) where I then
write it to the database. I've now proven the data is good leaving the 2nd
page (response.write) and truncated upon receipt in the 3rd. If I bypass the
display step and
avoid passisng the data via hidden form fields - all works fine, in fact
really fine now that I'm using parameter queries!. Any suggestions?

Steve G.
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eBmWfvbYEHA.732@TK2MSFTNGP10.phx.gbl...

> Several of these posts include instructions on how to run the queries from
> ASP.
>
>

[url]http://www.google.com/groups?as_q=saved%20parameter%20query&safe=images& ie=UTF-8&as_uauthors=Bob%20Barrows&lr=&hl=en[/url]

>
> Bob Barrows
>
> SteveG wrote:

> > Bob,
> >
> > Thanks for the response. I've never used QueryDef or saved parameter
> > queries. I've now read most of the related articles on google and am
> > still unsure how to code it in ASP. M/S KBase article # 142938 comes
> > closest but I don't think the example is for ASP. Can you point me
> > to a KB article or one of yours that might help?
> >
> > Steve G.
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:%23nKtKRPYEHA.1224@TK2MSFTNGP09.phx.gbl...

> >> SteveG wrote:
> >>> I'm using an sql INSERT statement to add text data to an Access
> >>> database. If the text includes an embedded quotation mark, the text
> >>> is truncated at that point - without an error. This only happens
> >>> when
> >>> I am hosted on Windows 2003 sever. When hosted on Windows 2000 it
> >>> works fine.
> >>
> >> Really? Delimiter problems should not depend on the OS being used ...
> >>
> >>>
> >>> Is there something similar to using the Replace function to add a
> >>> 2nd apostrophe (eg: Replace(textstream,"'","''") to avoid errors
> >>> with embedded apostrophes?
> >>>
> >>> Thanks, Steve G.
> >>
> >> Switch to using saved parameter queries and never worry about
> >> delimiters again. Just Google for posts by me containing those words
> >> for details.
> >>
> >> Bob Barrows
> >>
> >> --
> >> 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"

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

SteveG wrote:

> Bob,
>
> Those were excellent. Once I found one that explained that they were
> plain
> ol' Access queries with parameters, I was off and running. They are
> really
> easier once you understand them. The good news is that they work,
> the bad
> new is I still have my problem. I should have paid more heed to your
> original questioning of a Win 2003 problem. Turns out that the data
> truncation is occurring while passing hidden form fields from one
> page to
> another. I take the form input in on one page, display the data on a
> 2nd
> page and then pass it to a 3rd page (via hidden form fields) where I
> then
> write it to the database. I've now proven the data is good leaving
> the 2nd
> page (response.write) and truncated upon receipt in the 3rd. If I
> bypass the
> display step and
> avoid passisng the data via hidden form fields - all works fine, in
> fact
> really fine now that I'm using parameter queries!. Any suggestions?
>

I guess we need to see what the code looks like in page two. What's wrong
with using both hidden fields and display fields?

Bob Barrows
--
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"

[SteveG]

Bob,
Here is the code from Page 2 . I left out the display tables above this form

<form method="POST" action="ProcessOrder.asp">
<p align="center"> If you want to make changes to your input, press the
[BACK] button on your Browser.</p><br>
Press &quot;Proceed&quot; when you are satisfied with your Gripe-O-Gram
<br>
and you are ready to proceed with payment <br>
<input type="submit" value="Proceed" name="B1">
<input type="hidden" name="fName" value="<%= Request.Form("Name") %>">
<input type="hidden" name="fEmail" value="<%= Request.Form("Email") %>">
<input type="hidden" name="fToName" value="<%= Request.form("ToName") %>">
<input type="hidden" name="fToAdr" value="<%= Request.Form("ToAdr") %>">
<input type="hidden" name="fToCity" value="<%= Request.Form("ToCity") %>">
<input type="hidden" name="fToState" value="<%= Request.Form("ToState")
%>">
<input type="hidden" name="fToZip" value="<%= Request.Form("ToZip") %>">
<input type="hidden" name="fGriperName" value="<%=
Request.Form("GriperName") %>">
<input type="hidden" name="fGripeeName" value="<%=
Request.Form("GripeeName") %>">
<input type="hidden" name="fAdj" value="<%= Request.Form("Adjective") %>">
<input type="hidden" name="fMod" value="<%= Request.Form("Modifier") %>">
<input type="hidden" name="fNoun" value="<%= Request.Form("Noun") %>">
<input type="hidden" name="fGripeText" value="<%=
Request.Form("GripeText") %>">
</form>
<%= Request.form("Name")%>

Previous stmt gives this GOOD html result:
Billy "The Wonder Man" Graham

-------------------------------------------------------
Page 3 ("ProcessOrder.asp") code:
Dim fControlNum
Dim fName
Dim fEmail
Dim fToName
Dim fToAdr
Dim fToCity
Dim fToState
Dim fToZip
Dim fGriperName
Dim fGripeeName
Dim fAdj
Dim fMod
Dim fNoun
Dim fGripeText
Dim rsGripes
'== Create a random Control Number
Randomize
fControlNum = Round((Rnd *1000001)+1,0) 'results between 0&1, x1M +1, round
to 0 decimals
'== Put the form fields into variables in preparation for use in sql
Response.write(Request.Form("fName"))
fName = Replace(Request.Form("fName"),"'","''")
fEmail = Replace(Request.Form("fEmail"),"'","''")
fToName = Replace(Request.Form("fToName"),"'","''")
fToAdr = Replace(Request.Form("fToAdr"),"'","''")
fToCity = Replace(Request.Form("fToCity"),"'","''")
fToState = Replace(Request.Form("fToState"),"'","''")
fToZip = Request.Form("fToZip")
fGriperName = Replace(Request.Form("fGriperName"),"'","''")
fGripeeName = Replace(Request.Form("fGripeeName"),"'","''")
fAdj = Request.Form("fAdj")
fMod = Request.Form("fMod")
fNoun = Request.Form("fNoun")
fGripeText = Replace(Request.Form("fGripeText"),"'","''")
......followed by database processing......

Html results from Response.Write (14 lines back/above) gives truncated
result:
Billy

Anything obvious I'm doing wrong to cause this?
Really appreciate your help & follow-thru. Steve G.


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23UTlpAuYEHA.3512@TK2MSFTNGP12.phx.gbl...

> SteveG wrote:

> > Bob,
> >
> > Those were excellent. Once I found one that explained that they were
> > plain
> > ol' Access queries with parameters, I was off and running. They are
> > really
> > easier once you understand them. The good news is that they work,
> > the bad
> > new is I still have my problem. I should have paid more heed to your
> > original questioning of a Win 2003 problem. Turns out that the data
> > truncation is occurring while passing hidden form fields from one
> > page to
> > another. I take the form input in on one page, display the data on a
> > 2nd
> > page and then pass it to a 3rd page (via hidden form fields) where I
> > then
> > write it to the database. I've now proven the data is good leaving
> > the 2nd
> > page (response.write) and truncated upon receipt in the 3rd. If I
> > bypass the
> > display step and
> > avoid passisng the data via hidden form fields - all works fine, in
> > fact
> > really fine now that I'm using parameter queries!. Any suggestions?
> >

> I guess we need to see what the code looks like in page two. What's wrong
> with using both hidden fields and display fields?
>
> Bob Barrows
> --
> 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]]

Before I attempt to reproduce this with my web server, let me ask if you've
tried HTMLEncode when purring the data inot the hidden fields?

<input type="hidden" name="fName" value="
<%= Server.HTMLEncode(Request.Form("Name")) %>">

Bob Barrows


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

[SteveG]

It Works . No truncation using the HTMLencode.
Why do I need it? I'll be researching in parallel.

Steve G
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OhZYqBwYEHA.3972@TK2MSFTNGP12.phx.gbl...

> Before I attempt to reproduce this with my web server, let me ask if

you've

> tried HTMLEncode when purring the data inot the hidden fields?
>
> <input type="hidden" name="fName" value="
> <%= Server.HTMLEncode(Request.Form("Name")) %>">
>
> Bob Barrows
>
>
> --
> 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]]

Think about what happens when your data contains quotes:
<input type="hidden" name="fName" value="some data that "contains
some quotes"">

Where does the parser think the sting being assigned to the value attribute
ends?

Now look at the result of HTMLEncode:
<input type="hidden" name="fName" value="some data that &quot;contains
some quotes&quot;">

There is no problem deciding where this string ends, right?

Bob Barrows

SteveG wrote:

> It Works . No truncation using the HTMLencode.
> Why do I need it? I'll be researching in parallel.
>
> Steve G
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OhZYqBwYEHA.3972@TK2MSFTNGP12.phx.gbl...

>> Before I attempt to reproduce this with my web server, let me ask if
>> you've tried HTMLEncode when purring the data inot the hidden fields?
>>
>> <input type="hidden" name="fName" value="
>> <%= Server.HTMLEncode(Request.Form("Name")) %>">
>>

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

[SteveG]

Agreed - but why do I need it on this site and not others? Steve G.
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u35llj1YEHA.3688@TK2MSFTNGP12.phx.gbl...

> Think about what happens when your data contains quotes:
> <input type="hidden" name="fName" value="some data that "contains
> some quotes"">
>
> Where does the parser think the sting being assigned to the value

attribute

> ends?
>
> Now look at the result of HTMLEncode:
> <input type="hidden" name="fName" value="some data that &quot;contains
> some quotes&quot;">
>
> There is no problem deciding where this string ends, right?
>
> Bob Barrows
>
> SteveG wrote:

> > It Works . No truncation using the HTMLencode.
> > Why do I need it? I'll be researching in parallel.
> >
> > Steve G
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:OhZYqBwYEHA.3972@TK2MSFTNGP12.phx.gbl...

> >> Before I attempt to reproduce this with my web server, let me ask if
> >> you've tried HTMLEncode when purring the data inot the hidden fields?
> >>
> >> <input type="hidden" name="fName" value="
> >> <%= Server.HTMLEncode(Request.Form("Name")) %>">
> >>

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

I do not know. It should not be site-specific.

Bob
SteveG wrote:

> Agreed - but why do I need it on this site and not others? Steve G.
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:u35llj1YEHA.3688@TK2MSFTNGP12.phx.gbl...

>> Think about what happens when your data contains quotes:
>> <input type="hidden" name="fName" value="some data that "contains
>> some quotes"">
>>
>> Where does the parser think the sting being assigned to the value
>> attribute ends?
>>
>> Now look at the result of HTMLEncode:
>> <input type="hidden" name="fName" value="some data that
>> &quot;contains some quotes&quot;">
>>
>> There is no problem deciding where this string ends, right?
>>
>> Bob Barrows
>>
>> SteveG wrote:

>>> It Works . No truncation using the HTMLencode.
>>> Why do I need it? I'll be researching in parallel.
>>>
>>> Steve G
>>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
>>> news:OhZYqBwYEHA.3972@TK2MSFTNGP12.phx.gbl...
>>>> Before I attempt to reproduce this with my web server, let me ask
>>>> if you've tried HTMLEncode when purring the data inot the hidden
>>>> fields?
>>>>
>>>> <input type="hidden" name="fName" value="
>>>> <%= Server.HTMLEncode(Request.Form("Name")) %>">
>>>>

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