Stored Procedure Return value question

[Nick]

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

[Daniel Buchholz]

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

[Eliyahu Goldin]

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" <Ni**@NTWorks.no.spam.fsnet.co.uk> wrote in message
news:eA*************@TK2MSFTNGP15.phx.gbl...

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]

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" <ne**************@paranor.de> wrote in message
news:37*************@individual.net...

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]

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]

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" <ne**************@paranor.de> wrote in message
news:37*************@individual.net...

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]

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

[Nick]

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" <ne**************@paranor.de> wrote in message
news:37*************@individual.net...

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]

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" <Ni**@NTWorks.no.spam.fsnet.co.uk> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...

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" <ne**************@paranor.de> wrote in message
news:37*************@individual.net...

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