Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Stored Procedure Return value question

Reply
Thread Tools

Stored Procedure Return value question

 
 
Nick
Guest
Posts: n/a
 
      02-10-2005
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


 
Reply With Quote
 
 
 
 
Daniel Buchholz
Guest
Posts: n/a
 
      02-10-2005
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

 
Reply With Quote
 
 
 
 
Eliyahu Goldin
Guest
Posts: n/a
 
      02-10-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Nick
Guest
Posts: n/a
 
      02-10-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
Daniel Buchholz
Guest
Posts: n/a
 
      02-10-2005
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

 
Reply With Quote
 
Nick
Guest
Posts: n/a
 
      02-10-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
Daniel Buchholz
Guest
Posts: n/a
 
      02-10-2005
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

 
Reply With Quote
 
Nick
Guest
Posts: n/a
 
      02-10-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
Nick
Guest
Posts: n/a
 
      02-10-2005
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to detect return value from stored procedure used by a Details =?Utf-8?B?QmlzaG95?= ASP .Net 1 12-24-2006 05:54 PM
stored procedure return value question philip ASP .Net 2 08-10-2006 03:42 PM
Is there a possibility to call a stored procedure on the MSSQL DB without java to wait for a return value? gagu911@gmx.ch Java 3 11-30-2005 03:59 PM
Return a value from stored procedure to calling application Paul ASP .Net 1 01-19-2004 05:06 PM
Stored Procedure has both return value and data set (SqlDataReader) Neo Chou ASP .Net 1 08-03-2003 06:23 AM



Advertisments