Solved

Multiline Textbox to SQL Server via XML

Posted on 2003-10-22
16
1,333 Views
Last Modified: 2013-12-25
Hi all,

I have a VB6 form with customer information, including a multiline text box that contains aan address.

I'm creating an XML object (using a DOMDocument30 object) containing all fields on the form.  The resultant XML data is then being passed as a parameter to a SQL Server 2000 stored procedure.  The stored procedure simply updates the relevant customer record with the values in the XML data.

When I check the record in SQL Server, the normal carriage return/line feed characters denoting the end of line have been changed to a single, square character.  This is a line feed only - the carriage return has been stripped out.

When the record is viewed in my VB application, the same thing is displayed.  How can I maintain the integrity of the data from the textbox through to the back end?

Here's my code that is used to build up the XML data from a form:

  Dim objControl As Control, objCommand As ADODB.Command
  Dim sDataField$, varDataValue
  Dim objDom As DOMDocument30, objRoot As IXMLDOMNode, objNode As IXMLDOMNode, objChild As IXMLDOMElement

  With frmCurrent
    Set objDom = New DOMDocument30
    objDom.async = False
    objDom.preserveWhiteSpace = True
    Set objRoot = objDom.createNode("element", "change", "")
    objDom.appendChild objRoot
    Set objNode = objDom.createNode("element", "record", "")
    objDom.documentElement.appendChild objNode
   
    For Each objControl In frmData.Controls
      sDataField = objControl.DataField
      Set objChild = objDom.createElement(sDataField)
      varDataValue = objControl
      objChild.Text = varDataValue
      objNode.appendChild objChild
    Next
   
    Set objCommand = New ADODB.Command
    objCommand.ActiveConnection = g_ADOConn 'global ADO connection already established
    objCommand.CommandText = "exec sp_upd_customer '" & objDom.xml & "'"
    objCommand.CommandType = adCmdText
    objCommand.Execute
   
    Set objCommand = Nothing
    Set objChild = Nothing
    Set objNode = Nothing
    Set objDom = Nothing


Here's and example of the XML it generates (the only crlf characters are in the address, the others are from this website word wrapping):

<change><record><VAT_Code>S</VAT_Code><Contact>contact</Contact><Fax>1234-5678</Fax><Telephone>4444-5555</Telephone><Address>Brimington Road
Second Line of Address
Chesterfield
Derbyshire</Address><Name>Customer Name</Name><Customer>0019/01</Customer><Postcode>S41 7UG</Postcode><Currency>GBP</Currency><Email>e-mail</Email><Website>www.website.co.uk</Website><Head_Office></Head_Office><Credit_Stop>1</Credit_Stop><Credit_Terms>25</Credit_Terms><Credit_Limit>125000</Credit_Limit></record></change>


And here's the stored procedure sp_upd_customer that I'm using to update the fields:

DECLARE @hDoc int  
exec sp_xml_preparedocument @hDoc OUTPUT, @sXMLData

UPDATE Customers
SET

  [Name]=XMLRecord.[Name],
  Address=XMLRecord.address,
  Telephone=XMLRecord.Telephone,
  Fax=XMLRecord.Fax,
  Contact=XMLRecord.Contact,
  VAT_Code=XMLRecord.VAT_Code,
  Postcode=XMLRecord.Postcode,
  Currency=XMLRecord.Currency,
  Email=XMLRecord.Email,
  Website=XMLRecord.Website,
  Head_Office=XMLRecord.Head_Office,
  Credit_Stop=XMLRecord.Credit_Stop,
  Credit_Terms=XMLRecord.Credit_Terms,
  Credit_Limit=XMLRecord.Credit_Limit

FROM OPENXML(@hDoc, 'change/record', 2)
  WITH Customers XMLRecord
WHERE Customers.Customer = XMLRecord.Customer

EXEC sp_xml_removedocument @hDoc  
0
Comment
Question by:Frantic_Orange
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 8

