DAAB output pararmeters

[Clint Pidlubny]

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.


"Clint Pidlubny" <Clint Pi******@discussions.microsoft.comwrote in message
news:82**********************************@microsof t.com...

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

[Clint Pidlubny]

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

"sloan" wrote:

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.


"Clint Pidlubny" <Clint Pi******@discussions.microsoft.comwrote in message
news:82**********************************@microsof t.com...

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

[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
--
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

"Clint Pidlubny" <Cl***********@discussions.microsoft.comwrote in message
news:D8**********************************@microsof t.com...

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

"sloan" wrote:

>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.


"Clint Pidlubny" <Clint Pi******@discussions.microsoft.comwrote in
message
news:82**********************************@microso ft.com...

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

[Clint Pidlubny]

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

"Mark Fitzpatrick" wrote:

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
--
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006