newbie:- why is output parameter not being return??

  • Thread starter Thread starter Peter Row
  • Start date Start date
P

Peter Row

Hi,

I am running a SP on a SQL Server 2000 DB that takes 3 input parameters and
has 2 integer output parameters.
The query does not return a recordset, it has SET NOCOUNT ON.

It works fine in Query Analyser and the output parameters are being set to
what I expect given the inputs.
However when run from with a sqlcommand object using the ExecuteNonQuery()
method, the output parameters
always return zero, which is a lie.

I have another SP that does not return a recordset and returns 1 output
parameter and that works through ADO.NET
and in Query Analyser.

But I can't for the life of me see the difference between them. They both
have the same number of input and output
parameters. Neither returns a recordset.

I've tried taking SET NOCOUNT ON out but that doesn't seem to make any
difference. And that was the only thing
that I could see that was different between the 2 SPs.

Any help or ideas would be much appreciated.

Regards,
Peter
 
Hi,

Ok this is hard to do because I have a wrapper class that collects the
parameters of the command object, plus
this is a porting task from VB6 which is why its not fully .NET optimised
yet.

Anyway here goes.... this is the code the builds the parameters up:

Private Sub collectParamsForCall(ByRef cmd As SqlCommand, ByVal vParams
As Array, ByRef bOutputParametersDefined As Boolean)
'Sub Name: collectParamsForCall
'Purpose: collect parameters from array of parameters, for binding
to pre-
' prepared SQL statements
Dim i As Integer

bOutputParametersDefined = False
If Not IsArray(vParams) Then Exit Sub

Dim u As Integer
Dim l As Integer
For i = LBound(vParams) To UBound(vParams)
'** Check if enough values were specified for this parameter.
u = UBound(vParams(i))
l = LBound(vParams(i))
If u - l >= 3 Then
'** Check if we're adding an output parameter.
If vParams(i)(2) = ParameterDirection.Output Then
bOutputParametersDefined = True

Dim param As SqlParameter
' e.g Array("@repid", adInteger, adParamInput, 4, repid)
param = cmd.Parameters.Add(vParams(i)(0), vParams(i)(1))
param.Direction = vParams(i)(2)
param.Size = vParams(i)(3)

'** Was a value specified?
If u > 3 Then
'** Yes.
param.Value = vParams(i)(4)
End If
End If
Next
End Sub

Here is the call made to the SP (NOTE SP/param names are not real:

g_db.RunSP("MySP", _
New Array() { _
New Object() {"@Param1", adInteger, adParamInput, 4,
30}, _
New Object() {"@Param2", adInteger, adParamInput, 4,
1568}, _
New Object() {"@Param3", adInteger, adParamInput, 4,
1}, _
New Object() {"@Param4", adInteger, adParamOutput,
4, 0}, _
New Object() {"@Param5", adInteger, adParamOutput,
4, 0} _
}, dicOutParams _
)

The Sqlcommand object is executed by the method "ExecuteNonQuery()".

Regards,
Peter
 
Hi Peter,

I think I see your problem.
param = cmd.Parameters.Add(vParams(i)(0), vParams(i)(1))
param.Direction = vParams(i)(2)
Click to expand...

Those are probably wrong.
vParams(i)(1) should be of type System.Data.SqlDbType and not adInteger.
vParams(i)(2) is also wrong. It should be System.Data.ParameterDirection.

You might turn on Option Strict (Option Strict On) which will show you the
second error.
While the first one will be hidded because it is a valid overload (but not
correct in your case).
 
Hi,

Thanks for replying. I have now worked out why it was not working.
It was me being a bit of a dumb-arse. One of the input parameters was
being set to the wrong value due to a logical error before the call hence
the output parameter was in actual fact giving the correct value.

As for your comments in the email. There actually is no problem with the
code. For the initial port of the code I wanted to make it as pure as
possible
(i.e. with as little change as possible) therefore I implemented an
ADOMapping
enumeration which maps old ADO.COM+ types like adInteger to the equivalent
SqlDbType and the same for the parameter direction. Admittedly in the code
you
point out a cast function will no doubt be required once option strict is
turned on.
Again I haven't done this yet as I just want to get the initial porting done
and
after a trial of having it on there are millions of implicit casting bits
whinged about.
I know people will say I should do it know so I will have less to do, but I
am
putting explicit casting in everywhere I notice it missing and in any new
code
that I forced to put in.

Thanks for taking the time to read my post.
Pete
 
Back
Top