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

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.

1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
2. On the Project menu, select References, and make a reference to Microsoft ActiveX Data Objects 2.6.
3. Add one command button. Name it cmdTestIt and change the caption to Test.
4. Add a text box. Name it txtResults and size it so that it is large enough to display the output.
5. 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
   					

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