Return always 0

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

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("ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
Dim applicantReader = objCmd.ExecuteReader

while applicantReader.Read()
if applicantReader("ResumeID") is DBNull.Value then
trace.warn("ResumeID = nothing")
trace.warn("ResumeID = " & applicantReader("ResumeID") )
else
trace.warn("ResumeID <> nothing")
end if
end while

trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").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
)
AS
declare @errorCode int
....
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).

If I use an ExecureNonQuery, it comes back 1 (as it should)

At first I used an "if" statement for the applicantReader.Read() and that
didn't work. I remember someone mentioning that a DataReader has to read
all the data before it will sent the return value. That was why I changed
it to "while". But that still sends me a 1.

Do I have it set up correctly?

Thanks,

Tom
 
Tom:

According to the following MSDN article the SQLDataReader return values and
output parameters are not available until after the SQL DataReader is
closed.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


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("ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objConn.Open()
Dim applicantReader = objCmd.ExecuteReader

while applicantReader.Read()
if applicantReader("ResumeID") is DBNull.Value then
trace.warn("ResumeID = nothing")
trace.warn("ResumeID = " & applicantReader("ResumeID") )
else
trace.warn("ResumeID <> nothing")
end if
end while

trace.warn("Error return = " &
Convert.ToInt32(objCmd.Parameters("ReturnValue").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
)
AS
declare @errorCode int
....
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).

If I use an ExecureNonQuery, it comes back 1 (as it should)

At first I used an "if" statement for the applicantReader.Read() and that
didn't work. I remember someone mentioning that a DataReader has to read
all the data before it will sent the return value. That was why I changed
it to "while". But that still sends me a 1.

Do I have it set up correctly?

Thanks,

Tom
 
Back
Top