Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Quotes in Strings

Posted on 2002-04-25
39
348 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
ID: 6969303
I think you need quote around html variable like

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

0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6969304
YTou use 2 doubklequotes to make it work

response.write("21 "" monitor")

CJ
0
 

Author Comment

by:mapper
ID: 6969315
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 20

Expert Comment

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

Author Comment

by:mapper
ID: 6969332
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
ID: 6969354
You should keep double quotes around your html fields and replace with 2 double quotes just before updating db
0
 

Author Comment

by:mapper
ID: 6969372
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
ID: 6969376
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
ID: 6969382
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
ID: 6969397
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
ID: 6969400
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
ID: 6969408
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
ID: 6969416
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
ID: 6969419
Single quotes around the HTML items, <input type='hidden'>

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

Author Comment

by:mapper
ID: 6969435
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
ID: 6969441
Antoher way to do it is to use the CHR function

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

Expert Comment

by:jitganguly
ID: 6969444
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
ID: 6969468
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
ID: 6969477
Can you do a response.write of your sql and let me know
0
 
LVL 20

Expert Comment

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

Author Comment

by:mapper
ID: 6969485
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
ID: 6969497
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
ID: 6969501
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
ID: 6969507
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
ID: 6969513
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
ID: 6969524
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
ID: 6969537
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
ID: 6969538
Can I see your whole code now ?
0
 

Author Comment

by:mapper
ID: 6969550
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
ID: 6969558
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
ID: 6969559
Have you tried adding a new row and test it with my fucntions ?
Forget about your 21 " stuff
0
 

Author Comment

by:mapper
ID: 6969583
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
ID: 6969586
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
ID: 6969589
Good , I didn't know SQLServer is smarter than Oracle here
0
 

Author Comment

by:mapper
ID: 6969591
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
ID: 6969609
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
ID: 6969613
:-)  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
ID: 6970175
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
ID: 6971278
X Man - Thanks...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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