Retrieve Recordset into Streams through XML template queries??

Retrieve Recordset into Streams through XML template queries??

Post by Hung Yeun » Fri, 29 Jun 2001 00:58:30



I have the following code that executes an XML template
query and returns the data into a stream.
Unfortunately it dont work completely... :(

I keep getting an error when i try to assign the output
stream to the command object.
The error i get when running it is:

"Item cannot be found in the collection corresponding to
the requested name or ordinal"
at the line oCmd.Properties("Output Stream") = strmOut

I have checked the documentation on MSDN and have received
articles from 15seconds that this property exists!?
I have been referencing Microsoft ActiveX Data objects 2.6
and 2.5....Both give me the same error?

Any ideas would be greatly appreciated!!

Cheers,

Hung

==========================================================
VB Code:
==========================================================
Dim oConn As New ADODB.Connection
    Dim oCmd As New ADODB.Command
    Dim oRs As New ADODB.Recordset
    Dim sTemplate As String
    Dim strmIn As New ADODB.Stream
    Dim strmOut As New ADODB.Stream

    sTemplate = "d:\xml\xml.txt"

    oConn.ConnectionString
= "Provider=SQLOLEDB;server=scottishp3;Initial
Catalog=Northwind;User ID=sa;Password=password"
    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

    With strmIn
        .Open
        .Charset = "UTF-8"
        .LoadFromFile (sTemplate)
        .Position = 0
    End With

    Set oCmd.CommandStream = strmIn

    strmOut.Open
    strmOut.Position = 0
    strmOut.Charset = "UTF-8"

    oCmd.Properties("Output Stream") = strmOut
    oCmd.Execute , , adExecuteStream

    MsgBox strmOut.ReadText

==========================================================
xml file: xml.txt
==========================================================
<?xml version="1.0" ?>
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>select customerid TITLE, contactname ARTIST,
city from Customers CD FOR XML auto, elements</sql:query>
</ROOT>

 
 
 

Retrieve Recordset into Streams through XML template queries??

Post by Bryant Like » Fri, 29 Jun 2001 05:59:02



Quote:> I have the following code that executes an XML template
> query and returns the data into a stream.
> Unfortunately it dont work completely... :(

> I keep getting an error when i try to assign the output
> stream to the command object.
> The error i get when running it is:

> "Item cannot be found in the collection corresponding to
> the requested name or ordinal"
> at the line oCmd.Properties("Output Stream") = strmOut

The Command Properties collection is provider based. In order to use the
"Output Stream" property you must be using the SQLOLEDB provider. I noticed
that you *are* using this provider, however, you never set the command
connection to your connection object. So once you do this everything should
work properly.

Just add oCmd.ActiveConnection = oConn before you set the dialect.

--
Bryant

 
 
 

Retrieve Recordset into Streams through XML template queries??

Post by TRaef » Fri, 29 Jun 2001 21:49:31


I had received the same error. is strmOut declared as New ADODB.Stream and is
it then opened?

Dim strmOut As New ADODB.Stream

strmOut.Open

Then your code should work.

Let me know if this helps or not. I've been through many troubled times with
XML, ASP, SQL and VB. I'd be glad to help.

Thomas J. Raef
e-Based Solutions, Inc.

 
 
 

1. Using OLEDB Templates to Retrieve XML Data Through XPath Query

Hi,

I am using the sample found at
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272177 to
retrieve data using XSD schema and XPath. However the sample fails, I
found the error(using AtlTraceErrorRecords) to be  - "Microsoft XML
Extensions to SQL Server" Description: "XPath: unable to find
/Customers in the schema.

I used to get such an error when I was using the SQL virtual directory
to retrieve data using http. But that could be solved by upgrading the
virtual directory to version 3.

I assumed that it could be a similar problem of not able to link to
SQLXML 3 and after some searching in news groups even added into the
code, the line -

propset.AddProperty(SSPROP_SQLXMLXPROGID, OLESTR("SQLXML.3.0"));

But I still get the same error.

Can anyone, please tell me what am I doing wrong?

Kind regards,
Daya

2. Removing Non-Alpha/Non-Numeric Characters

3. Query ADO recordset created from an XML stream

4. How to point a referential integrity in a SQL table?

5. Problem using ADO stream object for result of XML template against SQL 2K

6. Figuring Percentages.

7. XML template with params via stream in ASP?

8. OpenRoad v/s Visual Basic v/s PowerBuilder

9. How to include and existing xml file in xml template query

10. Problem with ADO Stream retrieving XML from SQL 2000 in VBScript

11. Using an ADO stream to retrieve xml data from remote website

12. Problem with IE not showing the XML data retrieved with by a template

13. Convert XML Stream to ADO Recordset