Expert Comment

by:MYLim
ID: 9603739
for multipleline text box in VB6,i will set :
-multiline = true
-scrollbars = 2

can you set like that ?
0
 

Author Comment

by:Frantic_Orange
ID: 9605026
Thanks for the reply but I think you missed the point of the question.

I already know how to make a textbox show multiline, the address is entered on screen and looks perfect.

It's only when it's submitted to the database (via the XML code above) that it gets the line feeds stripped out.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 9605040
I had already seen another q on EE about a similar problem, and a solution was not found.
The only way I see is that on your VB front-end, when reading the XML, you could replace the vbLf by vbCrLf before displaying...
Hope this helps
0
 

Author Comment

by:Frantic_Orange
ID: 9605083
angelIII: Yes, I thought of that as well.

The only problem with that is the data will still look incorrect when people query the database directly, i.e. in custom made reports or queries, etc.
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9605207
After i have read your question carefully,i have figure it out you may need to try
'Replace' command when u read data,but seem still no other way to control data insert to your back end.

Private Function cleanse(dirty As String) As String  
'THIS FUNCTION WILL ESCAPE ALL SINGLE QUOTE CHARACTERS IN AN EFFORT
'TO PREVENT SQL INJECTION ATTACKS. IT IS RECCOMENDED THAT ALL TAINTED DATA BE  
'PASSED THROUGH THIS FUNCTION PRIOR TO BEING USED IN DYNAMIC SQL QUERIES.  
'  
'WRITTEN BY: MIKE HILLYER  
'LAST MODIFIED: 14JUN2003  
      cleanse = Replace(dirty, "' " ,  " \' " )  
'CLEVER HACKERS COULD PASS \' TO THIS FUNCTION, WHICH WOULD BECOME \\'  
' \\' GETS INTERPRETED AS \', WITH THE \ BEING IGNORED AND THE ' GETTING  
'INTERPRETED, THUS BYPASSING THIS FUNCTION, SO WE SHALL LOOP UNTIL WE ARE LEFT  
'WITH JUST \' WHICH ESCAPES THE QUOTE, LOOP IS NEEDED BECAUSE A HACKER COULD TYPE  
' \\\' IF WE SIMPLY CHECKED FOR \\' AFTER DOING THE INITIAL REPLACE.  
      Do While InStr(cleanse, "\\' " )  
            cleanse = Replace(cleanse, "\\' " ,  " \' " )  
      Loop  
End Function
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 9605213
I could reproduce the problem, and located the problem being the xml_prepare_document stored procedure "eating" the cr.
One workaround could be to add this after the update...

UPDATE Customers
SET

  [Name]=XMLRecord.[Name],
  Address=replace(XMLRecord.address, char(10), char(13)+char(10)),
  Telephone=XMLRecord.Telephone,
  Fax=XMLRecord.Fax,
  Contact=XMLRecord.Contact,
  VAT_Code=XMLRecord.VAT_Code,
  Postcode=XMLRecord.Postcode,
  Currency=XMLRecord.Currency,
  Email=XMLRecord.Email,
  Website=XMLRecord.Website,
  Head_Office=XMLRecord.Head_Office,
  Credit_Stop=XMLRecord.Credit_Stop,
  Credit_Terms=XMLRecord.Credit_Terms,
  Credit_Limit=XMLRecord.Credit_Limit

FROM OPENXML(@hDoc, 'change/record', 2)
  WITH Customers XMLRecord
WHERE Customers.Customer = XMLRecord.Customer


CHeers
0
 

Author Comment

by:Frantic_Orange
ID: 9605319
angelIII: This is looking better - I incorrectly assumed that the problem was being introduced in the XML code rather than in the stored procedure.  Well spotted.

The only problem now is that leading blank lines in the textbox are being stripped out.  I thought the objDom.preserveWhiteSpace = True line would stop this from happening.

Maybe I need to use the attribute xml:space="preserve" but I'm a bit of a novice at XML.  If this will help me, how can I adjust my XML creation code to include this attribute?

Thanks.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 9605339
I never really used XML neither, so I won't be of much help about that...
Anyway, I assume that PreserveWhiteSpace or xml:spage=preserve only preserver spaces " ", but not carrage returns or line feeds...
CHeers
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Frantic_Orange
ID: 9605625
The XML docs specify that white space actually includes carriage return, new line and tab characters but I'll probably have to ask another question specifically relating to this.

Submit a proposed answer angelIII and I'll accept it as my original question has been answered.

Thanks for your help.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 9605637
In fact, it is no longer possible to submit "answer" at EE since some time...
On the other hand, you can accept ANY (except your own) comment, and even split points...

CHeers
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9610842
Frantic_Orange,

angelIII (as usual) is on track.  XML will "eat" any whitespace (exactly the same way that HTML does) so there is no real fix there and has nothing to do with the way that sp_xml_preparedocument is processing your XML document (it is after all using MSXML 3.0).  There may be a workaround, but is there any chance you can split the address into separate nodes.  This would be the best solution.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9610927
Also,

>>I thought the objDom.preserveWhiteSpace = True line would stop this from happening.
Maybe I need to use the attribute xml:space="preserve" but I'm a bit of a novice at XML.<<

You need to understand that you are not sending an XML document to the Stored Procedure, but rather sending a string representation to the Stored Procedure which in turn will load it to process with sp_xml_preparedocument.  So the fact that you are using preserveWhiteSpace is irrelevant.

>>When I check the record in SQL Server, the normal carriage return/line feed characters denoting the end of line have been changed to a single, square character.  This is a line feed only - the carriage return has been stripped out.
When the record is viewed in my VB application, the same thing is displayed. How can I maintain the integrity of the data from the textbox through to the back end?<<

One cheesy workaround you can do (if you are sure it is preserving the line feed) is replace the line feed character with CRLF, as in:

Declare @LF char(1),
            @CRLF char(2)
Set @LF = CHAR(10)
Set @CRLF = @LF + CHAR(13)
Select [Name],
          Replace(Address, @LF, @CRLF) As Address
          Telephone,
          Fax,
          Contact,
          VAT_Code,
          Postcode,
          Currency,
          Email,
          Website,
          Head_Office,
          Credit_Stop,
          Credit_Terms,
          Credit_Limit
From   Customers
Where  'some condition goes here'

Anthony


Anthony
0
 

Author Comment

by:Frantic_Orange
ID: 9612645
acperkins: Things are getting clearer now - thanks.  You suggested the same thing that angelIII did a few posts back and that's the solution I implemented.

A bit cheesy, as you say, but it works fine except that leading carriage return/line feeds are completely stripped out before the stored procedure receives the data so the Replace command doesn't have a chance in that case.  For the rest of the time it works fine though.
0
 

Author Comment

by:Frantic_Orange
ID: 9612659
angelIII: The 'Accept' button was way off screen to the right - that's why I didn't see it.  Comment accepted as answer - thanks to you and acperkins for the help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9614048
>>You suggested the same thing that angelIII did a few posts back <,
I trust you also saw my suggestion regarding splitting the address into separate nodes.

>>but it works fine except that leading carriage return/line feeds are completely stripped out <<
That was what I suspected XML would do (and what it is supposed to do), except that you had indicated that it kept the Line Feed character.
"When I check the record in SQL Server, the normal carriage return/line feed characters denoting the end of line have been changed to a single, square character.  This is a line feed only - the carriage return has been stripped out."

Anthony
0
 

Author Comment

by:Frantic_Orange
ID: 9614651
I did see the suggestion about splitting the address but I'm working on a system conversion and there are lots of fields like this.  Although splitting would seem sensible for new projects, there are just too many implications in this case, especially when the deadline is looming.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now