Replace function for double quotation mark.

E

Enjoy Life

I have a product that has a description of:
Pillowcases with a 2" Hem

When I update the product description into an Access database I get
the following value:
Pillowcases with a 2

I am familiar with the replace function for using it to fix single
quotes:
ProductName = replace(ProductName, "'", "''")

I am unsure how to adapt this replace function to convert the double
quote for an Access database.

Thanks in advance.

-Darren
 
R

Ray at

Why do you have to convert it to anything? Are you getting an error from
this quote? If so, what error and with what code?

Ray at work
 
E

Enjoy Life

I have to convert it b/c if I don't, the description will be:
Pillowcases with a 2
Instead of:
Pillowcases with a 2" Hem

It drops the " and everything following it.
 
R

Ray at

What does? Show the code!

Ray at work

Enjoy Life said:
I have to convert it b/c if I don't, the description will be:
Pillowcases with a 2
Instead of:
Pillowcases with a 2" Hem

It drops the " and everything following it.
Click to expand...
 
E

Enjoy Life

The code is as follows:
-------------------------------------------------------------------------------------------------------------------
ProductID = Request.Form("ProductID")
SupplierID = Request.Form("SupplierID")
ProductName = trim(Request.Form("ProductName"))

strSQL = "Update Products set ProductName = '" & ProductName & "'',
SupplierID = '" & SupplierID & "' where ProductID = " & ProductID & ""

cnLinens.Execute(strSQL)
-------------------------------------------------------------------------------------------------------------------
If ProductName has a value of: Pillowcases with a 2" Hem
It only updates it to: Pillowcases with a 2
I need it to update to: Pillowcases with a 2" Hem
 
R

Ray at

You have some other issue. The " won't matter in this case. One thing that
I noticed, and this should have given you an error, is that you have '' in
your query.

Yours:

strSQL = "Update Products set ProductName = '" & ProductName &
"'',SupplierID = '" & SupplierID & "' where ProductID = " & ProductID & ""

Look right before ,SupplierID
You have ''. You should only have '.

Ray at work
 
R

Ray at

P.S. Where are you seeing that this value is being truncated? Are you
looking in the database itself? My guess is that you're viewing the value
in a Web page, and probaly in an <input type="text"> input. Do a
view-source. You'll see your value is there, but it's not displaying in
your browser because the " is ending the value of the input. To fix that,
do:



