Solved

Quotes in Strings

Posted on 2002-04-25
39
344 Views
Last Modified: 2012-06-21
How do I keep quotes in strings from keeping data following the quotes from not being written to the database table?

I need to have a 21" monitor - nothing after the " is being added to the database...

I have tried:

initiative=Replace(Request.Form("Initiative"),"'","’")

And it's not working...

0
Comment
Question by:mapper
  • 17
  • 16
  • 2
  • +4
39 Comments
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
I think you need quote around html variable like

<input type= hidden name="Initiative" value="<%=Initiative%>">

0
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
YTou use 2 doubklequotes to make it work

response.write("21 "" monitor")

CJ
0
 

Author Comment

by:mapper
Comment Utility
Okay - I don't think I provided enough info here -

I have a form that users can enter data and it writes to a DB - the problem I am running into is - that if a user tries to use " in the string anything after the " is not writing to the DB - the page that does the writing is using the:

initiative=Replace(Request.Form("Initiative"),"'","&rsquo;")

This is where I need to manipulate the input so it will take the " and not truncate any data after the "

Does that make more sense?
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Did you do what I said ? Put double quotes around variable value in your html form and try first
0
 

Author Comment

by:mapper
Comment Utility
Okay - I had the process wrong - I was thinking of the intial entry - this is a form that is populated by the data in the DB table - the user can then modify the data - I had a user try to add a " after a number to signify inches - it didn't pass to the DB - here is how the DB is populating that one field:

<textarea name="Initiative" wrap="PHYSICAL" cols="20" rows="5"><% = RS.Fields("Initiative")%></textarea>

Is this what you had in mind?

Here is the initial field where the user writes to the DB table -

 <input type="hidden" name="Initiative" value="<% =request.form("Initiative") %>">

Is that correct or what  you meant?


0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
You should keep double quotes around your html fields and replace with 2 double quotes just before updating db
0
 

Author Comment

by:mapper
Comment Utility
I am not too sure what you are saying - the page that updates the record in the DB is using:

initiative=Replace(Request.Form("Initiative"),"'","&rsquo;")

How can an input (hidden) field write to the DB? I am using " " around all form field values -

I am really confused now...
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
You should keep double quotes around your html fields and replace with 2 double quotes just before updating db
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
sorry for double posting, try
<textarea name="Initiative" wrap="PHYSICAL" cols="20" rows="5">"<% = RS.Fields("Initiative")%>"</textarea>

and take off hidden variable
0
 

Author Comment

by:mapper
Comment Utility
J,

It's a three page process - the first page has form fields populated by

<textarea name="Initiative" wrap="PHYSICAL" cols="20" rows="5">"<% = RS.Fields("Initiative")%>"</textarea>

for textareas and similar ones for the input fields - that page goes to a preview page where all of the data is presented in a table format - this is where they change to:

<input type="hidden" name="Initiative" value="<% =request.form("Initiative") %>">

and the last page does the write to the DB table to update the records in the DB - it uses:

initiative=Replace(Request.Form("Initiative"),"'","&rsquo;")

I have double quotes " " around everything and I was trying to change the

initiative=Replace(Request.Form("Initiative"),"'","&rsquo;")

so it would recognize the double quotes - it works for single quotes ' ' but NOT double quotes " "

that's what I would like to do - as it is - it does not write anything to the DB after the "
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Actually this is the way I do it. I have two functions like

<%
Function EncodeSQL(sql)
     If isNull(sql) Then
          EncodeSQL = ""
     ElseIf isEmpty(sql) Then
          EncodeSQL = ""
     Else
          EncodeSQL = Replace(sql, "'", "\~")
     End If
End Function
%>
<%
Function DecodeSQL(sql)
     If isNull(sql) Then
          DecodeSQL = ""
     ElseIf isEmpty(sql) Then
          DecodeSQL = ""
     Else
          DecodeSQL = Replace(sql, "\~", "'")
     End If
End Function
%>

When I display, I do
<td><b>Comments:</b></TD>
<TD><%= decodesql(C_DESCRIPTION & "") %></td>

and for Update, I do
if len(comments) > 0 then sql = sql &  ",'>  " & EncodeSQL(comments) & " -" & date & "' "
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
In this example I store single/double quotes as a differnet character and deocode it to single/double when I display.
This can be solved if you use ADO update. ADO is more intelligent than ANSI SQL
0
 

Author Comment

by:mapper
Comment Utility
J - dude you lost me on that one...

Here is what I am using:

<%
logno=CInt(Request("Logno"))
usrname=Replace(Request.Form("UsrName"),"'","''")
phonenum=Replace(Request.Form("PhoneNum"),"'","''")
manager=Replace(Request.Form("Manager"),"'","''")
proposedideal=Replace(Request.Form("ProposedIdeal"),"'","''")
initiative=Replace(Request.Form("Initiative"),"'","&rsquo;")
bizunit=Replace(Request.Form("BizUnit"),"'","''")
savings=Replace(Request.Form("Savings"),"'","&rsquo;")
costs=Replace(Request.Form("Costs"),"'","''")
risks=Replace(Request.Form("Risks"),"'","&rsquo;")

