Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Store pure HTML in a SQL Server 2000 Database using Classic ASP

Posted on 2007-07-30
18
Medium Priority
?
322 Views
Last Modified: 2010-04-09
Hi there I have a textarea HTML element holds some actual HTML code. I've set the corresponding field to type ntext in the database and am trying to submit that code into the database. I know the value is being passed but Im getting this error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'p_PP_InsertProject2' expects parameter '@BriefDescriptionOfScope', which was not supplied.
/PastProjects/PPForm_2.asp, line 35

POST Data:
hiddenClientID=&hiddenContactID=&hiddenDesignPhaseID=&txtBriefDescriptionOfScope=%3Cfont+face%3D%22verdana%2Cgeneva%22+size%3D%223%22%3E%3Cstrong%3EGenesis+provided+extensive%0D%0Asupport+to+Texaco+an . . .

The question is What do I need to do to my data before I submit it as its not accepting it as pure HTML. Can Anyone help.

Mayoor
0
Comment
Question by:MayoorPatel
  • 7
  • 5
  • 5
  • +1
18 Comments
 
LVL 14

Expert Comment

by:huji
ID: 19593340
I guess it is because there are single quotes (') in the uploaded HTML, which confuse SQL server. For this reason, and for security reasons, you should change single quotes to two single quotes in your texts. For example, if you get the value of the text area like this:

   txtTextArea = request.form("myTextArea")

You should change that line to such:

   txtTextArea = replace(request.form("myTextArea"), "'", "''")

I am rewriting the last part of that command in a wider way, to make sure you can read it and understand it easily:
        ...mytextArea  "  ) ,  "  '  " ,    "   '  '   "

Huji
0
 
LVL 34

Expert Comment

by:Big Monty
ID: 19593347
Try using the Server.HTMLEncode function before you submit your code. If that doesnt work, please post the code that saves to the database.

B.D.
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 19593492
Huji that is submitting but when I check the DB there is a blank column there. Here is the ASP code that does the submit.

If Action = "Next" then    
   
    txtBriefDescriptionOfScope = replace(request.form("BriefDescriptionOfScope"), "'", "''")  

    Set adocmd = Server.CreateObject("ADODB.Command")
    With adocmd
        .ActiveConnection = objConn
        .CommandText = "p_PP_InsertProject2"
        .CommandType = adCmdStoredProc    
        .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput,2, 4)
        .Parameters.Append .CreateParameter("@BriefDescriptionOfScope", adVarchar, adParamInput,2, txtBriefDescriptionOfScope)
     
        .Execute, , adExecuteNoRecords
    End With
    Set adocmd = Nothing

    response.Redirect("PPForm_3.asp")

End If



0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 14

Expert Comment

by:huji
ID: 19593600
while I'm working on it, try to submit a very simple text (for example BLAH BLAH) and see if it works or not.
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 19593778
Hi there normal text seems to work fine. Its just when its HTML from that text area!
0
 
LVL 14

Expert Comment

by:huji
ID: 19593819
I'm not okay with these two lines:

.Parameters.Append .CreateParameter("@ID", adInteger, adParamInput,2, 4)
.Parameters.Append .CreateParameter("@BriefDescriptionOfScope", adVarchar, adParamInput,2, txtBriefDescriptionOfScope)

The first one is okay to me indeed. But the second one... you have that   2   there. It makes the parameter length equal to "two characters", which is not what you probably want.
0
 
LVL 14

Expert Comment

by:huji
ID: 19593822
Try this and see if it works:

Set adocmd = Server.CreateObject("ADODB.Command")
    With adocmd
        .ActiveConnection = objConn
        .CommandText = "p_PP_InsertProject2"
        .CommandType = adCmdStoredProc    
        .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput,2, 4)
        .Parameters.Append .CreateParameter("@BriefDescriptionOfScope", adVarchar, adParamInput,9, "BLAH BLAH")

tell me the result as well
0
 
LVL 25

Expert Comment

by:kevp75
ID: 19597753
the problem may lie in the table itself.  I notice you are tying to pass a varchar for that field with a specified size of 9

try changing it to ntext
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 19598668
Right I have the following code I am told is correct

Set adocmd = Server.CreateObject("ADODB.Command")
    With adocmd
        .ActiveConnection = objConn
        .CommandText = "p_PP_InsertProject2"
        .CommandType = adCmdStoredProc    
        .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput,2, 4)
        .Parameters.Append .CreateParameter("@BriefDescriptionOfScope", adLongVarWChar,adParamInput,-1, txtBriefDescriptionOfScope)      
        .Execute, , adExecuteNoRecords
    End With
    Set adocmd = Nothing

