locked
Stored Procedure VB.NET Visual Studio 2003 Return Value RRS feed

  • Question

  • Hi @ll.

     

    I am trying to get the Return Value of an Stored Procedure in SQL Server 2005.

     

    So Far I Am:

    sqlsp.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

    sqlsp.InsertCommand = "[AktsInsert]"

    sqlsp.InsertParameters.Add("IDAktSize", IDAktSize)

    sqlsp.InsertParameters.Add("IDMand", idmand)

    sqlsp.InsertParameters.Add("AkTypeKey", AkTypeKey)

    sqlsp.InsertParameters.Add("LfdNr", LfdNr)

    sqlsp.InsertParameters.Add("RelYear", RelYear)

    sqlsp.InsertParameters.Add("BeginDate", BeginDate)

    sqlsp.InsertParameters.Add("EndDate", EndDate)

    sqlsp.InsertParameters.Add("AkSubType", beschreibung)

    sqlsp.InsertParameters.Add("Retour", 0)

    sqlsp.InsertParameters(8).Direction = Data.ParameterDirection.ReturnValue

    sqlsp.Insert()

     

    These are my Tries to get the Return Value, but it does not work.

     

    I hope someone can help me. I think it must be very simmilar to get it, but i failed.

    Thx for your time

     

    'idakt = sqlsp.InsertParameters(8).GetHashCode 'Output:212245564

    'idakt = sqlsp.InsertParameters(8).DefaultValue 'Output:0

    'idakt = sqlsp.InsertParameters(8).ToString 'Output:Retour

    'idakt = sqlsp.InsertParameters(8).Name 'Output:Retour

    'idakt = sqlsp.InsertParameters("Retuor").ToString Output::Retour

    'idakt = sqlsp.InsertParameters(8).ConvertEmptyStringToNull Output:0

    'idakt = sqlsp.InsertParameters(8).ToString Output:Retour

    'idakt = Me.sqlsp.InsertParameters(8).Name Output:Retour

     

     

     

    Friday, August 3, 2007 2:27 PM

Answers

  • Either in design mode, or somewhere at runtime, you should setup a reference to the "Inserted" event on your SqlDataSource.  Within that event handler, you will have access to the command that was built and executed, and this will also contain the parameter information... at which point you can extract the information you need.  Below is a very simplistic version of what I'm talking about:

     

    Code Snippet

    Public Sub DoInsert()

       AddHandler SqlDataSource1.Inserted, AddressOf SqlDataSourc1_Inserted

       sqlsp.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

       sqlsp.InsertCommand = "[AktsInsert]"

       sqlsp.InsertParameters.Add("IDAktSize", IDAktSize)

       sqlsp.InsertParameters.Add("IDMand", idmand)

       sqlsp.InsertParameters.Add("AkTypeKey", AkTypeKey)

       sqlsp.InsertParameters.Add("LfdNr", LfdNr)

       sqlsp.InsertParameters.Add("RelYear", RelYear)

       sqlsp.InsertParameters.Add("BeginDate", BeginDate)

       sqlsp.InsertParameters.Add("EndDate", EndDate)

       sqlsp.InsertParameters.Add("AkSubType", beschreibung)

       sqlsp.InsertParameters.Add("Retour", 0)

       sqlsp.InsertParameters(8).Direction = Data.ParameterDirection.ReturnValue

       sqlsp.Insert()

     

    End Sub

     

    Public Sub SqlDataSourc1_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs)

     

       Dim i As Int32

     

       i = CType(e.Command.Parameters("@Retour").Value.ToString(), String)
      
    End Sub

     

     


    Monday, August 6, 2007 12:45 PM

All replies

  • Code Snippet

    idakt = sqlsp.InsertParameters(8).Value

    - or -

    idakt = sqlsp.InsertParameters("Retour").Value

     

     

    That should do it.

    Saturday, August 4, 2007 3:44 AM
  •  

    Compilerfehlermeldung: BC30456: Value ist kein Member von System.Web.UI.WebControls.Parameter.

    Quellfehler:

    Zeile 95:         sqlsp.InsertParameters(8).Direction = Data.ParameterDirection.ReturnValue
    Zeile 96:         sqlsp.Insert()
    Zeile 97:         idakt = sqlsp.InsertParameters(8).Value
    Zeile 98: 
    Zeile 99:         'idakt = sqlsp.InsertParameters(8).GetHashCode   '212245564

     

    Value is not a member of System.Web.UI.WebControls.Parameter.

    I am sorry, that I tried, but it seems, that this doesn`t work.

     

    Any other Ideas?

     

     

    Monday, August 6, 2007 7:33 AM
  • Either in design mode, or somewhere at runtime, you should setup a reference to the "Inserted" event on your SqlDataSource.  Within that event handler, you will have access to the command that was built and executed, and this will also contain the parameter information... at which point you can extract the information you need.  Below is a very simplistic version of what I'm talking about:

     

    Code Snippet

    Public Sub DoInsert()

       AddHandler SqlDataSource1.Inserted, AddressOf SqlDataSourc1_Inserted

       sqlsp.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

       sqlsp.InsertCommand = "[AktsInsert]"

       sqlsp.InsertParameters.Add("IDAktSize", IDAktSize)

       sqlsp.InsertParameters.Add("IDMand", idmand)

       sqlsp.InsertParameters.Add("AkTypeKey", AkTypeKey)

       sqlsp.InsertParameters.Add("LfdNr", LfdNr)

       sqlsp.InsertParameters.Add("RelYear", RelYear)

       sqlsp.InsertParameters.Add("BeginDate", BeginDate)

       sqlsp.InsertParameters.Add("EndDate", EndDate)

       sqlsp.InsertParameters.Add("AkSubType", beschreibung)

       sqlsp.InsertParameters.Add("Retour", 0)

       sqlsp.InsertParameters(8).Direction = Data.ParameterDirection.ReturnValue

       sqlsp.Insert()

     

    End Sub

     

    Public Sub SqlDataSourc1_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs)

     

       Dim i As Int32

     

       i = CType(e.Command.Parameters("@Retour").Value.ToString(), String)
      
    End Sub

     

     


    Monday, August 6, 2007 12:45 PM
  • That`s it, I thank you very much.

     

    Monday, August 6, 2007 2:21 PM