%>
<%

Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn_ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\inetpub\wwwroot\htdocs\cism\assetmgmt\value\value.mdb;"
DBConn.Open DBConn_ConnectionString

Set RS=DBConn.Execute("UPDATE costsavings SET UsrName='" & usrname &"', PhoneNum='" & phonenum &"', Manager='" & manager &"', ProposedIdeal='" & proposedideal &"', Initiative='" & initiative &"', BizUnit='" & bizunit &"', Savings='" & savings &"', Costs='" & costs &"', Risks='" & risks &"' WHERE LogNo=" & logno &" ")

DBConn.Close
%>

How can I modify the initiative=Replace(Request.Form("Initiative"),"'","&rsquo;") so it will not truncate after the "

??

Thanks,
0
 
LVL 18

Expert Comment

by:mgfranz
Comment Utility
Single quotes around the HTML items, <input type='hidden'>

Double quotes around the VBScript items, "&...&"
0
 

Author Comment

by:mapper
Comment Utility
So, if I go back through the forms and change the " " to ' ' that will allow data after the " in the string to write to the DB then?
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
Antoher way to do it is to use the CHR function

CHR(34) = "
CHR(39) = '
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Here it is. I am only doing it for initiative

<%
Function EncodeSQL(sql)
    If isNull(sql) Then
         EncodeSQL = ""
    ElseIf isEmpty(sql) Then
         EncodeSQL = ""
    Else
         EncodeSQL = Replace(sql, "'", "\~")
    End If
End Function

Function DecodeSQL(sql)
    If isNull(sql) Then
         DecodeSQL = ""
    ElseIf isEmpty(sql) Then
         DecodeSQL = ""
    Else
         DecodeSQL = Replace(sql, "\~", "'")
    End If
End Function


