Problem Receiving Output Param Value...

  • Thread starter Thread starter Tifer
  • Start date Start date
T

Tifer

Hello,

I'm still new to the whole .Net thing and I'm having a problem with
something that should be so simple -- executing a query and returning
an output parameter. It's a standard "Add" stored procedure, where I
add a user to the database. I insert the record, and return the user's
ID.

I can run it just fine and it does execute the code because the user is
being inserted successfully. But I can't receive any output values.
I've since pared it down to a very simple stored procedure that simply
takes one value and it's an output Integer. I hardcore the value.
This is the stored procedure:

-----------------------------------
ALTER PROCEDURE dbo.User_Test

(
@myOutput INT = NULL OUTPUT
)

AS

SET NOCOUNT ON
SELECT @myOutput = 8
SET NOCOUNT OFF
-----------------------------------


And here is the code being run on the web form:

-----------------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Trace.IsEnabled = True

Dim objConn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim objComm As SqlCommand = New SqlCommand()
objConn.Open()
objComm.CommandType = CommandType.StoredProcedure
objComm.CommandText = "dbo.User_Test"


Dim objParam As SqlParameter
objParam = New SqlParameter("@myOutput", SqlDbType.Int, 4,
ParameterDirection.Output)
objComm.Parameters.Add(objParam)
Try
objComm.Connection = objConn
objComm.ExecuteNonQuery()

Response.Write("=================<br>")
Trace.Warn("Test")
Trace.Warn(objParam.Value)
Trace.Warn(objComm.Parameters("myOutput").Value)
Response.Write("***<br>")
Response.Write("=================<br>")

objConn.Close()
Catch
Response.Write(Err.Description)
End Try



End Sub
-----------------------------------

You can see I tried Warn'ing the parameter value in 2 different ways.
Nothing ever gets returned.

Can anyone spot what I'm doing wrong?

Thanks,

Chris T
 
It all looks correct to me except for one line:
You need to set the value for the parameter.

Instead of:
objParam = New SqlParameter("@myOutput", SqlDbType.Int, 4,
ParameterDirection.Output)
objComm.Parameters.Add(objParam)

Do this:

objParam = New SqlParameter("@myOutput", SqlDbType.Int, 4,
ParameterDirection.Output)
objParam.Value = yourVariable
objComm.Parameters.Add(objParam)
 
Chris said:
Woops.. never mind. I didn't see that you set the output param to 8 in the
sproc.
Click to expand...

Correct, and before I posted this message, I have also tried passing in
a NULL value:
----------------------------------------
objParam = New SqlParameter("@myOutput", SqlDbType.Int, 4,
ParameterDirection.Output)
objParam.Value = System.DBNull.Value
----------------------------------------

But that didn't produce anything helpful.

This seems like such an easy problem, yet it's holding me up. Any time
I want to mess around with .Net, I always run into one of these and it
keeps me from wanting to continue.

I'm still holding out hope that someone here has an answer for me.
 
Just a quick update. A buddy helped me solve the problem, but we're
still stumped as to why my code didn't work. The ONLY thing I changed
was the below declaration of a parameter that I then added to the
collection:
----------------------------------------
Dim objParam As SqlParameter
objParam = New SqlParameter("@myOutput", SqlDbType.Int, 4,
ParameterDirection.Output)
objComm.Parameters.Add(objParam)

To this:
----------------------------------------
objComm.Parameters.Add("@myOutput", SqlDbType.Int, 4)
objComm.Parameters("@myOutput").Direction = ParameterDirection.Output

Now I receive my output param just fine. So I gues the big question
now is: Why the HELL does it have to be done this way? I was following
examples online which led me to my initial code. Is there something
wrong in my original code that I'm just not seeing?

Thanks in advance.
 
Back
Top