Solved

Multiline Textbox to SQL Server via XML

Posted on 2003-10-22
16
1,359 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 143

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 143

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 143

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
 

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 143

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reading the Contents of a Directory In Access VBA 5 87
vb6 connector to mongodb 2 138
Error with a code discussed on this page 5 38
Collapse and expand table in Word 2010 2 90
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

685 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