ASP Code to update field using SP (text field)

We had a field which was nvarchar 8000, it was changed to a text field to accomodate more characters.

I currently have the code below to call the Stored Procedure, but the parameter is for 8000 characters, how should I change it to allow the entry of as many characters as the text field allows?


Set UpdateSignature = Server.CreateObject ("ADODB.Command")
UpdateSignature.ActiveConnection = MM_bluedot_STRING
UpdateSignature.CommandText = "UPDATE Userlogin SET Emailsignature = ? WHERE  userid = ?"
UpdateSignature.Parameters.Append UpdateSignature.CreateParameter("MMColParam", 200, 1, 8000, MM_IIF(Request.Form("signature"), Request.Form("signature"), UpdateSignature__MMColParam & ""))
UpdateSignature.Parameters.Append UpdateSignature.CreateParameter("MMColParam2", 3, 1, 4, MM_IIF(Session("Userid"), Session("Userid"), UpdateSignature__MMColParam2 & ""))
UpdateSignature.CommandType = 1
UpdateSignature.CommandTimeout = 0
UpdateSignature.Prepared = true


Open in new window

Something else that may be relevant is that this field is intended to save the user's signature block, if they add an image (their logo) then the HTML editor converts it into characters and this is where 8000 characters don't seem to be enough.
I am not sure if because we are including an image I may need to use a different type of data for the user, otherwise I guess text fiedl will work.
Who is Participating?
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.

Big MontyWeb Ninja at largeCommented:
i would advise using the infamous file, which includes all constants definitions for all things ADO. simply download it and include it on your page, and use the constant name when setting your parameters. in this case, it would be adLongVarChar, which is set to 201

if you just want to use the numerical value, your code would look like:

UpdateSignature.Parameters.Append UpdateSignature.CreateParameter("MMColParam", 201, 1, Len( "" & Request.Form("signature") ), MM_IIF(Request.Form("signature"), Request.Form("signature"), UpdateSignature__MMColParam & ""))

I bolded the changes you'll need to make. since TEXT fields have a very large size, it's easier just to specify the length of your data, as shown with the LEN function

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
AleksAuthor Commented:
Ill have to learn more later about the include file.
I changed the code and seems to work, is text field OK ?
Big MontyWeb Ninja at largeCommented:
yes, text fields are the "next step up" from varchars. Unless you're storing binary data, text fields are fine
AleksAuthor Commented:
It seems to have worked!  thanks. all set here.
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
Web Development

From novice to tech pro — start learning today.