Visual Basic Q&A

Symptoms
The Stream object introduced in ActiveX Data Objects (ADO) 2.5 can be used to greatly simplify the code that needs to be written to access and modify Binary Large Object (BLOB) data in a SQL Server Database. The previous versions of ADO [ 2.0, 2.1, and 2.1 SP2 ] required careful usage of the GetChunk and AppendChunk methods of the Field Object to read and write BLOB data in fixed-size chunks from and to a BLOB column. An alternative to this method now exists with the advent of ADO 2.5. This article includes code samples that demonstrate how the Stream object can be used to program the following common tasks: Save the data stored in a SQL Server Image column to a file on the hard disk.Move the contents of a .gif file to an Image column in a SQL Server table.
Resolution
The following code samples are based on the data stored in the pub_info table in the SQL Server 7.0 pubs sample database. You need to modify the ADO connection string to point to your SQL Server installation. Example 1 : Saving the Data in a SQL Server Image Column to a File on the Hard Disk The code in this example opens a recordset on the pub_info table in the pubs database and saves the binary image data stored in the logo column of the first record to a file on the hard disk, as follows: Open a new Standard EXE Visual Basic project.On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.Place a CommandButton control on Form1.Make the following declarations in the form’s General declarations section:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream Cut and paste the following code into the Click event of the CommandButton that you added to the form:

Set cn = New ADODB.Connectioncn.Open “Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>”Set rs = New ADODB.Recordsetrs.Open “Select * from pub_info”, cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.Write rs.Fields(“logo”).Valuemstream.SaveToFile “c:\publogo.gif”, adSaveCreateOverWriters.Closecn.Close Save and run the Visual Basic project.Click the CommandButton to save the binary data in the logo column of the first record to the file c:\publogo.gid. Look for this file in Windows Explorer and open it to view the saved image.
The code in this example declares an ADODB Stream object and sets its Type property to adTypeBinary to reflect that this object will be used to work with Binary data. Following this, the binary data stored in the logo column of the first record in the pub_info table is written out to the Stream object by calling its Write method. The Stream object now contains the binary data that is saved to the file by calling its SaveToFile method and passing in the path to the file. The adSaveCreateOverWrite constant passed in as the second parameter causes the SaveToFile method to overwrite the specified file if it already exists.Example 2 : Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table The code in this example saves an image stored in a .gif file to the logo column in the first record of the pub_info table by overwriting its current contents, as follows: Open a new Standard EXE Visual Basic project.On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.Place a CommandButton on Form1. Make the following declarations in the form’s General declarations section:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream Cut and paste the following code in the Click event of the CommandButton that you added to the form:

Set cn = New ADODB.Connectioncn.Open “Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>”Set rs = New ADODB.Recordsetrs.Open “Select * from pub_info”, cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.LoadFromFile “<path to .gif file>”rs.Fields(“logo”).Value = mstream.Readrs.Updaters.Closecn.Close Save and run the Visual Basic project.Click on the CommandButton to run the code to stream the contents of the .gif file to the ADO Stream object, and save the data in the Stream to the logo column in the first record of the recordset.Verify that the image in the logo column has been modified by using the code in Example 1.

Symptoms
Queries that result in a large amount of XML data being returned through the ReadText method of the ActiveX Data Object (ADO) Stream object may take a great deal of time to execute; if this is done in a COM+ component that is invoked from an ASP page, the user’s session may time out.
Resolution
ADO converts Stream object data from UTF-8 encoding to Unicode; the frequent memory reallocation involved in conversion of such a large quantity of data at once is quite time-consuming.

