Output Parameters

[comcrack]

I trying to use output parameters and it doesn't work :D . I need help to understand how to use it :

 

        Dim tmpCommand As New SqlCommand("DECLARE @tmpId uniqueidentifier" & vbCrLf & _
       "set @tmpId = NEWID()" & vbCrLf & _
       "set @CategoryID= @tmpId" & vbCrLf & _
       "INSERT INTO Personne (Id,Nom) VALUES (@tmpId,'Com')")

       tmpCommand.Connection = Conn
       Dim prm As SqlParameter
       prm = New SqlParameter("@CategoryID", SqlDbType.UniqueIdentifier, 16, ParameterDirection.InputOutput)
       tmpCommand.Parameters.Add(prm)


           tmpCommand.ExecuteNonQuery()

 

Thank you

 

Com Crack

[PlausiblyDamp]

When you say

it doesn't work

could you give more details please?

Do you mean you cannot read the value of the parameter after you have executed the query or that it is failing to run the stored procedure itself? If possible could you post the code for the stored proc?

[Robby]

You could use this method in a stored proc...

 

you need to Return your new id... something like this ...

 

Declare @id int

 

--Do your insert here

 

SET @id = @@IDENTITY

 

Retrun @id

 

__________.NET code

 

Friend Function SomeInsert() As Integer

Dim ret As Integer = 0

Dim cmd As New SqlCommand

Dim cn As New SqlConnection(Conn)

Try

Dim sp As SqlParameter = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)

sp.Direction = ParameterDirection.ReturnValue

 

cn.Open()

cmd.Connection = cn

cmd.ExecuteReader(CommandBehavior.Default)

ret = CType(cmd.Parameters("RETURN_VALUE").Value, Integer)

 

Catch ex As Exception

Throw New Exception(ex.Message)

Finally

If cn.State = ConnectionState.Open Then cn.Close()

If Not cmd Is Nothing Then cmd.Dispose()

End Try

Return ret

End Function

[comcrack]

This is my code :

 

Public Sub Insert(ByVal tmpTableNom As String, ByRef tmpInsertStrColone As String, ByRef tmpInsertStrValeur As String)


       Dim tmpCommand As New SqlCommand(" DECLARE @tmpId UniqueIdentifier" & vbCrLf & " SET @tmpId=newid() " & vbCrLf & "INSERT INTO" & tmpTableNom & " (Id," & tmpInsertStrColone & ") VALUES (@tmpId," & tmpInsertStrValeur & ")" & vbCrLf & "return @tmpId")

       tmpCommand.Connection = Conn
       Dim prm As SqlParameter
       Dim sp As SqlParameter = tmpCommand.Parameters.Add("RETURN_VALUE", SqlDbType.UniqueIdentifier)
       sp.Direction = ParameterDirection.ReturnValue

       Try

           tmpCommand.ExecuteReader(CommandBehavior.Default)

           MsgBox(CType(sp.Value, Guid).ToString)
       Catch ex As SqlException

           MsgBox(ex.Message, MsgBoxStyle.Exclamation, "App")
       End Try
   End Sub

 

This is the SqlException message :

A RETURN statement with a return value cannot be used in this context

:confused:

[kahlua001]

Arent return codes suppose to be int only? Use a OUTPUT param instead of a Return param. Also, why are you using .executereader? why not executenonquery.

[comcrack]

Because I don't know what is the difference.

 

 

Ok int only, but if I want to get the GUID(newid()) that I Inserted What can I do?

[kahlua001]

Try

 

DECLARE @tmpId UniqueIdentifier OUTPUT
SELECT @tmpID = NewID()

 

Then

Dim paramOutput As SqlParameter

paramOutput = objCmd.Parameters.Add(New SqlParameter("@tmpId", SqlDbType.UniqueIdentifier))
paramOutput.Direction = ParameterDirection.Output

objCmd.executenonquery()

response.write(objCmd.Parameters("@tmpId").Value)

 

OR

 

DECLARE @tmpId UniqueIdentifier
SET @tmpID = NewID()
SELECT @tempID

 

Then

 

response.write(objCmd.ExecuteScalar())

 

REturn codes should be used for error codes, but I find my lazy self to use them as data values sometimes :) Output params can handle different datatypes than just an integer, so you should use that. You can have both Return Values and Output params at the same time. You can have some intelligent Stored Procedures using both effectively at the same time.