logno=CInt(Request("Logno"))
usrname=Replace(Request.Form("UsrName"),"'","''")
phonenum=Replace(Request.Form("PhoneNum"),"'","''")
manager=Replace(Request.Form("Manager"),"'","''")
proposedideal=Replace(Request.Form("ProposedIdeal"),"'","''")
initiative=EncodeSQL(Request.Form("Initiative")
bizunit=Replace(Request.Form("BizUnit"),"'","''")
savings=Replace(Request.Form("Savings"),"'","&rsquo;")
costs=Replace(Request.Form("Costs"),"'","''")
risks=Replace(Request.Form("Risks"),"'","&rsquo;")

Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn_ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\inetpub\wwwroot\htdocs\cism\assetmgmt\value\value.mdb;"
DBConn.Open DBConn_ConnectionString

Set RS=DBConn.Execute("UPDATE costsavings SET UsrName='" & usrname &"', PhoneNum='" & phonenum &"',
Manager='" & manager &"', ProposedIdeal='" & proposedideal &"', Initiative='" & initiative &"', BizUnit='"
& bizunit &"', Savings='" & savings &"', Costs='" & costs &"', Risks='" & risks &"' WHERE LogNo=" &
logno &" ")

DBConn.Close
%>

When you display, do it like
<textarea name="Initiative" wrap="PHYSICAL" cols="20" rows="5">"<% = decodesql(RS.Fields("Initiative"))%>"</textarea>
0
 

Author Comment

by:mapper
Comment Utility
J, I tried it just like you submitted - it didn't work all text after the " was not added to the DB
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Can you do a response.write of your sql and let me know
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
If you use my functions then SQL must write a "\~" to the db, check that
0
 

Author Comment

by:mapper
Comment Utility
J - I see where I screwed up - I want the data after the " to write to the database - if it writes to the database then it will write back to the HTML page - I have tested it by going into the DB and adding the " after the 21 for 21" monitor and it worked just fine when I displayed the data - it's getting the " to write to the DB is where I am having the problem.
0
 

Author Comment

by:mapper
Comment Utility
J,

I added the 2 functions to the page that writes to the DB - it didn't add anything after the " in 21" monitor - all it wrote was 21 - nothing else - I manually added the " monitor and then went to a page that lists all entries in the DB and low and behold the entry was now: 21" monitor after I manually entered it into the DB -

Here is where I am having the problem getting the " and any text after it - to write to the DB - not displaying the "

Thanks
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
If db reaches a single it will assume thats the end of the string, so you have to put somethign else. For example

mystiring="Happy X'mas"
This will only take upto X and thats why my function will convert this to
mystring=encodeSQL("Happy X'mas") ' to Happy X\~mas

and in display purpose I am doing
myconvertedstring = Decode("Happy X\~mas") ' to Happy X'mas

Got it dude ?
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Actually you should try adding a new entry with the form when my 2 functions are incorporated. B'cos it expects to a "\~" in the db
Add a new one and let me know
0
 

Expert Comment

by:usamol
Comment Utility
If you are using SQL server you don't have to do anything, it will insert " into the database. But you want to display that text into text it shows text upto double quotes("), I am using similar criteria in one of my web page. Try to use
<input type='text/hidden' name='demo' value='<%=RS("field name")%>'>

in this tag use single quote instead of double quote as shown above.

0
 

Author Comment

by:mapper
Comment Utility
Yes, I have it - but, I tried your function and it didn't write anything after the " (double quote) to the database - so, what good is going to do me if it is not solving my original problem?  I am not trying to be impertinent - here - or disrespectful - but, I think there is a communication flyby happening here - by that, I don't  think that I am articulating my problem sufficiently enough to get the proper response.

The way the pages are set up now the will accept anything BUT a " (double quote) I can put in ' (single quotes) in all over the place and they will write to the DB and display when viewed on the listing page -

That is the problem I am having is there a way to get the way I am doing it now:

initiative=Replace(Request.Form("Initiative"),"'","&rsquo;")

To write the data after any " (double quote)?  That is where I am having a problem - not with single quotes or other characters - not in displaying the data in other pages - but, actually writing a double quote and any data after it to the DB.

Thanks,
0
 

Author Comment

by:mapper
Comment Utility
Usamol - to clarify what you have said so I understand - on the page that passes the value to the page that writes to the database change it from:

<input type="hidden" name="Initiative" value="<% =request.form("Initiative") %>">

TO:

<input type='tex/hidden' name='Initiative' value='<% =request.form("Initiative") %>'>

is that correct?

Also, this will allow the page that writes to the DB to actually write the double quote and any data after it to the DB?

thanks
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Can I see your whole code now ?
0
 

Author Comment

by:mapper
Comment Utility
Here is the code that writes to the DB:

<%
 
logno=CInt(Request("Logno"))
'logno=Replace(Request.Form("LogNo"),"'","''")
'approval=("NO")
usrname=Replace(Request.Form("UsrName"),"'","''")
phonenum=Replace(Request.Form("PhoneNum"),"'","''")
manager=Replace(Request.Form("Manager"),"'","''")
proposedideal=Replace(Request.Form("ProposedIdeal"),"'","''")
initiative=Replace(Request.Form("Initiative"),"'","&rsquo;")
bizunit=Replace(Request.Form("BizUnit"),"'","''")
savings=Replace(Request.Form("Savings"),"'","&rsquo;")
costs=Replace(Request.Form("Costs"),"'","''")
risks=Replace(Request.Form("Risks"),"'","&rsquo;")

%>
<%

Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn_ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\inetpub\wwwroot\htdocs\cism\assetmgmt\value\value.mdb;"
DBConn.Open DBConn_ConnectionString

Set RS=DBConn.Execute("UPDATE costsavings SET UsrName='" & usrname &"', PhoneNum='" & phonenum &"', Manager='" & manager &"', ProposedIdeal='" & proposedideal &"', Initiative='" & initiative &"', BizUnit='" & bizunit &"', Savings='" & savings &"', Costs='" & costs &"', Risks='" & risks &"' WHERE LogNo=" & logno &" ")

DBConn.Close
%>

This is w/o what you have suggested - or do you want me to cut and paste what you suggested back in and submit it to you?

The DB is an Access2000 one...
0
 

Accepted Solution

by:
usamol earned 50 total points
Comment Utility
Yes mapper, that will work because i am using similar code in my application and it works perfect.

(Note: Only thing is I have given option in input type ie text or hidden, whatever applicable to you. NOT TEXT/HIDDEN)
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Have you tried adding a new row and test it with my fucntions ?
Forget about your 21 " stuff
0
 

Author Comment

by:mapper
Comment Utility
Usamol - I did what you suggested and it worked like a charm!

Thanks to all who have helped out with this...

Thanks again
0
 

Author Comment

by:mapper
Comment Utility
Thanks for the help - where I didn't have to re-write a ton of code...

I will remember this...

Thanks,

0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
Good , I didn't know SQLServer is smarter than Oracle here
0
 

Author Comment

by:mapper
Comment Utility
No thanks for your help - I really appreicated it - I was just looking for the easiest way out of this problem.

Thanks again for your input.
0
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
My solution was a more native one. Its like these days they have OUTER JOIN clause, but in my college days they always used to refer as *= or =* for inner join
0
 

Author Comment

by:mapper
Comment Utility
:-)  Thanks again - I value your ability and willingness to try to help some who is having problems work them out...
0
 

Expert Comment

by:xbathala
Comment Utility
mapper, you may want to use this code. i used to bang my head with that "quote" problem :D

-------------------------------------------------
Function InsertAP(n)
     strVDummy=n
     strVDummy = Replace(strVDummy,"'","''")
     InsertAP=strVDummy
End Function

function insQuote(strDummy)
     insQuote = replace(strDummy,chr(34),"&quot;")
end function
-------------------------------------------------

use InsertAP when "inserting" text to to the database.

use insQuote when displaying it, especially on text fields.

the problem is that the characters ' and " are interpreted by the browser to "enclose" on form elements; using insQuote, you can now pass quotes as &quot thus making it appear on the browser.
0
 

Author Comment

by:mapper
Comment Utility
X Man - Thanks...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now