Symptoms
This sample in this article demonstrates how to retrieve an XML document from SQL Server 2000 by using a template file that contains parameters.More specifically, a query is issued against SQL Server 2000 by using an ActiveX Data Objects (ADO) 2.6 command stream. This query fetches the XML DataStream.
This sample reads the “products.xml” template file from disk and sets thecommand text for the ADODB.Command object for the query to run.
You must set the following properties for the Properties collection of the ADODB.Command object:Base Path. This property establishes the location of the template and the Extensible Style Sheet Language (XSL) files.Output Stream. This property designates where the resulting XML data stream will be piped.Dialect. The dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. By setting the command language dialect, you specify how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ADO. The dialect is specified by a globally unique identifier (GUID) and is set by using the Dialect property of the Command object.XSL. This property transforms the XML document.NOTE: Output Stream may be any object that supports an IStream or ISequentialStream interface. Objects that support the IStream interfaces are the ADODB.Stream object, the Microsoft Internet Information Services (IIS) 5.0 Response object, and a Microsoft XML (MSXML) DomDocument object.
Resolution
Create a new Standard EXE project in Visual Basic. Form1 is created by default.On the Project menu, click Components, and then add a reference to both Microsoft Internet Controls and Microsoft ActiveX Data Objects 2.6.Add a Web browser to the form, name it WebBrowser1, and then size it accordingly.Add a frame to the form, and place two option buttons in it. Name the first button optXSLYes with the caption XSL Yes, and then name the second button optXSLNo with the caption XSL No.Add two command buttons. Name the first button cmdTestIt with the caption Test, and then name the second button cmdExitProgram with the caption Exit.Paste the following Visual Basic code in the Code window of the form:

Option ExplicitConst DBGUID_DEFAULT As String = “{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}” Private Sub cmdExitProgram_Click()Unload MeEndEnd SubPrivate Sub cmdTestIt_Click()Dim cn As New ADODB.ConnectionDim cmd As New ADODB.CommandDim cmdStream As New ADODB.StreamDim cmdOutput As New ADODB.StreamDim txtOutputFileName As String’ open the database connectioncn.Open “provider=sqloledb;data source=.;initial catalog=northwind;uid=sa;pwd=”‘ open the command stream that will eventually contain the templated querycmdStream.Open’ set the character set to asciicmdStream.Charset = “ascii”‘ set the command stream type to text, not binary.cmdStream.Type = adTypeText’ read the template file from disk into the command stream to executecmdStream.LoadFromFile App.Path & “\products.xml”‘ set the command connectionSet cmd.ActiveConnection = cn’ set the command’s command stream to hook the template query to the command you want to run.Set cmd.CommandStream = cmdStream’ set the command dialectcmd.Dialect = DBGUID_DEFAULT’ open the output stream to receive the results for the command execute.cmdOutput.Open’ set the base path for where the template file resides.’ Currently, this must point to a file on disk. Remote templates via’ http://…template.xml” are not allowed.cmd.Properties(“Base Path”) = App.Path’ set up the output stream that will receive the output of the command execute.cmd.Properties(“Output Stream”) = cmdOutput’ set the XSL to process if the user requested the output to be fixed.If optXSLYes Then’ set the file name for the XSL: this inherits the “base path” settingcmd.Properties(“XSL”) = “products.xsl”‘ set the file extension to “.htm”, mainly so the Web browser displays’ set the output as a table.Try with a “.xml” extension… what is displayed?txtOutputFileName = App.Path & “\queryout.htm”ElsetxtOutputFileName = App.Path & “\queryout.xml”End If’ execute the command stream with the settings specified above.cmd.Execute , , adExecuteStream’ position the stream back to the beginning: the “file position” in the’stream will be at the end of the stream.Writing to a stream will append’on the end and only while the output from the command execute generates.cmdOutput.Position = 0’save the output to a file, this is only needed to use the navigate on the Web browser controlcmdOutput.SaveToFile txtOutputFileName, adSaveCreateOverWrite’ Navigate/display the results of the command executes.WebBrowser1.Navigate txtOutputFileName’ close and clean-up the objects used.cmdOutput.ClosecmdStream.Closecn.CloseSet cmdOutput = NothingSet cmdStream = NothingSet cmd = NothingSet cn = NothingEnd Sub To create a template file, create a new text file, paste the following template into the file, and then save it with a name of products.xml:

<?xml version=’1.0′ ?><root xmlns:sql=”urn:schemas-microsoft-com:xml-sql”><sql:header><sql:param name=”ProdName”>%</sql:param></sql:header><sql:query>SELECT*FROMProductsWHEREProductName like ‘%’ + @ProdName + ‘%’ORDER BYProductNameFOR XML AUTO</sql:query></root> As an option, create a file named products.xsl with the following code to transform the output:

