DAAB output pararmeters

[Guest]

Hello,

I'm using ASP.Net 2.0 and the Jan 2006 Enterprise Library.

What I'm doing is passing an ArrayList into a stroed procedure to do an
Insert. Here's some code:

Dim params as New ArrarList
params.Add("first parameter value")
params.Add("second parameter value")
etc.

db.ExecuteNonQuery(transaction, "sproc_Insert", params.ToArray)
tansaction.Commit()

Obviously some code is missing, which doesn't matter because the insert
works. The problem I'm not sure how to get around is how can I get the ID of
the inserted record out of hte stored procedure using this same method or is
there a way to add something to what I'm doing to get the @@indentity from
the SPROC.

Any suggestions are welcomed.

Thanks
Clint Pidlubny

 

[sloan]

Something along these lines.


db.AddOutParameter( "@numberRowsAffected", DbType.Int32, 0)

Dim rowsAffected As Int32

rowsAffected = db.ExecuteNonQuery()

' Row of data is captured via output parameters
Dim results As String =
String.Format(CultureInfo.CurrentCulture, "{0}", db.GetParameterValue(
"@numberRowsAffected"))


"@numberRowsAffected is if you declare an output parameter for the stored
procedure and populate it

rowsAffected works also, BUT doesn't work against select queries.



I usually include the output parameter and populate that way, if I want
IDENTITY or any other value.

 

[Guest]

Thanks for trying but that isn't what I'm looking for.

I'm only passing an ArrayList and letting SQL Server match up the
parameters, so I'm not specifically stating the parameters. I actually don't
have a problem getting the # of rows affected, but what I'm not sure about is
how to pass the @@identity when I'm only passing an ArrayList for the
parameters.

It might not be possible, but I thought I'd check.

Thanks anyways.

Clint

 

[Mark Fitzpatrick]

You'll have to pass it in some parameter form. You'll need to either use the
return result parameter (if the identity is an integer) or another parameter
defined as an output.

Also, don't use @@IDENTITY. What you want is SCOPE_IDENTITY(). Using
@@Identity returns the last insert record, anywhere. If another insert
happens at close to the same time SQL Server will grab that one. Using
SCOPE_IDENTITY() ensures that the identity being returned is the last one
that was added within the scope of the current operation, such as the
current stored procedure

 

[Guest]

Thanks Mark. I didn't know that about @@Identity. Have you ever tried passing
a parameter through an ArrayList, like I describe, or do I have to take a
different approach if I want to grab an identity?

Clint