Solved

Quotes in Strings

Posted on 2002-04-25
39
347 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
API not working 33 62
format nvarchar field as mm/dd/yyyy 4 71
is this a cms? 8 60
age from date of birth 4 43
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 …
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

831 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