Return always 0.

I am trying to find out why my return from my ASP.Net page is always 0.
I have the following code:
****************************************
************
Dim objCmd as New SqlCommand("AddNewResumeCoverTemplate",objConn)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.parameters.add("@.ClientID",SqldbType.VarChar,20).value =
session("ClientID")
objCmd.parameters.add("@.Email",SqlDbType.VarChar).value = session("Email")
objCmd.parameters.add("@.ResumeTitle",SqlDbType.VarChar,45).value =
ResumeTitle.Text
objCmd.parameters.add("@.Resume",SqlDbType.text).value = ResumeText.Text
objCmd.parameters.add("@.CoverLetterTitle",SqlDbType.VarChar,45).value =
ResumeTitle.Text
objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
CoverLetter.Text
objCmd.Parameters.Add("@.errorCode", SqlDbType.Int).Direction =
ParameterDirection.Output
objConn.Open()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("@.errorCode").Value))
****************************************
************************************
*
The stored procedure essentially looks like:
****************************************
************************************
*
CREATE PROCEDURE AddNewResumeCoverTemplate
(
@.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle varChar(45),@.Resume
text,@.CoverLetterTitle varChar(45),@.CoverLetter text,@.errorCode int Output
)
AS
...
select @.errorCode = 1
return @.errorCode
GO
****************************************
************************************
**
I put the select statement there just to force @.errorCode to be 1.
But my pages trace.warn is showing it as 0 (always).
Do I have it set up correctly?
Thanks,
Tomtshad wrote:
> I am trying to find out why my return from my ASP.Net page is always
> 0.
> I have the following code:
> ****************************************
************
> Dim objCmd as New SqlCommand("AddNewResumeCoverTemplate",objConn)
> objCmd.CommandType = CommandType.StoredProcedure
> objCmd.parameters.add("@.ClientID",SqldbType.VarChar,20).value =
> session("ClientID")
> objCmd.parameters.add("@.Email",SqlDbType.VarChar).value =
> session("Email")
> objCmd.parameters.add("@.ResumeTitle",SqlDbType.VarChar,45).value =
> ResumeTitle.Text
> objCmd.parameters.add("@.Resume",SqlDbType.text).value =
> ResumeText.Text
> objCmd.parameters.add("@.CoverLetterTitle",SqlDbType.VarChar,45).value
> = ResumeTitle.Text
> objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
> CoverLetter.Text objCmd.Parameters.Add("@.errorCode",
> SqlDbType.Int).Direction = ParameterDirection.Output
> objConn.Open()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("@.errorCode").Value))
> ****************************************
**********************************
***
> The stored procedure essentially looks like:
> ****************************************
**********************************
***
> CREATE PROCEDURE AddNewResumeCoverTemplate
> (
> @.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle
> varChar(45),@.Resume text,@.CoverLetterTitle varChar(45),@.CoverLetter
> text,@.errorCode int Output )
> AS
> ...
> select @.errorCode = 1
> return @.errorCode
> GO
> ****************************************
**********************************
****
> I put the select statement there just to force @.errorCode to be 1.
> But my pages trace.warn is showing it as 0 (always).
> Do I have it set up correctly?
> Thanks,
> Tom
Return types and parameters are two different things. You are not
declaring a return type from your .Net code. However, I would think the
output parameter, as you defined it, should be coming back correctly. In
order to use the @.errorCode as a return value, you don't want to declare
it in the procedure as a parameter. From the ADO.Net code, you define
the return value using the ParameterDirection = ReturnValue.
For example from MSDN:
Dim PubsConn As SqlConnection = New SqlConnection & _
("Data Source=server;integrated security=sspi;" & _
"initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand & _
("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add ("RetValue",
SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add ("@.au_idIN",
SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add
("@.numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eW6TjTvYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> tshad wrote:
> Return types and parameters are two different things. You are not
> declaring a return type from your .Net code. However, I would think the
> output parameter, as you defined it, should be coming back correctly. In
> order to use the @.errorCode as a return value, you don't want to declare
> it in the procedure as a parameter. From the ADO.Net code, you define the
> return value using the ParameterDirection = ReturnValue.
> For example from MSDN:
> Dim PubsConn As SqlConnection = New SqlConnection & _
> ("Data Source=server;integrated security=sspi;" & _
> "initial Catalog=pubs;")
> Dim testCMD As SqlCommand = New SqlCommand & _
> ("TestProcedure", PubsConn)
> testCMD.CommandType = CommandType.StoredProcedure
> Dim RetValue As SqlParameter = testCMD.Parameters.Add ("RetValue",
> SqlDbType.Int)
> RetValue.Direction = ParameterDirection.ReturnValue
> Dim auIDIN As SqlParameter = testCMD.Parameters.Add ("@.au_idIN",
> SqlDbType.VarChar, 11)
> auIDIN.Direction = ParameterDirection.Input
> Dim NumTitles As SqlParameter = testCMD.Parameters.Add ("@.numtitlesout",
> SqlDbType.Int)
> NumTitles.Direction = ParameterDirection.Output
> auIDIN.Value = "213-46-8915"
> PubsConn.Open()
> Dim myReader As SqlDataReader = testCMD.ExecuteReader()
> Console.WriteLine("Book Titles for this Author:")
> Do While myReader.Read
> Console.WriteLine("{0}", myReader.GetString(2))
> Loop
> myReader.Close()
> Console.WriteLine("Return Value: " & (RetValue.Value))
> Console.WriteLine("Number of Records: " & (NumTitles.Value))
>
Still doesn't seem to work.
I changed the asp.net code as so:
objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
CoverLetter.Text
objCmd.Parameters.Add("@.errorCode", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
and the Stored Procedure as:
CREATE PROCEDURE AddNewResumeCoverTemplate
(
@.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle varChar(45),@.Resume
text,@.CoverLetterTitle varChar(45),@.CoverLetter text
)
AS
declare @.errorCode int
...
select @.errorCode = 1
return @.errorCode
I am still getting back a value of 0.
Tom|||tshad wrote:
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:eW6TjTvYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> Still doesn't seem to work.
> I changed the asp.net code as so:
> objCmd.parameters.add("@.CoverLetter",SqlDbType.text).value =
> CoverLetter.Text
> objCmd.Parameters.Add("@.errorCode", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> and the Stored Procedure as:
> CREATE PROCEDURE AddNewResumeCoverTemplate
> (
> @.ClientID varChar(20),@.Email varChar(45),@.ResumeTitle
> varChar(45),@.Resume text,@.CoverLetterTitle varChar(45),@.CoverLetter
> text )
> AS
> declare @.errorCode int
> ...
> select @.errorCode = 1
> return @.errorCode
> I am still getting back a value of 0.
> Tom
Try removing the @. prefix on the return value in the code and see what
happens.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23Om7HqwYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> tshad wrote:
> Try removing the @. prefix on the return value in the code and see what
> happens.
>
I changed it to:
objCmd.Parameters.Add("errorCode", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("errorCode").Value))
But still get 0 back.
Tom
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23iFl1vwYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23Om7HqwYFHA.3032@.TK2MSFTNGP10.phx.gbl...
Your solution is exactly as shown in my asp.net book
I even tried to use the "ReturnValue", as they show and did the following:
objCmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
Dim applicantReader = objCmd.ExecuteReader
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
if applicantReader.Read then
if applicantReader("ResumeID") is DBNull.Value then
trace.warn("ResumeID = nothing")
else
trace.warn("ResumeID <> nothing")
end if
end if
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
I realized that the trace.warn was in the wrong place and moved it after the
applicantReader.Read if statement.
But I still got a 0.
Very confusing.
Tom
> I changed it to:
> objCmd.Parameters.Add("errorCode", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> objConn.Open()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("errorCode").Value))
> But still get 0 back.
> Tom
>|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23iFl1vwYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23Om7HqwYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> I changed it to:
> objCmd.Parameters.Add("errorCode", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> objConn.Open()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("errorCode").Value))
> But still get 0 back.
Ok.
I got it to work, bu changing it from a Reader to ExecuteNonQuery.
objCmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
objCmd.ExecuteNonQuery()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
Now I am getting a 1 back.
In this case, I am not passing back any data (just my return value).
But even with a DataReader, I still need to get the return value if there is
no Data passed back (as in this case). So how do I get the Return value if
this is the case?
Thanks,
Tom|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezmd28wYFHA.2756@.tk2msftngp13.phx.gbl...
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23iFl1vwYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> Ok.
> I got it to work, bu changing it from a Reader to ExecuteNonQuery.
> objCmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction =
> ParameterDirection.ReturnValue
> objConn.Open()
> objCmd.ExecuteNonQuery()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
> Now I am getting a 1 back.
> In this case, I am not passing back any data (just my return value).
> But even with a DataReader, I still need to get the return value if there
> is no Data passed back (as in this case). So how do I get the Return
> value if this is the case?
>
I remember someone mentioning before that a DataReader had to read to the
end of the data before it got the return value. So I changed the DataReader
code to:
while applicantReader.Read()
if applicantReader("ResumeID") is DBNull.Value then
trace.warn("ResumeID = nothing")
trace.warn("ResumeID = " & applicantReader("ResumeID") & "
CoverLetterID = " & applicantReader("CoverLetterID"))
else
trace.warn("ResumeID <> nothing")
end if
trace.warn("inside read ResumeID = " & applicantReader("ResumeID") & "
CoverLetterID = " & applicantReader("CoverLetterID"))
end while
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
I just changed the "if" to a "while", but I am still getting 0 back.
I know it is sending a 1 back since I do get that if I use an
"ExecuteNonQuery".
Tom

> Thanks,
> Tom
>|||Before retrieving output parameters or the return code, you need to invoke
the NextResult method after retrieving resultset(s):
applicantReader.NextResult()
trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
Hope this helps.
Dan Guzman
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OFH9QKxYFHA.3572@.TK2MSFTNGP12.phx.gbl...
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ezmd28wYFHA.2756@.tk2msftngp13.phx.gbl...
> I remember someone mentioning before that a DataReader had to read to the
> end of the data before it got the return value. So I changed the
> DataReader code to:
> while applicantReader.Read()
> if applicantReader("ResumeID") is DBNull.Value then
> trace.warn("ResumeID = nothing")
> trace.warn("ResumeID = " & applicantReader("ResumeID") & "
> CoverLetterID = " & applicantReader("CoverLetterID"))
> else
> trace.warn("ResumeID <> nothing")
> end if
> trace.warn("inside read ResumeID = " & applicantReader("ResumeID") & "
> CoverLetterID = " & applicantReader("CoverLetterID"))
> end while
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
> I just changed the "if" to a "while", but I am still getting 0 back.
> I know it is sending a 1 back since I do get that if I use an
> "ExecuteNonQuery".
> Tom
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eQ0WxXyYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Before retrieving output parameters or the return code, you need to invoke
> the NextResult method after retrieving resultset(s):
> applicantReader.NextResult()
> trace.warn("Error return = " &
> Convert.ToInt32(objCmd.Parameters("ReturnValue").Value))
I'm here.
I thought that NextResult() gets you the next set if you are doing multiple
selects and expecting multiple results?
Does this mean that you really need to always do a NextResult after getting
your results to make sure you get the return value (if there was one)?
What about if you just to a databind()? Would you still need to do a
NextResult() to get the return value?
Also, just want to make sure I understand, if there is no more result sets
and no return value, wouldn't a NextResult() give you an error?
Thanks,
Tom
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OFH9QKxYFHA.3572@.TK2MSFTNGP12.phx.gbl...
>