Re: Retrieving the @@IDENTITY value from a SP in VB.Net

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Frans Bouma [C# MVP] (perseus.usenetNOSPAM_at_xs4all.nl)
Date: 07/12/04 

Date: Mon, 12 Jul 2004 06:45:36 -0700

Thanks for the tests, Greg.

I did some checking after reading your tests as well, and got the same
results.

Digging deeper into what seemed to be the cause of my understanding of the
matter was a confusion on my side, where I confused a different situation and
what is in context with the situation discussed. In all my code generators I
do it as required, but messed up with the posting. Mainly it comes down to
the fact that you can write InputOutput for an input or an output, which was
the root of the confusion on my side and I take all the blame for this crappy
misunderstanding/mess :)

Sorry to have wasted your time, Greg.

                Frans.

Greg Burns wrote:

> > > I do have to disagree with the default direction for a parameter. It is
> > > not InputOutput, it is just Input.
> >
> > no, it's not. :) SqlServer's IN is always INOUT. But that's a minor detail
> :)
> >
>
> The online help says different:
>
> SqlParameter.Direction Property
>
> Gets or sets a value indicating whether the parameter is input-only,
> output-only, bidirectional, or a stored procedure return value parameter.
>
> Property Value
> One of the ParameterDirection values. The default is Input.
>
>
> But since you can't always trust what you read, I threw together a test.
>
> CREATE PROCEDURE usp_GetValue
> @value int OUTPUT -- removing OUTPUT doesn't work either
> AS
> SELECT @value=@value+1
>
>
> Dim cmd As New SqlCommand("usp_GetValue", cn)
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@value", SqlDbType.Int).Value = 1
> cmd.Parameters("@value").Direction = ParameterDirection.InputOutput
> Try
> cn.Open()
> cmd.ExecuteNonQuery()
> Catch
> Finally
> cn.Close()
> End Try
>
> Dim x As Integer = CType(cmd.Parameters("@value").Value, Integer)
>
> Without this line:
> cmd.Parameters("@value").Direction = ParameterDirection.InputOutput
> x = 1
> with the line
> x = 2
>
> I was thinking maybe things would be different in Query Analyzer, but I
> couldn't get it to do as you say there either.
>
> DECLARE @x int
> SET @x=1
> EXEC usp_GetValue @x --OUTPUT (again, commented out for test)
> PRINT @x
>
> returns 1, not 2
>
> I don't mean to sound like a smart*ss. Just want to get to the bottom of
> this.
>
> Greg

Relevant Pages

  • Font Switching Macro?
    ... Sorry for the confusion. ... Greg's, a Chad, and a Dave involved. ... I am Greg Maxey ...
    (microsoft.public.word.vba.beginners)
  • Re: Retrieving the @@IDENTITY value from a SP in VB.Net
    ... critisize me putting effort in answering questions:) ... Greg. ... >> I did some checking after reading your tests as well, ... >> matter was a confusion on my side, where I confused a different situation ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Retrieving the @@IDENTITY value from a SP in VB.Net
    ... Now if we can just get you and Cor to friends again. ... > matter was a confusion on my side, where I confused a different situation ... > Sorry to have wasted your time, Greg. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Get the Identity value
    ... Hi Aaron, ... It might well be that I caused some confusion. ... Here, sp was short for stored procedure, so I intended this as ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.programming)
  • Re: Select range of month and year
    ... > I have created a stored procedure where I want to dynamically pass ... > I have tried DATENAME, DATEPART, ... If you have any confusion regarding ...
    (microsoft.public.sqlserver.programming)