471,071 Members | 1,235 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

DAAB output pararmeters

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
Nov 9 '06 #1
4 3293
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

Nov 9 '06 #2
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


Nov 9 '06 #3
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



Nov 9 '06 #4
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
Nov 13 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by veera sekhar kota | last post: by
reply views Thread by veera sekhar kota | last post: by
reply views Thread by veera sekhar kota | last post: by
7 posts views Thread by Alec MacLean | last post: by
5 posts views Thread by | last post: by
reply views Thread by dobee | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.