Microsoft KB Archive/278872

= How To Specify Parameters for an XML Template Query from Visual Basic =

Article ID: 278872

Article Last Modified on 8/30/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Professional Edition

-



This article was previously published under Q278872



SUMMARY
The functionality to specify parameters for a T-SQL or XPath query is provided by enhancements in ActiveX Data Objects (ADO) 2.6. This article presents a sample of how to specify parameters to a templated query against SQL Server 2000 from an MDAC 2.6 client.



MORE INFORMATION
The steps in this article are similar to the samples in the Microsot Knowledge Base articles listed in the &quot;References&quot; section. Each of these samples demonstrates various ways to retrieve an XML stream from SQL Server. The Active Server Pages (ASP) sample is mentioned primarily as a reference for converting a Visual Basic sample into an ASP solution.

The following technique demonstrates how to post a template file that contains either a T-SQL or XPath query that accepts parameters. The 2.6 version of the ADODB.Command object introduced the NamedParameters property to support this functionality. The NamedParameters property works for non-XML queries when a command that calls a stored procedure with accept parameters is run.

Note The SQL Server to which the template request is being posted must allow template queries; otherwise, this sample does not work as described. See the &quot;References&quot; section for information on authorizing template queries.

The template shown in the following code accepts a parameter that is named ProdName for the ProductName field. Note that the parameter in the template file is given a default value of &quot;%.&quot; Therefore, if no values are supplied for the parameter, all the records are returned.  Create a new Standard EXE project in Visual Basic. Form1 is created by default. On the Project menu, select References, and make a reference to Microsoft ActiveX Data Objects 2.6. Add one command button. Name it cmdTestIt and change the caption to Test. Add a text box. Name it txtResults and size it so that it is large enough to display the output.  Paste the following code into the form's code window:

Note You must change User ID= and password= to the correct values before you run this code. Make sure that  has the appropriate permissions to perform this operation on the database. Private Sub cmdTestIt_Click Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim strmOut As ADODB.Stream Dim stSQLXML As New ADODB.Stream

stSQLXML.Open stSQLXML.WriteText &quot;&quot; stSQLXML.WriteText &quot;<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>&quot; stSQLXML.WriteText &quot;   <sql:header>&quot; stSQLXML.WriteText &quot;       <sql:param name='ProdName'>%</sql:param>&quot; stSQLXML.WriteText &quot;   </sql:header>&quot; stSQLXML.WriteText &quot;   <sql:query>&quot; stSQLXML.WriteText &quot;       SELECT      *&quot; stSQLXML.WriteText &quot;       FROM        Products&quot; stSQLXML.WriteText &quot;       WHERE       ProductName like '%' + @ProdName + '%'&quot; stSQLXML.WriteText &quot;       ORDER BY    ProductName&quot; stSQLXML.WriteText &quot;       FOR XML AUTO&quot; stSQLXML.WriteText &quot;   </sql:query>&quot; stSQLXML.WriteText &quot; &quot; ' create and open a new stream for the command output. Set strmOut = New ADODB.Stream strmOut.Open ' create and set the connection for the Northwind database. Set cn = New ADODB.Connection cn.ConnectionString = &quot;PROVIDER=SQLOLEDB;UID=<UID>;PWD= ;Initial Catalog=Northwind;Data Source=scout333;&quot; cn.Open '  create the command needed to run the query. '  set the respective properties as needed. Set cmd = New ADODB.Command cmd.ActiveConnection = cn   '   specify namedparameters should be used, if not set to    '   true, the parameter values will not be used as expected '  and the default values will be used instead. cmd.NamedParameters = True '  must set the command dialect. cmd.Dialect = &quot;{5D531CB2-E6Ed-11D2-B252-00C04F681B71}&quot; '  specify the command type as text. cmd.CommandType = adCmdText '  specify the output results should be sent to the output stream. cmd.Properties(&quot;Output Stream&quot;) = strmOut '  create and append the parameter value. cmd.Parameters.Append cmd.CreateParameter(&quot;@ProdName&quot;, adVarChar, adParamInput, 25) '  set the parameter value. cmd.Parameters(&quot;@ProdName&quot;).Value = &quot;Chang&quot; ' Trim(txtCustomerID) '  must set the position in the steam back to the beginning, '  otherwise, the stream is position as the end of the last write. stSQLXML.Position = 0 '  set the comm you are going to run. Set cmd.CommandStream = stSQLXML '  run the command cmd.Execute, , adExecuteStream '  reset the stream position in the output stream back to the beginning. '  not doing so will make it appear that the command returned no data. strmOut.Position = 0 '  format the xml some to see the output a little better. txtResults.Text = Replace(strmOut.ReadText, &quot;><&quot;, &quot;>&quot; & vbCrLf & &quot;<&quot;)

End Sub </li> Check the connection string to ensure that it points to your SQL Server.</li> Press the F5 key to run the code and view the results in the text box.</li></ol>

<div class="references_section">