I am also running the txt throught this function beforehand.

Function HTMLDecode(sText)
    Dim I
    sText = Replace(sText, """, Chr(34))
    sText = Replace(sText, "<"  , Chr(60))
    sText = Replace(sText, ">"  , Chr(62))
    sText = Replace(sText, "&" , Chr(38))
    sText = Replace(sText, " ", Chr(32))
    For I = 1 to 255
        sText = Replace(sText, "&#" & I & ";", Chr(I))
    Next
    HTMLDecode = sText
End Function

So really there shold be no excuses. Here is the whole code.

Function HTMLDecode(sText)
    Dim I
    sText = Replace(sText, """, Chr(34))
    sText = Replace(sText, "<"  , Chr(60))
    sText = Replace(sText, ">"  , Chr(62))
    sText = Replace(sText, "&" , Chr(38))
    sText = Replace(sText, " ", Chr(32))
    For I = 1 to 255
        sText = Replace(sText, "&#" & I & ";", Chr(I))
    Next
    HTMLDecode = sText
End Function


Action = Request.QueryString("Action")

If Action = "Next" then
 
   'Dim x
   'For x = 1 to Request.Form.Count
        'Response.Write Request.Form.Key(x) & ": "
        'Response.Write Request.Form.Item(x) & "<br />"
   'Next
   'response.End
   
   
    txtBriefDescriptionOfScope = HTMLDecode(request.form("BriefDescriptionOfScope"))
   

    Set adocmd = Server.CreateObject("ADODB.Command")
    With adocmd
        .ActiveConnection = objConn
        .CommandText = "p_PP_InsertProject2"
        .CommandType = adCmdStoredProc    
        .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput,2, 4)
        .Parameters.Append .CreateParameter("@BriefDescriptionOfScope", adLongVarWChar,adParamInput,-1, txtBriefDescriptionOfScope)      
        .Execute, , adExecuteNoRecords
    End With
    Set adocmd = Nothing

    response.Redirect("PPForm_3.asp")

End If

Cannot figure out what could be wrong.
0
 
LVL 14

Expert Comment

by:huji
ID: 19599318
You can get the value of a variable (named var) in your code by adding this line in the appropriate place:

response.Write var : response.End

the response.End part makes sure the results are shown even if an error is on one of the lines of code which are after this line.

Use this trick, and ensure the variables based on which you are creating parameters, do have the desired values. Let me know as well
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 19599356
Yep that is all working I am using

Dim x
  For x = 1 to Request.Form.Count
        Response.Write Request.Form.Key(x) & ": "
        Response.Write Request.Form.Item(x) & "<br />"
   Next
   response.End

to print out all variables so far im getting

hiddenClientID:
hiddenContactID:
hiddenDesignPhaseID:
txtBriefDescriptionOfScope: gsddfgsdf

which shows the variable is getting passed.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 19599543
hmm....now, I was told that with using parameterized queries that you don't need to do any cleaning....

what you posted...are those top 3 purposfully left blank, or is that what is printed to the page?
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 19599930
Yeah they are purposely left blank, ignore those they are just hidden fields for later.
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 19599990
here is my stored proc

CREATE  PROCEDURE [dbo].[p_PP_InsertProject2]

@ID int,
@BriefDescriptionOfScope ntext

AS

UPDATE tblProjects
SET BriefDescriptionScope  = @BriefDescriptionOfScope
WHERE id = @ID
GO
0
 
LVL 25

Expert Comment

by:kevp75
ID: 19600009
txtBriefDescriptionOfScope = HTMLDecode(request.form("txtBriefDescriptionOfScope"))
0
 
LVL 25

Accepted Solution

by:
kevp75 earned 2000 total points
ID: 19600049
according to your question>>>
"
POST Data:
hiddenClientID=&hiddenContactID=&hiddenDesignPhaseID=&txtBriefDescriptionOfScope=%3Cfont+face%3D%22verdana%2Cgeneva%22+size%3D%223%22%3E%3Cstrong%3EGenesis+provided+extensive%0D%0Asupport+to+Texaco+an . . ."

meaning....you are requesting BriefDescriptionOfScope, instead of txtBriefDescriptionOfScope which is what is shown in your POST data...
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 19600069
Dear oh Dear... Cheers kev!
0
 
LVL 25

Expert Comment

by:kevp75
ID: 19600811
;)

you know...i kept missing it too, even after re-reading (usually I tend to skip the question, and go to the posts, just to see what everyone else has tried.) but this one time...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

810 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