<?xml version=’1.0′ ?><xsl:stylesheet xmlns:xsl=”http://www.w3.org/TR/WD-xsl”><xsl:template match=”/”><html><head><title>MSDN ADO Product Sample with SQL Server 2000 Features</title><base href=”http://localhost/3tier/” /></head><body><table border=”0″ cellPadding=”1″ cellSpacing=”1″width=”100%”style=”COLOR:black;FONT-FAMILY:Arial;FONT-SIZE:12pt.;FONT-WEIGHT:500″><tr bgColor=”#336699″ align=”center”><TD><P ><STRONG><FONT color=”white” size=”2″>Product ID:</FONT></STRONG></P></TD><TD><P ><STRONG><FONT color=”white” size=”2″>Product Name:</FONT></STRONG></P></TD><TD><P ><STRONG><FONT color=”white” size=”2″>Unit Price:</FONT></STRONG></P></TD><TD><P ><STRONG><FONT color=”white” size=”2″>Units In Stock:</FONT></STRONG></P></TD><TD><P ><STRONG><FONT color=”white” size=”2″>Restock Level:</FONT></STRONG></P></TD><TD><P ><FONT color=”white” size=”2″><STRONG>Units On Order:</STRONG></FONT></P></TD></tr><xsl:for-each select=”root/Products”><tr style=”COLOR: black; FONT-FAMILY: Arial; FONT-SIZE: 0.8em; FONT-WEIGHT: 500″><td bgColor=”#F0F0F0″><xsl:value-of select=”@ProductID”/></td><td bgColor=”#F0F0F0″><xsl:value-of select=”@ProductName”/></td><td bgColor=”#F0F0F0″><xsl:value-of select=”@UnitPrice”/></td><td bgColor=”#F0F0F0″><xsl:value-of select=”@UnitsInStock”/></td><td bgColor=”#F0F0F0″><xsl:value-of select=”@ReorderLevel”/></td><td bgColor=”#F0F0F0″><xsl:value-of select=”@UnitsOnOrder”/></td></tr></xsl:for-each></table></body></html></xsl:template></xsl:stylesheet>

Symptoms
If you run an ADODB command stream and specify SQL SELECT with the FOR XML AUTO clause, an XML document stream is fetched from SQL Server and displayed in the Visual Basic Intermediate window.
You must set the following properties for the Properties collection of the ADODB.Command object: Output Stream. This property designates where the resulting XML data stream will be piped.Dialect. The dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. By setting the command language dialect, you specify how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ActiveX Data Objects (ADO). The dialect is specified by a globally unique identifier (GUID) and is set by using the Dialect property of the Command object.
Resolution
Create a new Visual Basic Standard EXE. Form1 is created by default. On the Project menu, select References, and then set a reference to Microsoft ActiveX Data Objects 2.6.Place a CommandButton on Form1, and then place the following code in its click event:
Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

Private Sub Command1_Click()Dim sConn As StringDim sQuery As StringDim outStrmsConn = “Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID=<username>;Password=<strong password>;”Dim adoConn As ADODB.ConnectionSet adoConn = New ADODB.ConnectionadoConn.ConnectionString = sConnadoConn.CursorLocation = adUseClientadoConn.OpenDim adoCmd As ADODB.CommandSet adoCmd = New ADODB.CommandSet adoCmd.ActiveConnection = adoConnsQuery = “<ROOT xmlns:sql=’urn:schemas-microsoft-com:xml-sql’>”sQuery = sQuery & “<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>”’sQuery = sQuery & “</ROOT>”Dim adoStreamQuery As ADODB.StreamSet adoStreamQuery = New ADODB.Stream’Open the command stream so it may be written toadoStreamQuery.Open’Set the input command stream’s text with the query stringadoStreamQuery.WriteText sQuery, adWriteChar’Reset the position in the stream, otherwise it will be at EOS.adoStreamQuery.Position = 0′Set the command object’s command to the input stream set above.Set adoCmd.CommandStream = adoStreamQuery’Set the dialect for the command stream to be a SQL query.adoCmd.Dialect = “{5D531CB2-E6Ed-11D2-B252-00C04F681B71}”‘Create the output stream to stream the results into.Set outStrm = CreateObject(“ADODB.Stream”)outStrm.Open’Set command’s output stream to the output stream just opened.adoCmd.Properties(“Output Stream”) = outStrm’Execute the command, thus filling the output stream.adoCmd.Execute , , adExecuteStream’Position the output stream back to the beginning of the stream.outStrm.Position = 0′Create temporary string.Dim str As String’Assign the stream’s output to the temp string to format.str = outStrm.ReadText(-1)’Add a cr/lf pair for each row in the result stream.str = Replace(str, “><”, “>” & vbCrLf & “<”)Debug.Print strGoTo ByeRecError:Debug.Print Err.Number & “: ” & Err.DescriptionBye:Set adoCmd = NothingIf adoConn.State = adStateOpen ThenadoConn.CloseEnd IfSet adoConn = NothingEnd Sub Specify either the SQL 2000 Server or, if the server is on your local machine, use the period symbol (.) or (local). Note that the Immediate window of Visual Basic displays the results.

