Output param trouble w/ data access block

[Kurt Mang]

Sorry - previous post sent on mistake.

Anyways, I can't get the output parameter value back when
I use the DAL application block v. 2.0.

I pass an enumerated value instead of the index value to
the parameter array ("params") for better readability.

THE CODE:
===
Dim params(2) As SqlParameter params =
SqlHelperParameterCache.GetSpParameterSet
("p_Member_InsertUpdate", False)

params(p_Member_InsertUpdate.UserName).Value = UserName
params(p_Member_InsertUpdate.Pass).Value = Password
' output param:
params(p_Member_InsertUpdate.NewID).SqlDbType =
SqlDbType.Int
params(p_Member_InsertUpdate.NewID).Direction =
ParameterDirection.Output

SR.SqlHelper.ExecuteNonQuery
(CommandType.StoredProcedure, "p_Member_InsertUpdate",
params)
MemberID = ctype(params
(p_Member_InsertUpdate.NewID).Value, Integer)

===

I never get the parameter value back. Why not? Any
takers?

Thanks - Kurt

 

[Kevin Sun [MS]]

Some suggestions:

1. Run the stored procedure directly in Query Analyzer or other client tool
to see if the returned parameter works.

2. Made the syntax changes and ensured that the parameter object for the
return value is the first item in the Parameters collection per article

"Q308624 PRB: Output Parameters Not Returned w/ ADO.NET Command (VC++)"
http://support.microsoft.com/default.aspx?scid=KB;EN-US;308624

Additionally, ensure that the parameter's data type matches that of the
expected return value.

3. Make sure the latest version of the updates are applied:

- MDAC 2.8
- .Net Frameworks 1.1
- Microsoft Application Block 2.0

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Content-Class: urn:content-classes:message
| From: "Kurt Mang" <[email protected]>
| Sender: "Kurt Mang" <[email protected]>
| Subject: Output param trouble w/ data access block
| Date: Sun, 21 Sep 2003 15:11:23 -0700
| Lines: 34
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcOAjUtTJHSDppgfRA631/DEEHGdcA==
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:61735
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Sorry - previous post sent on mistake.
|
| Anyways, I can't get the output parameter value back when
| I use the DAL application block v. 2.0.
|
| I pass an enumerated value instead of the index value to
| the parameter array ("params") for better readability.
|
| THE CODE:
| ===
| Dim params(2) As SqlParameter params =
| SqlHelperParameterCache.GetSpParameterSet
| ("p_Member_InsertUpdate", False)
|
| params(p_Member_InsertUpdate.UserName).Value = UserName
| params(p_Member_InsertUpdate.Pass).Value = Password
| ' output param:
| params(p_Member_InsertUpdate.NewID).SqlDbType =
| SqlDbType.Int
| params(p_Member_InsertUpdate.NewID).Direction =
| ParameterDirection.Output
|
| SR.SqlHelper.ExecuteNonQuery
| (CommandType.StoredProcedure, "p_Member_InsertUpdate",
| params)
| MemberID = ctype(params
| (p_Member_InsertUpdate.NewID).Value, Integer)
|
| ===
|
| I never get the parameter value back. Why not? Any
| takers?
|
| Thanks - Kurt
|

 

[Adam Boczek]

It is seem to be true that DAL can't pass output parameter (and also
RETURN_VALUE). I've seen code and it is easy to find such code like:

int retval = cmd.ExecuteNonQuery();
// Detach the SqlParameters from the command object, so they can be used
again
cmd.Parameters.Clear();

so we have no chance to get output parameters. That's quite uncool :-( I
thought I can use DAL for my application. Now I have to write my own "DB
Class"...

Adam