<%
Function SafeOut(theText)
SafeOut = Replace(theText, """", "&quot;")
End Function
%>

<input type="text"
value="<%=SafeOut(YourRecordset.Fields.Item(yourIndex).Value)%>">

Ray at work
 
E

Enjoy Life

Yes, it is truncated in the database. If I manually put in the
correct value, it displays it correctly in the asp page. However, if
I hit update, it will truncate the value and store it incorrectly into
the database.
 
E

Enjoy Life

That extra " must have been a typo, sorry about that. That isn't the
problem however.
 
R

Ray at

You had an extra ', not ". Instead of TYPING the code into the message, can
you copy and paste your EXACT code? There is no reason that your value
should truncate at the " character. ' is your delimiter.

Ray at work
 
E

Enjoy Life

Ok, here comes the code. I didn't want to give you too much and then
you say you can't find anything.

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<!-- Database Connection File -->
<!-- #include file="connection.asp" -->
<%
ProductID = Request.Form("ProductID")
SupplierID = Request.Form("SupplierID")
ProductName = trim(Request.Form("ProductName"))
ProductNo = Request.Form("ProductNo")
QuantityPerUnit = Request.Form("QuantityPerUnit")
UnitPrice = Request.Form("UnitPrice")
UnitCost = Request.Form("UnitCost")
CasePack = Request.Form("CasePack")
SizeProduct = Request.Form("SizeProduct")
Weight = Request.Form("Weight")
MFG = Request.Form("MFG")
MFG_Shown = Request.Form("MFG_Shown")
Cotton_Poly = Request.Form("Cotton_Poly")
Ten_Sixteen = Request.Form("Ten_Sixteen")
Make = Request.Form("Make")
MakeShown = Request.Form("MakeShown")
CategoryID = Request.Form("CategoryID2")
ShadeID = Request.Form("ShadeID")
ButtonChoice = Request.Form("ButtonChoice")
Notes = Request.Form("Notes")

ProductName = replace(ProductName, "'", "''")

Make = replace(Make, "'", "''")
MakeShown = replace(MakeShown, "'", "''")
MFG = replace(MFG, "'", "''")
MFG_Shown = replace(MFG_Shown, "'", "''")
Notes = replace(Notes, "'", "''")
If Notes = "" Then
Notes = " "
End If

If ButtonChoice = "Update Product Information" Then

strSQL = "Update Products set ProductName = '" & ProductName &
"', ShadeID = '" & ShadeID & "', Notes = '" & Notes & "', CategoryID =
'" & CategoryID & "', MakeShown = '" & MakeShown & "', Make = '" &
Make & "', Ten_Sixteen = '" & Ten_Sixteen & "', Cotton_Poly = '" &
Cotton_Poly & "', MFG_Shown = '" & MFG_Shown & "', MFG = '" & MFG &
"', Weight = '" & Weight & "', SizeProduct = '" & SizeProduct & "',
CasePack = '" & CasePack & "', UnitCost = '" & UnitCost & "',
UnitPrice = '" & UnitPrice & "', QuantityPerUnit = '" &
QuantityPerUnit & "', ProductNo = '" & ProductNo & "', SupplierID = '"
& SupplierID & "' where ProductID = " & ProductID & ""
cnLinens.Execute(strSQL)

Response.Redirect ("ProductDetails.asp?ProductID=" &
ProductID)

set cnLinens = Nothing
End If

If ButtonChoice = "Insert New Product" Then

strSQL = "Insert into Products (ProductName, ShadeID,
Ten_Sixteen, ProductNo, CategoryID, SupplierID, SizeProduct, CasePack,
Weight, QuantityPerUnit, UnitPrice, UnitCost, MFG, MFG_Shown,
Cotton_Poly, Make, Notes, MakeShown) values ('" & ProductName & "', '"
& ShadeID & "', '" & Ten_Sixteen & "', '" & ProductNo & "', '" &
CategoryID & "', '" & SupplierID & "', '" & SizeProduct & "', '" &
CasePack & "', '" & Weight & "', '" & QuantityPerUnit & "', '" &
UnitPrice & "', '" & UnitCost & "', '" & MFG & "', '" & MFG_Shown &
"', '" & Cotton_Poly & "', '" & Make & "', '" & Notes & "', '" &
MakeShown & "')"
cnLinens.Execute(strSQL)

Response.Redirect ("Products.asp")

set cnLinens = Nothing
End If
%>
</BODY>
</HTML>
 
B

Bob Barrows [MVP]

Enjoy said:
The code is as follows:
-------------------------------------------------------------------------- -----------------------------------------
ProductID = Request.Form("ProductID")
SupplierID = Request.Form("SupplierID")
ProductName = trim(Request.Form("ProductName"))

strSQL = "Update Products set ProductName = '" & ProductName & "'',
SupplierID = '" & SupplierID & "' where ProductID = " & ProductID & ""

cnLinens.Execute(strSQL)
-------------------------------------------------------------------------- -----------------------------------------
If ProductName has a value of: Pillowcases with a 2" Hem
It only updates it to: Pillowcases with a 2
I need it to update to: Pillowcases with a 2" Hem
Click to expand...
Can you show us what you get when you do:
Response.Write strSQL

Bob Barrows
 
R

Ray at

There's nothing jumping out.

Please see Bob's request. I should have asked for that first. I'm out of
practice! SOrry.
 
E

Enjoy Life

Sure, not a problem. When I do a response.write, I get the following:

Update Products set ProductName = '- Pillowcases T-180 Percale 55/45
42x34 w/ 2 " Hem', ShadeID = '1', Notes = ' ', CategoryID = '13',
MakeShown = '', Make = '', Ten_Sixteen = '', Cotton_Poly = '',
MFG_Shown = '', MFG = '', Weight = '', SizeProduct = '', CasePack =
'6', UnitCost = '11.88', UnitPrice = '16.5', QuantityPerUnit = 'DZ',
ProductNo = '03717500', SupplierID = '31' where ProductID = 566
 
E

Enjoy Life

Yeah, I just responded to Bob's request. Here it is here as well.
Response.write strSQL give me the following:

Update Products set ProductName = '- Pillowcases T-180 Percale 55/45
42x34 w/ 2 " Hem', ShadeID = '1', Notes = ' ', CategoryID = '13',
MakeShown = '', Make = '', Ten_Sixteen = '', Cotton_Poly = '',
MFG_Shown = '', MFG = '', Weight = '', SizeProduct = '', CasePack =
'6', UnitCost = '11.88', UnitPrice = '16.5', QuantityPerUnit = 'DZ',
ProductNo = '03717500', SupplierID = '31' where ProductID = 566
 
B

Bob Barrows [MVP]

Enjoy said:
Sure, not a problem. When I do a response.write, I get the following:

Update Products set ProductName = '- Pillowcases T-180 Percale 55/45
42x34 w/ 2 " Hem', ShadeID = '1', Notes = ' ', CategoryID = '13',
MakeShown = '', Make = '', Ten_Sixteen = '', Cotton_Poly = '',
MFG_Shown = '', MFG = '', Weight = '', SizeProduct = '', CasePack =
'6', UnitCost = '11.88', UnitPrice = '16.5', QuantityPerUnit = 'DZ',
ProductNo = '03717500', SupplierID = '31' where ProductID = 566
Click to expand...
Now if you copy and paste this query from the browser window into a query
builder's SQL View window in Access and run it, do you get the truncated
result? From what I'm seeing, you shouldn't. The
'- Pillowcases T-180 Percale 55/45 42x34 w/ 2 " Hem'
string is delimited with apostrophes, so the " character should be treated
as a literal.

I bet your text field is configured to only allow 50 characters ....

Bob Barrows
 
E

Enjoy Life

It's not the only 50 Characters, because I deleted 15 letters before
the 2" Hem and still get a truncated result. I'll check the Query
builder's SQL View
 
E

Enjoy Life

Okay, I ran the query in Access, and that works. It updates the
product with an " in the description. Now, how can I get my ASP code
to do the same? It's gotta be something simple that I am overlooking.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot convert (double) to (double*) 1
Trapping Single Quotation Mark 9
Help calling a function 3
Only one table shows up with the information 2
Accessing array index addresses with custom datatype in a function 0
Registration Form 7
Did you know that there is a match-case function in python? 4
trimmed string after replace function 6

Members online

Total: 26 (members: 2, guests: 24)
Robots: 338

Forum statistics

Threads
473,755
Messages
2,569,535
Members
45,007
Latest member
obedient dusk

Latest Threads

Top