Nada

sConn = "Provider=SQLOLEDB.1;Data Source=sss;UId=aaaa; Pwd=vvvvv; Initial Catalog=mydb"
What does it mean if you run an output stream and get absolutely nothing by way of XML back and no messages etc.
I get the XSL output but no XML to go with it. Is ther a way of discovering if I get any XML back. My stored procedure works and gives output but not when run from the ASP.
adoCmd.Properties("Output Stream") = response
adoCmd.Execute null, null, adExecuteStream
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:61C9FE51-7680-4517-BE2D-F0CCE59EC8E8@.microsoft.com...
[snip]
> What does it mean if you run an output stream and get absolutely nothing
by way of XML back and no messages etc.
What does your query look like?
Bryant
|||By query, do you mean what I pass into the stream or the stored procedure?
The query is
<DATA xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='scheme'>10000067</sql:param></sql:header><sql:query>exec sp100pamenu_select @.ClientID</sql:query></DATA>
The code to use the above query is the same as code from another project that works against a different database that resides on a different Server.
The parameter in the stored procedure is defined as an integer, see below.
@.ClientID INT
|||http://www.bernardbelanger.com/computing/NaDa/
|||The Stored Procedure is a FOR XML query, correct?
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:EB4AA9AA-E7CF-4420-B23A-C9ED99C32AE0@.microsoft.com...
> By query, do you mean what I pass into the stream or the stored procedure?
> The query is
> <DATA xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param
name='scheme'>10000067</sql:param></sql:header><sql:query>exec
sp100pamenu_select @.ClientID</sql:query></DATA>
> The code to use the above query is the same as code from another project
that works against a different database that resides on a different Server.
> The parameter in the stored procedure is defined as an integer, see below.
> @.ClientID INT
>
|||Yes, it uses :
FOR XML EXPLICIT
GO
|||Oh, in the template header you define "scheme" as the parameter name but in
the call you use "@.ClientID". Why is that?
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:07BA7839-023F-49B9-B266-DCB72FA573CE@.microsoft.com...
> Yes, it uses :
> FOR XML EXPLICIT
> GO
>
|||Indirectly you fixed this.
When you mentioned the parameter I realised that it should have the same name as the name given in the stored procedure. It still didn't fix the problem but caused me to look more closely at the query.
The reason I got no XML back was because in the query I had the stored procedure name as sp100pamenu_select when it is in fact sp100pamenus_select.
I think that the stream should report some sort of error in this situation Rather than just giving no xml back it would be nice if the ADO responded by giving an error stating that the procedure does not exist in the database. Does anyone have an example
of an ADO stream with error handling built in?
Thank you very much for the help.
|||Glad I could help by not helping.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:A6280EE6-D769-4A50-9558-BD4964FC8375@.microsoft.com...
> Indirectly you fixed this.
> When you mentioned the parameter I realised that it should have the same
name as the name given in the stored procedure. It still didn't fix the
problem but caused me to look more closely at the query.
> The reason I got no XML back was because in the query I had the stored
procedure name as sp100pamenu_select when it is in fact sp100pamenus_select.
> I think that the stream should report some sort of error in this situation
Rather than just giving no xml back it would be nice if the ADO responded by
giving an error stating that the procedure does not exist in the database.
Does anyone have an example of an ADO stream with error handling built in?
> Thank you very much for the help.

my output parameter is being treated as an input parameter...why

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems...

ASP Code...

<asp:SqlDataSourceID="SqlDS_Form"runat="server"ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>"SelectCommand="PTN_sp_getFormDD"SelectCommandType="StoredProcedure"OldValuesParameterFormatString="original_{0}"UpdateCommand="PTN_sp_Form_Update"UpdateCommandType="StoredProcedure"OnUpdated="SqlDS_Form_Updated"OnUpdating="SqlDS_Form_Updating"DeleteCommand="PTN_sp_Form_Del"DeleteCommandType="StoredProcedure"OnDeleting="SqlDS_Form_Updating"OnDeleted="SqlDS_Form_Deleted">
<UpdateParameters><asp:ControlParameterControlID="GridView1"Name="DescID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="GridView1"Name="FormNum"PropertyName="SelectedValue"Type="String"/><asp:ParameterName="original_FormNum"Type="String"/><asp:ParameterDirection="InputOutput"size="25"Name="RetVal"Type="String"/></UpdateParameters>

<DeleteParameters>
<asp:ParameterName="original_FormNum"Type="String"/>
<asp:ParameterDirection="InputOutput"Size="1"Name="NewRetVal"Type="Int16"/>
</DeleteParameters>
</asp:SqlDataSource>

Code Behind:

protectedvoid SqlDS_Form_Deleted(object sender,SqlDataSourceStatusEventArgs e)
{
if (e.Exception ==null)
{ string strRetVal = (String)e.Command.Parameters["@.NewRetVal"].Value.ToString();
.....................

Stored Procedure:

CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (@.original_FormNumnvarchar(20), @.NewRetValINT OUTPUT )ASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDDECLARE @.stoptransvarchar(5), @.AvailFoundint, @.AssignedFoundintSet @.stoptrans ='NO'/* ------- Search PART #1 ---------------- */SET @.AvailFound = (SELECTCOUNT(*)FROM dbo.AvailableNumberWHERE dbo.AvailableNumber.FormNum = @.original_FormNum )SET @.AssignedFound = (SELECTCOUNT(*)FROM dbo.AssignedNumberWHERE dbo.AssignedNumber.FormNum=@.original_FormNum )IF @.AvailFound > 0OR @.AssignedFound > 0/* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/--This means the delete can't happen.........BEGINIF @.AssignedFound > 0AND @.AvailFound = 0BEGINSET @.NewRetVal = 1ENDIF @.AssignedFound > 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 2ENDIF @.AssignedFound = 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 3ENDENDELSEBEGINDELETE FROM dbo.FormWHERE dbo.Form.FormNum=@.original_FormNumSET @.NewRetVal = 0--Successful deletionENDGO

------------------

When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

Not sure whether it will help, but try to set the Direction of NewRetVal to Output instead of IntputOutput:

<asp:ParameterDirection="Output"Size="1"Name="NewRetVal"Type="Int16"/>

You may also take a look at this article:

Input and Output Parameters, and Return Values

|||It could also be because the type doesn't match. A SQL int is 32-bits, and you've specified a 16-bit int.