asp to aspx xml page

[henslecd]

I am currently converting an ASP page to vb.net. I am not very strong in XML and I am running into some difficulties.

Can someone help me translate the following code into .Net? I have already referenced Microsoft.Data.SqlXml. I also have an idea on most of the transitions but I left that out due to clarity.

If nothing else I just need the new vb.net way of using Open, ActiveConnection, WriteText, Dialect, Execute and ReadText. Thanks for the help.

Private Function RetrieveXMLStream(objCon, sQuery)

Dim objCmd ' ADODB.Command
Dim objQueryStream ' ADODB.Stream
Dim objOutputStream ' ADODB.Stream
Dim sQ

Set objQueryStream = Server.CreateObject("ADODB.Stream&quot ' create the input stream

Set objOutputStream = Server.CreateObject("ADODB.Stream&quot ' create the output stream

Set objCmd = Server.CreateObject("ADODB.Command&quot

Set objCmd.ActiveConnection = objCon

sQ = PackageAsXML(sQuery)

objQueryStream.Open ' Open the command stream so it may be written to

objQueryStream.WriteText sQ, adWriteChar ' Set the input command stream's text with the query string

objQueryStream.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS

Set objCmd.CommandStream = objQueryStream ' Set the command object's command to the input stream set above

objCmd.Dialect = "{5D531CB2-E6ED-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query
' note: the default value for dialect -- if you don't specify it -- is "{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}"
' also note... even if you leave "Dialect" as the default, this function seems to work fine for our purposes

objOutputStream.Open ' Open the output stream so it can receive data

objCmd.Properties("Output Stream&quot = objOutputStream ' assign the command's output to the stream just opened

objCmd.Execute , , adExecuteStream ' execute the command, thus filling up the output stream.

objOutputStream.Position = 0 ' reset the output stream back to beginning-of-stream

RetrieveXMLStream = objOutputStream.ReadText(-1) ' assign the stream's output to the output variable

End Function

 

[culshaja]

You need top reference system.data.sqlclient rather than sqlxml.

Your SQL xml results MUST have a root element in them for .Net (you could get away without it in VB6 and VBScrip).

the following is an example of how to get XML from SQL Server in VB.Net:

m_cmCommandSQL.CommandType = CommandType.StoredProcedure
m_cmCommandSQL.CommandText = i_strStoredProcedure
Dim xmlDOM As XmlDataDocument = New XmlDataDocument()
xmlDOM.Load(m_cmCommandSQL.ExecuteXmlReader)

Hope this helps,

James

James Culshaw
james@miniaturereview.co.uk

http://www.miniaturereview.co.uk

http://www.caninerescue.co.uk