Stored Procedure Return value question

Discussion in 'ASP .Net' started by Nick, Feb 10, 2005.

  1. Nick

    Nick Guest

    Hi,

    I'm trying to add some data on a company to an SQL server (which I can do),
    and want to return the FirmID (SQL identity) of the data that was just
    inserted so I can use it to redirect to another page based on the new data -
    which is the bit I can't do!

    Can anyone point me in the right direction?

    Thanks
     
    Nick, Feb 10, 2005
    #1
    1. Advertising

  2. Nick wrote:

    > Hi,
    >
    > I'm trying to add some data on a company to an SQL server (which I can do),
    > and want to return the FirmID (SQL identity) of the data that was just
    > inserted so I can use it to redirect to another page based on the new data -
    > which is the bit I can't do!
    >
    > Can anyone point me in the right direction?


    The FirmID is already the return value of the proc?

    In that case just add a parameter with Direction.ReturnValue to the
    parameter list of your command:

    Dim myParameter As New SqlParameter()
    myParameter.Direction = ParameterDirection.ReturnValue

    myParameter.Value will then give you the value after the execution of
    the command.

    Daniel
     
    Daniel Buchholz, Feb 10, 2005
    #2
    1. Advertising

  3. What bit can't you do? Get id back in SQL? Pass result from sp? What is sp
    for? Are you aware of SCOPE_IDENTITY?

    Eliyahu

    "Nick" <> wrote in message
    news:...
    > Hi,
    >
    > I'm trying to add some data on a company to an SQL server (which I can

    do),
    > and want to return the FirmID (SQL identity) of the data that was just
    > inserted so I can use it to redirect to another page based on the new

    data -
    > which is the bit I can't do!
    >
    > Can anyone point me in the right direction?
    >
    > Thanks
    >
    >
     
    Eliyahu Goldin, Feb 10, 2005
    #3
  4. Nick

    Nick Guest

    Hi,

    I have 'Return @@Identity' in the sp - so I think the return value is there.
    I'm not sure how to use the parameters though - this is how I'm running the
    sp:

    Dim strConn As String = "server=" & Global.ServerName & ";database=" &
    Global.DatabaseName & ";integrated security=true"

    Dim objConn As New SqlConnection(strConn)

    Dim objDS As New DataSet

    Dim SQLInsert As String

    Dim daInsertCompany As New SqlDataAdapter("spd_InsertCompany " & SQLInsert,
    objConn)

    daUpdateCompany.Fill(objDS)

    objDS.Dispose()

    daInsertCompany.Dispose()

    objConn.Close()

    objConn.Dispose()


    Where would I add the parameters part? (Or am I running the sp incorrectly
    in the first place - although it does work...)

    Thanks,

    Nick


    "Daniel Buchholz" <> wrote in message
    news:...
    > Nick wrote:
    >
    >> Hi,
    >>
    >> I'm trying to add some data on a company to an SQL server (which I can
    >> do), and want to return the FirmID (SQL identity) of the data that was
    >> just inserted so I can use it to redirect to another page based on the
    >> new data - which is the bit I can't do!
    >>
    >> Can anyone point me in the right direction?

    >
    > The FirmID is already the return value of the proc?
    >
    > In that case just add a parameter with Direction.ReturnValue to the
    > parameter list of your command:
    >
    > Dim myParameter As New SqlParameter()
    > myParameter.Direction = ParameterDirection.ReturnValue
    >
    > myParameter.Value will then give you the value after the execution of the
    > command.
    >
    > Daniel
    >
     
    Nick, Feb 10, 2005
    #4
  5. Nick wrote:

    > Hi,
    > Where would I add the parameters part? (Or am I running the sp incorrectly
    > in the first place - although it does work...)


    Looks quite complicated.

    Perhaps try something like:

    Dim strConn As String = "server=" & Global.ServerName & ";database=" &
    Global.DatabaseName & ";integrated security=true"

    Dim objConn As New SqlConnection(strConn)
    objConn.Open()

    Dim objCmd As New SqlCommand("spd_InsertCompany " & SQLInsert,
    objConn)

    objCmd.CommandType = CommandType.StoredProcedure

    Dim para As New SqlParameter()
    para.Direction = ParameterDirection.ReturnValue

    objCmd.Parameters.Add(para)

    objCmd.ExecuteNonQuery()

    objConn.Close()

    --

    para.Value should have your value then.

    Daniel
     
    Daniel Buchholz, Feb 10, 2005
    #5
  6. Nick

    Nick Guest

    Thanks for this. Am I missing something though? The code generates an error
    at the ExecuteNonQuery line - saying it can't find the sp... Here's the code
    now:

    Dim objCmd As New SqlCommand("spd_InsertCompanyDetails " & SQLinsert,
    objConn)

    objCmd.CommandType = CommandType.StoredProcedure

    Dim ReturnIdentity As New SqlParameter

    ReturnIdentity.Direction = ParameterDirection.ReturnValue

    objCmd.Parameters.Add(ReturnIdentity)

    objCmd.ExecuteNonQuery() '<==== Breaks here for the error...

    ReturnValue = ReturnIdentity.Value

    objConn.Close()


    Thanks,

    Nick


    "Daniel Buchholz" <> wrote in message
    news:...
    > Nick wrote:
    >
    >> Hi,
    >> Where would I add the parameters part? (Or am I running the sp
    >> incorrectly in the first place - although it does work...)

    >
    > Looks quite complicated.
    >
    > Perhaps try something like:
    >
    > Dim strConn As String = "server=" & Global.ServerName & ";database=" &
    > Global.DatabaseName & ";integrated security=true"
    >
    > Dim objConn As New SqlConnection(strConn)
    > objConn.Open()
    >
    > Dim objCmd As New SqlCommand("spd_InsertCompany " & SQLInsert,
    > objConn)
    >
    > objCmd.CommandType = CommandType.StoredProcedure
    >
    > Dim para As New SqlParameter()
    > para.Direction = ParameterDirection.ReturnValue
    >
    > objCmd.Parameters.Add(para)
    >
    > objCmd.ExecuteNonQuery()
    >
    > objConn.Close()
    >
    > --
    >
    > para.Value should have your value then.
    >
    > Daniel
    >
     
    Nick, Feb 10, 2005
    #6
  7. Nick wrote:

    > Thanks for this. Am I missing something though? The code generates an error
    > at the ExecuteNonQuery line - saying it can't find the sp... Here's the code
    > now:
    >
    > Dim objCmd As New SqlCommand("spd_InsertCompanyDetails " & SQLinsert,
    > objConn)


    Uh, sorry, my fault.

    You have to specify your parameters to the procedure. The "SQLInsert"
    part is not correct.

    For each parameter you have to pass do something like

    sqlCmd.Parameters.Add("[yourSPParam]", [yourParamValue])

    after creating the Command like

    Dim objCmd As New SqlCommand("spd_InsertCompanyDetails")

    Daniel
     
    Daniel Buchholz, Feb 10, 2005
    #7
  8. Nick

    Nick Guest

    Ok - Ignore that last message!

    I changed the CommandType to text because I was passing the sp parameters in
    the same string, whereas the system was looking for an sp with the name of
    the sp+the parameters.

    Unfortunately, the parameter value stays 0 rather than the actual identity
    value... How do I check it is actually being returned from the sp?

    Nick

    "Daniel Buchholz" <> wrote in message
    news:...
    > Nick wrote:
    >
    >> Hi,
    >> Where would I add the parameters part? (Or am I running the sp
    >> incorrectly in the first place - although it does work...)

    >
    > Looks quite complicated.
    >
    > Perhaps try something like:
    >
    > Dim strConn As String = "server=" & Global.ServerName & ";database=" &
    > Global.DatabaseName & ";integrated security=true"
    >
    > Dim objConn As New SqlConnection(strConn)
    > objConn.Open()
    >
    > Dim objCmd As New SqlCommand("spd_InsertCompany " & SQLInsert,
    > objConn)
    >
    > objCmd.CommandType = CommandType.StoredProcedure
    >
    > Dim para As New SqlParameter()
    > para.Direction = ParameterDirection.ReturnValue
    >
    > objCmd.Parameters.Add(para)
    >
    > objCmd.ExecuteNonQuery()
    >
    > objConn.Close()
    >
    > --
    >
    > para.Value should have your value then.
    >
    > Daniel
    >
     
    Nick, Feb 10, 2005
    #8
  9. Nick

    Nick Guest

    Ignore the last post again!!

    Return value doesn't work with a commandtype as text...I changed back to
    storedprocedure and added the parameters separately, and all seems to be
    working.

    Thanks very much for your time & patience!!

    Nick

    "Nick" <> wrote in message
    news:%...
    > Ok - Ignore that last message!
    >
    > I changed the CommandType to text because I was passing the sp parameters
    > in the same string, whereas the system was looking for an sp with the name
    > of the sp+the parameters.
    >
    > Unfortunately, the parameter value stays 0 rather than the actual identity
    > value... How do I check it is actually being returned from the sp?
    >
    > Nick
    >
    > "Daniel Buchholz" <> wrote in message
    > news:...
    >> Nick wrote:
    >>
    >>> Hi,
    >>> Where would I add the parameters part? (Or am I running the sp
    >>> incorrectly in the first place - although it does work...)

    >>
    >> Looks quite complicated.
    >>
    >> Perhaps try something like:
    >>
    >> Dim strConn As String = "server=" & Global.ServerName & ";database=" &
    >> Global.DatabaseName & ";integrated security=true"
    >>
    >> Dim objConn As New SqlConnection(strConn)
    >> objConn.Open()
    >>
    >> Dim objCmd As New SqlCommand("spd_InsertCompany " & SQLInsert,
    >> objConn)
    >>
    >> objCmd.CommandType = CommandType.StoredProcedure
    >>
    >> Dim para As New SqlParameter()
    >> para.Direction = ParameterDirection.ReturnValue
    >>
    >> objCmd.Parameters.Add(para)
    >>
    >> objCmd.ExecuteNonQuery()
    >>
    >> objConn.Close()
    >>
    >> --
    >>
    >> para.Value should have your value then.
    >>
    >> Daniel
    >>

    >
    >
     
    Nick, Feb 10, 2005
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. philip
    Replies:
    2
    Views:
    11,593
    bruce barker \(sqlwork.com\)
    Aug 10, 2006
  2. Skip Montanaro
    Replies:
    0
    Views:
    70
    Skip Montanaro
    Mar 12, 2014
  3. Ian Kelly
    Replies:
    0
    Views:
    68
    Ian Kelly
    Mar 12, 2014
  4. Ian Kelly
    Replies:
    0
    Views:
    65
    Ian Kelly
    Mar 12, 2014
  5. Petite Abeille
    Replies:
    5
    Views:
    74
    Chris Angelico
    Mar 13, 2014
Loading...

Share This Page