473,327 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

.net loses sp result when calling executeXmlReader. please help

Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml

In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through, but the result just disappears.
----CODE START
Dim dbg As Boolean = True
'XMD = New
SqlXmlCommand(ConfigurationSettings.AppSettings("C onnectionString").ToString()) '"storedprocname"
XMD = New SqlXmlCommand("Provider=SQLOLEDB;data source=xxxx;initial
catalog=xxxx;user id=user;password=xxx")
XMD.CommandType = SqlXmlCommandType.Sql 'CommandType.StoredProcedure
XMD.CommandText = "select_xxx_xxx"
XMD.ClientSideXml = True
'XMD.RootTag = "root"
'CN.Open()
Try
' XR = CMD.ExecuteXmlReader()
'Dim mydoc As New XmlDocument
'mydoc.Load(CMD.ExecuteXmlReader().ReadInnerXml)
'Dim sux As System.Xml.XmlReader
XR = XMD.ExecuteXmlReader() ' Stream() ' XmlReader()

Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
dbg = False
Finally
If dbg Then
System.Diagnostics.Debug.WriteLine(XR.ReadInnerXml )
End If
End Try
Return XR.ReadInnerXml()

----CODE END

I've looked in sql profiler and the stored proc is definitely being run. So
the login is ok and the stored proc works fine. (In fact the whole damn thing
used to work in classic asp)

Does anyone know why the result is being lost?
Thanks

Nov 12 '05 #1
5 6763
Hi Adolf

This works fine for me against the pubs database.

SP:
CREATE PROCEDURE dbo.test
(

@test int OUTPUT
)
AS
SELECT @test = 23
SELECT TOP 1 au_lname FROM authors FOR XML AUTO
RETURN 34

Code:
Me.SqlConnection1.Open()
Dim x As Xml.XmlReader = Me.SqlCommand1.ExecuteXmlReader()
MessageBox.Show(Me.SqlCommand1.Parameters("@test") .Value)
MessageBox.Show(Me.SqlCommand1.Parameters("@RETURN _VALUE").Value)
Dim d As New Xml.XmlDocument
d.Load(x)
MessageBox.Show(d.OuterXml)
Me.SqlConnection1.Close()

Gets back the value of the output parameter, the return value, and the XML.

Nigel
"adolf garlic" wrote:
Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml

In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through, but the result just disappears.
----CODE START
Dim dbg As Boolean = True
'XMD = New
SqlXmlCommand(ConfigurationSettings.AppSettings("C onnectionString").ToString()) '"storedprocname"
XMD = New SqlXmlCommand("Provider=SQLOLEDB;data source=xxxx;initial
catalog=xxxx;user id=user;password=xxx")
XMD.CommandType = SqlXmlCommandType.Sql 'CommandType.StoredProcedure
XMD.CommandText = "select_xxx_xxx"
XMD.ClientSideXml = True
'XMD.RootTag = "root"
'CN.Open()
Try
' XR = CMD.ExecuteXmlReader()
'Dim mydoc As New XmlDocument
'mydoc.Load(CMD.ExecuteXmlReader().ReadInnerXml)
'Dim sux As System.Xml.XmlReader
XR = XMD.ExecuteXmlReader() ' Stream() ' XmlReader()

Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
dbg = False
Finally
If dbg Then
System.Diagnostics.Debug.WriteLine(XR.ReadInnerXml )
End If
End Try
Return XR.ReadInnerXml()

----CODE END

I've looked in sql profiler and the stored proc is definitely being run. So
the login is ok and the stored proc works fine. (In fact the whole damn thing
used to work in classic asp)

Does anyone know why the result is being lost?
Thanks

Nov 12 '05 #2
:) deep joy - it works

but i'm confused as to why my code didn't, but appears to do the same thing

I tried a million variations on this but none of them worked.

Thanks

"Nigel Armstrong" wrote:
Hi Adolf

This works fine for me against the pubs database.

SP:
CREATE PROCEDURE dbo.test
(

@test int OUTPUT
)
AS
SELECT @test = 23
SELECT TOP 1 au_lname FROM authors FOR XML AUTO
RETURN 34

Code:
Me.SqlConnection1.Open()
Dim x As Xml.XmlReader = Me.SqlCommand1.ExecuteXmlReader()
MessageBox.Show(Me.SqlCommand1.Parameters("@test") .Value)
MessageBox.Show(Me.SqlCommand1.Parameters("@RETURN _VALUE").Value)
Dim d As New Xml.XmlDocument
d.Load(x)
MessageBox.Show(d.OuterXml)
Me.SqlConnection1.Close()

Gets back the value of the output parameter, the return value, and the XML.

Nigel
"adolf garlic" wrote:
Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml

In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through, but the result just disappears.
----CODE START
Dim dbg As Boolean = True
'XMD = New
SqlXmlCommand(ConfigurationSettings.AppSettings("C onnectionString").ToString()) '"storedprocname"
XMD = New SqlXmlCommand("Provider=SQLOLEDB;data source=xxxx;initial
catalog=xxxx;user id=user;password=xxx")
XMD.CommandType = SqlXmlCommandType.Sql 'CommandType.StoredProcedure
XMD.CommandText = "select_xxx_xxx"
XMD.ClientSideXml = True
'XMD.RootTag = "root"
'CN.Open()
Try
' XR = CMD.ExecuteXmlReader()
'Dim mydoc As New XmlDocument
'mydoc.Load(CMD.ExecuteXmlReader().ReadInnerXml)
'Dim sux As System.Xml.XmlReader
XR = XMD.ExecuteXmlReader() ' Stream() ' XmlReader()

Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
dbg = False
Finally
If dbg Then
System.Diagnostics.Debug.WriteLine(XR.ReadInnerXml )
End If
End Try
Return XR.ReadInnerXml()

----CODE END

I've looked in sql profiler and the stored proc is definitely being run. So
the login is ok and the stored proc works fine. (In fact the whole damn thing
used to work in classic asp)

Does anyone know why the result is being lost?
Thanks

Nov 12 '05 #3
New problem

The solution you supplied fails when the stored procedure in question has
input parameters.

The result that comes back now is
Invalid command sent to ExecuteXmlReader. The command must return an Xml
result

"adolf garlic" wrote:
Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml

In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through, but the result just disappears.
----CODE START
Dim dbg As Boolean = True
'XMD = New
SqlXmlCommand(ConfigurationSettings.AppSettings("C onnectionString").ToString()) '"storedprocname"
XMD = New SqlXmlCommand("Provider=SQLOLEDB;data source=xxxx;initial
catalog=xxxx;user id=user;password=xxx")
XMD.CommandType = SqlXmlCommandType.Sql 'CommandType.StoredProcedure
XMD.CommandText = "select_xxx_xxx"
XMD.ClientSideXml = True
'XMD.RootTag = "root"
'CN.Open()
Try
' XR = CMD.ExecuteXmlReader()
'Dim mydoc As New XmlDocument
'mydoc.Load(CMD.ExecuteXmlReader().ReadInnerXml)
'Dim sux As System.Xml.XmlReader
XR = XMD.ExecuteXmlReader() ' Stream() ' XmlReader()

Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
dbg = False
Finally
If dbg Then
System.Diagnostics.Debug.WriteLine(XR.ReadInnerXml )
End If
End Try
Return XR.ReadInnerXml()

----CODE END

I've looked in sql profiler and the stored proc is definitely being run. So
the login is ok and the stored proc works fine. (In fact the whole damn thing
used to work in classic asp)

Does anyone know why the result is being lost?
Thanks

Nov 12 '05 #4
Hi Adolf

I've just tried adding an input parameter to the SP, and modified the code
and it still works...

Nigel

SP:
ALTER PROCEDURE dbo.test

(
@id int,
@test int OUTPUT
)

AS
SELECT @test = 23
SELECT Food FROM FavouriteFoods WHERE id = @id FOR XML AUTO
RETURN 34
Code:
Me.SqlConnection1.Open()
Me.SqlCommand1.Parameters("@id").Value = InputBox("Tell Me",
"Number", "2")
Dim x As Xml.XmlReader = Me.SqlCommand1.ExecuteXmlReader()
MessageBox.Show(Me.SqlCommand1.Parameters("@test") .Value)
MessageBox.Show(Me.SqlCommand1.Parameters("@RETURN _VALUE").Value)
Dim d As New Xml.XmlDocument
d.Load(x)
MessageBox.Show(d.OuterXml)
Me.SqlConnection1.Close()

"adolf garlic" wrote:
New problem

The solution you supplied fails when the stored procedure in question has
input parameters.

The result that comes back now is
Invalid command sent to ExecuteXmlReader. The command must return an Xml
result

"adolf garlic" wrote:
Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml

In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through, but the result just disappears.
----CODE START
Dim dbg As Boolean = True
'XMD = New
SqlXmlCommand(ConfigurationSettings.AppSettings("C onnectionString").ToString()) '"storedprocname"
XMD = New SqlXmlCommand("Provider=SQLOLEDB;data source=xxxx;initial
catalog=xxxx;user id=user;password=xxx")
XMD.CommandType = SqlXmlCommandType.Sql 'CommandType.StoredProcedure
XMD.CommandText = "select_xxx_xxx"
XMD.ClientSideXml = True
'XMD.RootTag = "root"
'CN.Open()
Try
' XR = CMD.ExecuteXmlReader()
'Dim mydoc As New XmlDocument
'mydoc.Load(CMD.ExecuteXmlReader().ReadInnerXml)
'Dim sux As System.Xml.XmlReader
XR = XMD.ExecuteXmlReader() ' Stream() ' XmlReader()

Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
dbg = False
Finally
If dbg Then
System.Diagnostics.Debug.WriteLine(XR.ReadInnerXml )
End If
End Try
Return XR.ReadInnerXml()

----CODE END

I've looked in sql profiler and the stored proc is definitely being run. So
the login is ok and the stored proc works fine. (In fact the whole damn thing
used to work in classic asp)

Does anyone know why the result is being lost?
Thanks

Nov 12 '05 #5
Nigel,

That's probably because you've gone back to using just a single piece of xml
in the result set (don't forget that I am returning multiple snippets of xml)

The way I managed to get this working in the end (in another posting) is this:

- use a sqlxml command
- make sure to put placeholders for each parameter in the commandtext (this
is crap as it does not appear documented) e.g. "select_bananas ?,?"
- put the result into a stream (putting it into an xmlreader fails with
invalid xml)
- load the alleged 'invalid xml' in the stream into a dom
it works

i don't understand why
thanks for trying though

i hate microsoft for their poorly documented afterthought products.
not knowing about the "?,?" wasted soooo much time
in protest i have moved to firefox and have just opened a gmail account and
am recommending everyone i know to do the same :]

Let me know if you do ever manage to get it working with a manually crafted
bit of xml.

"Nigel Armstrong" wrote:
Hi Adolf

I've just tried adding an input parameter to the SP, and modified the code
and it still works...

Nigel

SP:
ALTER PROCEDURE dbo.test

(
@id int,
@test int OUTPUT
)

AS
SELECT @test = 23
SELECT Food FROM FavouriteFoods WHERE id = @id FOR XML AUTO
RETURN 34
Code:
Me.SqlConnection1.Open()
Me.SqlCommand1.Parameters("@id").Value = InputBox("Tell Me",
"Number", "2")
Dim x As Xml.XmlReader = Me.SqlCommand1.ExecuteXmlReader()
MessageBox.Show(Me.SqlCommand1.Parameters("@test") .Value)
MessageBox.Show(Me.SqlCommand1.Parameters("@RETURN _VALUE").Value)
Dim d As New Xml.XmlDocument
d.Load(x)
MessageBox.Show(d.OuterXml)
Me.SqlConnection1.Close()

"adolf garlic" wrote:
New problem

The solution you supplied fails when the stored procedure in question has
input parameters.

The result that comes back now is
Invalid command sent to ExecuteXmlReader. The command must return an Xml
result

"adolf garlic" wrote:
Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml

In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through, but the result just disappears.
----CODE START
Dim dbg As Boolean = True
'XMD = New
SqlXmlCommand(ConfigurationSettings.AppSettings("C onnectionString").ToString()) '"storedprocname"
XMD = New SqlXmlCommand("Provider=SQLOLEDB;data source=xxxx;initial
catalog=xxxx;user id=user;password=xxx")
XMD.CommandType = SqlXmlCommandType.Sql 'CommandType.StoredProcedure
XMD.CommandText = "select_xxx_xxx"
XMD.ClientSideXml = True
'XMD.RootTag = "root"
'CN.Open()
Try
' XR = CMD.ExecuteXmlReader()
'Dim mydoc As New XmlDocument
'mydoc.Load(CMD.ExecuteXmlReader().ReadInnerXml)
'Dim sux As System.Xml.XmlReader
XR = XMD.ExecuteXmlReader() ' Stream() ' XmlReader()

Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
dbg = False
Finally
If dbg Then
System.Diagnostics.Debug.WriteLine(XR.ReadInnerXml )
End If
End Try
Return XR.ReadInnerXml()

----CODE END

I've looked in sql profiler and the stored proc is definitely being run. So
the login is ok and the stored proc works fine. (In fact the whole damn thing
used to work in classic asp)

Does anyone know why the result is being lost?
Thanks

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: David Walker | last post by:
Hi I have a Microsoft SQL database I can use (also mySQL, so if you know how to do this in mySQL that is just as useful). The database can only be accessed from webpages hosted on the same server...
0
by: Jason Nix | last post by:
I have created an Active Server Component (ASC) as a bridge between ASP and my existing C++ DLLs that we use in our PC-based program. Our intent is to re-use as much code as possible. But, when I...
1
by: Jason Nix | last post by:
I have created an Active Server Component (ASC) as a bridge between ASP and my existing C++ DLLs that we use in our PC-based program. Our intent is to re-use as much code as possible. But, when I...
3
by: Mark Miller | last post by:
I have an sp that outputs multiple xml fragments w/ no root. The sp calls individual sp's to output the correct set of data and each "type" has different fields. ex.: <LEADERBOARD...
1
by: Ross Klatte | last post by:
Okay, so he didn't. So, who cares? The important question is: How do I direct my SPUFI output to a disk file? I did this before, but now I have forgotten it. (And yet, you say Arnold is the...
1
by: randall g | last post by:
I have a stored procedure which returns multiple result sets, enclosing each in its own tag. This works in ADO but not ADO.NET, where an error is returned by ExecuteXmlReader: "Invalid command...
15
by: AussieRules | last post by:
Hi, I have a need to do two selects against to stored proc's in my SQL db. At the moment, each SP is called and two different dataset are populate. Thats two round trips to the SQL server. ...
7
by: -Lost | last post by:
I am calling setTimeout within the context of an object, and whilst this exists, it refuses to be passed along to the function I call. For example: $elemById('id').change = function() { // the...
6
by: Rich Raffenetti | last post by:
I have a form with a listview control and a textbox. When I change focus from the listview to the textbox, the highlighted item on the listview loses the highlight. That is not the behavior I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.