Microsoft Knowledge Base Archive

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.

↑ Back to the top

More information

The steps in this article are similar to the samples in the Microsot Knowledge Base articles listed in the "References" 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 "References" 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 "%." 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=<UID> and password=<strong password> to the correct values before you run this code. Make sure that <UID> 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 "<?xml version='1.0' ?>"
    stSQLXML.WriteText "<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
    stSQLXML.WriteText "    <sql:header>"
    stSQLXML.WriteText "        <sql:param name='ProdName'>%</sql:param>"
    stSQLXML.WriteText "    </sql:header>"
    stSQLXML.WriteText "    <sql:query>"
    stSQLXML.WriteText "        SELECT      *"
    stSQLXML.WriteText "        FROM        Products"
    stSQLXML.WriteText "        WHERE       ProductName like '%' + @ProdName + '%'"
    stSQLXML.WriteText "        ORDER BY    ProductName"
    stSQLXML.WriteText "        FOR XML AUTO"
    stSQLXML.WriteText "    </sql:query>"
    stSQLXML.WriteText "</root>"
    
    ' 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 = "PROVIDER=SQLOLEDB;UID=<UID>;PWD=<password>;Initial Catalog=Northwind;Data Source=scout333;"
    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 = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    '   specify the command type as text.
    cmd.CommandType = adCmdText
    '   specify the output results should be sent to the output stream.
    cmd.Properties("Output Stream") = strmOut
    '   create and append the parameter value.
    cmd.Parameters.Append cmd.CreateParameter("@ProdName", adVarChar, adParamInput, 25)
    '   set the parameter value.
    cmd.Parameters("@ProdName").Value = "Chang" ' 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, "><", ">" & vbCrLf & "<")

End Sub

Check the connection string to ensure that it points to your SQL Server.
Press the F5 key to run the code and view the results in the text box.

↑ Back to the top

References

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

271620� How To Retrieve XML Data by Using a SQL XML Query in a Visual Basic Client

272266� How To Retrieve XML Data by Using a Template File in an ASP Client

For information on authorizing template queries, refer to the section titled "Using IIS Virtual Directory Management for SQL Server Utility" in SQL Books Online.

↑ Back to the top

Article ID	:	278872
Revision	:	6
Created on	:	8/30/2004
Published on	:	8/30/2004
Exists online	:	False
Views	:	484

Microsoft KB Archive Search

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

Summary

More information

References

Applies to: