ASP error '80020009' Exception occurred - relates to a Replace function

Hi Experts,

I've got an issue with an ASP site that uses an access database. It's probably a simple fix but i'm not familiar with ASP/access.

The issue occurs when they try to save a specific page, it's quite large, 99% HTML with a few javascript snippets in places. It's pretty messy due to years of editing via an old WYSIWYG, I'm really hoping to avoid manually cleaning it up if possible.

The issue has only emerged recently however reverting the content by restoring a backup of the DB doesn't seem to have fixed it. Other pages on the site are quite similar and  work fine.


When the user saves the page they get:

error '80020009'

Exception occurred.
save_section.asp, line 22

The line line mentioned from save_section.asp relates to sanitising the inputs for the access DB (see below)

There isn't really anything else to the file, the other parts are just the query and the response redirect once the query completes.

Let me know if I've missed any information which would help.

W1B



 
Dim strSQLpage, rsPage
	
	
	strSQLpage = "UPDATE tablename SET pagename = '" & MakeTextDBSafe(Request.Form("textarea")) & "' WHERE idpage = " & request.form("pageid")
	
	'Set rsPage = Server.CreateObject("ADODB.recordset")

	Set rsPage = dbCon.Execute(strSQLpage)

	response.write request.form("pageid")
	
	response.redirect "cms.asp?pageid=" & request.form("pageid") & "&saved=1"
	
	
	Function MakeTextDBSafe(strInputText)

	Dim strSafeText
	
	'replace apostrophies with double apostrophies
	strSafeText = Replace(strInputText, "'", "''")      '<--LINE 22

	
	MakeTextDBSafe = Trim(strSafeText)

End Function

Open in new window

LVL 1
While1-BreatheAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne BarronAuthor, Web DeveloperCommented:
Try this instead

strInputText = request.form("Fieldname")

strInputText = Replace(strInputText, "'", "''")

Good Luck
Carrzkiss
0
BanthorCommented:
Null is not an empty String, If the form element does not exist request.form("textarea")  null will be sent to the function.
either add a empty string to strInputText
strInputText = strInputText & ""
or validate request.form("textarea") before building strSQLpage
 
 
 
0
Dhanasekaran SengodanCommented:
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

govindarajan78Commented:

1. as Banthor says append an empty string and check

2. check as karrzkiss says - it will confirm that whether the error is in the line 22 or not [some times error line number faulty]

3. the line 6 is commented; un comment it and remove the word set from line 8 and check
0
While1-BreatheAuthor Commented:
Thanks for the quick responses everyone, I'll try these out later this afternoon (GMT+10).
0
While1-BreatheAuthor Commented:
Thanks for all your answers, it turns out the issue was due to the size of the page.

Apparently there is a limit of 102,000 characters for a POST in classic ASP, the page was sneaking in at 104,000.

We've got around it by updating the code to read the form in binary format.




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.