How to perform SQL INSERT with Chars like ', ",# in one of the colums 1

[paulfla]

With an MS Access database and ASP, I want to insert a row. The problem is that one of the columns, which is the iten description, may contain characters like a single quote ', double quotes ", pound signs # and any other character. When I perform the insert, sometimes the update is terminated at the '#' and only part of the field OrderItemTitle (item description) gets updated. Example: if the item description is "White 1" binder 8 x 10", when the item is added it would say ""White 1".

Can some one tell me how to get around this please. Thanks in advance for your time.

Paul

	'Open Database Connection
	Dim OrderDB,rsIssues,SC_Conn
	Set OrderDB = Server.CreateObject("ADODB.Connection")
	OrderDB.Mode = adModeReadWrite
	OrderDB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\inetpub\[URL unfurl="true"]wwwroot\_private\myacccessdb.mdb")[/URL]

			
			OrderItemIssue=	Request("IssueUnit")
			OrderItemCode = Request("ItemID")
			OrderItemClient = Request("client")
			OrderItemWhse = Request("whse")
			CurrOrderID = Request("CurrOrdID")
						
			OrderItemQty = Trim(Request.form("f_ItemQty"))
			If OrderItemQty = "" Then
				OrderItemQty = 1
			End if
			
			OrderItemTitle = Request.querystring("ItemName")
			OrderItemTitle = Replace(OrderItemTitle,"'","''")

			OrderItemPrice = Request.querystring("ItemCost")
			
			ActionQuery = ""
			ActionQuery = ActionQuery & "INSERT INTO OrderItems "
			ActionQuery = ActionQuery & "(OrderRef,OrderItemClient,OrderItemWhse,OrderItemTitle,OrderItemCode,OrderItemQty,OrderItemIssue)"
			ActionQuery = ActionQuery & "VALUES("
			ActionQuery = ActionQuery & CurrOrderID & ","
			ActionQuery = ActionQuery & "'" & OrderItemClient & "',"
			ActionQuery = ActionQuery & "'" & OrderItemWhse & "',"
			ActionQuery = ActionQuery & "'" & OrderItemTitle & "',"
			ActionQuery = ActionQuery & "'" & OrderItemCode & "',"
			ActionQuery = ActionQuery & "" & OrderItemQty & ","
			ActionQuery = ActionQuery & "'" & OrderItemIssue & "'"
			ActionQuery = ActionQuery & ")"
			OrderDB.Execute(ActionQuery)

			OrderDB.Close
			Set OrderDB = Nothing

 

[amorous]

Try

Server.HtmlEncode()

something like this:

ActionQuery = ActionQuery & "'" & Server.HtmlEncode("OrderItemClient") & "',"

and for all other values where ever needed

-VJ

-VJ

 

[paulfla]

Thanks for the suggestion, but the same thing is happening certain are being cutoff and the "#" pound sign.

By the way I had to take out a couple of " in the line of code.

Changed it from

ActionQuery = ActionQuery & "'" & Server.HtmlEncode("OrderItemClient") & "',"

to

ActionQuery = ActionQuery & "'" & Server.HtmlEncode(OrderItemClient) & "',"

As mentioned above, this did not resolve the problem. any other ideas?

 

[DreXor]

for starters your close looks good, the only thing hurting you is the garbage in garbage out rule :

try swapping this part around and put the code you altered back the way it was :

OrderItemIssue= SQLHandler(Request("IssueUnit"))
' Repeat the aforementioned on the rest of these .....
OrderItemCode = Request("ItemID")
OrderItemClient = Request("client")
OrderItemWhse = Request("whse")
OrderItemTitle = Request.querystring("ItemName")

' .... and add this function :
function sqlhandler(strtext)
on error resume next
sqlhandler = replace(replace(strtext,"'","''"),"""",""""")
end function

that should patch up the special characters to some degree, .. #'s shouldn't hurt you unless they're outside of text qualifiers

DreX
aKa - Robert

 

[paulfla]

In the function,the code below is giving me a "unterminated string" error:

sqlhandler = replace(replace (strtext,"'","''"),"""",""""")

Do I need to add another double-quote?

 

[paulfla]

one other thing, % and & are messing up the INSERT also

 

[Tarwn]

Yep, he meant it to be:

sqlhandler = replace(replace (strtext,"'","''"),"""",""[highlight]"[/highlight]""")

or maybe:

sqlhandler = replace(replace (strtext,"'","''"),"""","""[highlight]"[/highlight]"")

...ok, so it owuld be funny if I moved that poor quote all over the place, but I am to lazy

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[paulfla]

Got It! Finally.
It was a combination of this post and another post that was exactly a year old

http://www.tek-tips.com/viewthread.cfm?SQID=627416&SPID=333

thanks to nevermoor for his post and DreXor for making me think about the "garbage in" part. Since I was getting the information from a Form, that loaded the item description from a table, I performed this on the Form:

<form name="qtyForm" Method="Post" onsubmit="return
 doMaxOrdQ()" action="OrderQty.asp?Mode_test=2&ItemID=<%
=formno%>&client=<%=client%>&whse=<%=whse%
>&ItemCost=0&StatusVal=<%=statusval%>&IssueUnit=<%
=IssueUnit%>&CurrOrdID=<%=CurrOrdID%>&ItemName=<%=[b][COLOR=blue]Server.URLEncode(fname)[/color][/b]%>">

It took care of it all.

Thanks everyone

 

[Nevermoor]

For searching back to my struggles a year ago and learning from them: