Multiline Textbox to SQL Server via XML

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  
Frantic_OrangeAsked:
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.

MYLimCommented:
for multipleline text box in VB6,i will set :
-multiline = true
-scrollbars = 2

can you set like that ?
0
Frantic_OrangeAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Frantic_OrangeAuthor Commented:
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
MYLimCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

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
Frantic_OrangeAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Frantic_OrangeAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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
Frantic_OrangeAuthor Commented:
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
Frantic_OrangeAuthor Commented:
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
Anthony PerkinsCommented:
>>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
Frantic_OrangeAuthor Commented:
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
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
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.