Symptoms
The Stream object introduced in ActiveX Data Objects (ADO) 2.5 can be used to greatly simplify the code that needs to be written to access and modify Binary Large Object (BLOB) data in a SQL Server Database. The previous versions of ADO [ 2.0, 2.1, and 2.1 SP2 ] required careful usage of the GetChunk and AppendChunk methods of the Field Object to read and write BLOB data in fixed-size chunks from and to a BLOB column. An alternative to this method now exists with the advent of ADO 2.5. This article includes code samples that demonstrate how the Stream object can be used to program the following common tasks: Save the data stored in a SQL Server Image column to a file on the hard disk.Move the contents of a .gif file to an Image column in a SQL Server table.
Resolution
The following code samples are based on the data stored in the pub_info table in the SQL Server 7.0 pubs sample database. You need to modify the ADO connection string to point to your SQL Server installation. Example 1 : Saving the Data in a SQL Server Image Column to a File on the Hard Disk The code in this example opens a recordset on the pub_info table in the pubs database and saves the binary image data stored in the logo column of the first record to a file on the hard disk, as follows: Open a new Standard EXE Visual Basic project.On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.Place a CommandButton control on Form1.Make the following declarations in the form’s General declarations section:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream Cut and paste the following code into the Click event of the CommandButton that you added to the form:

Set cn = New ADODB.Connectioncn.Open “Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>”Set rs = New ADODB.Recordsetrs.Open “Select * from pub_info”, cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.Write rs.Fields(“logo”).Valuemstream.SaveToFile “c:\publogo.gif”, adSaveCreateOverWriters.Closecn.Close Save and run the Visual Basic project.Click the CommandButton to save the binary data in the logo column of the first record to the file c:\publogo.gid. Look for this file in Windows Explorer and open it to view the saved image.
The code in this example declares an ADODB Stream object and sets its Type property to adTypeBinary to reflect that this object will be used to work with Binary data. Following this, the binary data stored in the logo column of the first record in the pub_info table is written out to the Stream object by calling its Write method. The Stream object now contains the binary data that is saved to the file by calling its SaveToFile method and passing in the path to the file. The adSaveCreateOverWrite constant passed in as the second parameter causes the SaveToFile method to overwrite the specified file if it already exists.Example 2 : Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table The code in this example saves an image stored in a .gif file to the logo column in the first record of the pub_info table by overwriting its current contents, as follows: Open a new Standard EXE Visual Basic project.On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.Place a CommandButton on Form1. Make the following declarations in the form’s General declarations section:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream Cut and paste the following code in the Click event of the CommandButton that you added to the form:

Set cn = New ADODB.Connectioncn.Open “Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>”Set rs = New ADODB.Recordsetrs.Open “Select * from pub_info”, cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.LoadFromFile “<path to .gif file>”rs.Fields(“logo”).Value = mstream.Readrs.Updaters.Closecn.Close Save and run the Visual Basic project.Click on the CommandButton to run the code to stream the contents of the .gif file to the ADO Stream object, and save the data in the Stream to the logo column in the first record of the recordset.Verify that the image in the logo column has been modified by using the code in Example 1.