Using ADO with the SQL Native Client - Displaying XML data retrieved from SQL 2005 (Page 4 of 4 )
ADO can execute queries on the SQL 2005 server with the for XML clause and retrieve XML data by the support provided by SQL Native Client for this kind of query. The code shown in the following paragraph is what is executed by the program when the command button with the caption "Display Data from SQL Server 2005" is clicked.
Private Sub Command0_Click()
Dim myStream As New ADODB.Stream
Dim myConnection As New ADODB.Connection
Dim myCommand As New ADODB.Command
'Open the connection with the SQL Native Client Provider
myConnection.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False; Initial Catalog=Northwind;" & _
"Data Source=Hodentekmysorian"
'make the opened connection the command's active connection
myCommand.ActiveConnection = myConnection
'provide a SQL statement to be executed with this command
myCommand.CommandText = "SELECT Phone, City, Country from customers " & _
"where CustomerID like 'A%' for xml auto"
'Open a stream object
myStream.Open
'associate the stream with the Outstream of the command
myCommand.Properties ("Output Stream").Value = myStream
'provide the root element for this XML (fragment)
myCommand.Properties ("xml root") = "root"
'MsgBox (myCommand.Properties(0).Name)
'execute the command with the Execute Stream flag
myCommand.Execute , , adExecuteStream
'to the beginning of stream
myStream.Position = 0
'set up the character set to be associated with the returned stream
myStream.Charset = "ISO-8859-1" Dim strxml As String
strxml = myStream.ReadText(adReadAll)
Call browse(strxml)
End Sub
The value of "myStream," which is in XML, is returned by the query. The procedure takes this string and, using the FileSytemObject, places it in the root directory of the local intranet web server running IIS 5.1. The web browser control browses this file from the web site, resulting in a display which is shown after the code.
Sub browse (ByVal stringi As String)
WebBrowser6.Visible = True
'declare a new FileSystemObject Dim fsys As New FileSystemObject
Dim outstream As TextStream
'set file location to intranet root (web root) testfile =
"C:inetpubwwwrootsqlNC.xml"
'make the stream flow to the testfile Set outstream =
fsys.CreateTextFile(testfile, True, False) 'write the outstream to
file variable stringi outstream.WriteLine (stringi)
Set outstream = Nothing 'navigate to the intranet site to display
WebBrowser6.Navigate2 ("http://localhost/sqlNC.xml")
End Sub
Summary
SQL Native Client is the brand new data access method from Microsoft with its own new dynamic link library. This extends the capabilities of ADO on the SQL Server. It so happened that with MS Access 2003, when the Web Browser Control was added, the Navigate() method was not supported by intellisense, but it still worked. You may also notice that in the display above, the encoding came out as "utf-8," whereas it was specifically programmed for "ISO-8859-1." The result however shows characters with an accent acute which is not possible with "utf-8." If one runs the code with "utf-8," one would find that the browser will throw up a parsing error.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 6 
