output param returns array

A

alex

this code below returns an int array (length=8) from the sql server 2K
proc. I have NO idea why... any suggestions?

----------------------------------------------------------------------------

Address a = address;
SqlParameter[] param = new SqlParameter[12];
int x = 0;

AddParam(param, "@p_AddressTypeId", a.AddressTypeID, x++);
AddParam(param, "@p_POBox", a.POBox, 50, x++);
AddParam(param, "@p_AddressCO", a.CareOf, 50, x++);
AddParam(param, "@p_Address", a.AddressLine1, 50, x++);
AddParam(param, "@p_AddressLine2", a.AddressLine2, 50, x++);
AddParam(param, "@p_City", a.City, 50, x++);
AddParam(param, "@p_USState", a.USState, x++);
AddParam(param, "@p_Province", a.Province, 50, x++);
AddParam(param, "@p_PostalCode", a.PostalCode, 10, x++);
AddParam(param, "@p_ZIP4", a.ZIP4, 4, x++);
AddParam(param, "@p_CountryId", a.Country, x++);
AddParam(param, "@p_AddressId", null, x);

param[x].Direction = ParameterDirection.Output;

ExecuteNonQuery(CommandType.StoredProcedure, "prAddressAdd", param);

object val = param[x].Value; <== object is array!!!!

----------------------------------------------------------------------------

create PROCEDURE prAddressAdd
@p_AddressTypeId TINYINT
,@p_POBox VARCHAR(50)
,@p_AddressCO VARCHAR(50)
,@p_Address VARCHAR(50)
,@p_AddressLine2 VARCHAR(50)
,@p_City VARCHAR(50)
,@p_USState TINYINT
,@p_Province VARCHAR(50)
,@p_PostalCode VARCHAR(10)
,@p_ZIP4 VARCHAR(4)
,@p_CountryId INT
,@p_AddressId INT OUTPUT
AS

SET @p_AddressId = NULL

INSERT INTO tb_ADDRESS(
AddressTypeId
,POBox
,AddressCO
,Address
,AddressLine2
,City
,USState
,Province
,PostalCode
,ZIP4
,CountryId
)
VALUES(
@p_AddressTypeId
,@p_POBox
,@p_AddressCO
,@p_Address
,@p_AddressLine2
,@p_City
,@p_USState
,@p_Province
,@p_PostalCode
,@p_ZIP4
,@p_CountryId
)

SET @p_AddressId = SCOPE_IDENTITY()
 
A

Alex

sorry if I sound ignorant... could you show me how to grab the int from the
code example below?

Thanks so much!

Alex.


Miha Markic said:
Did you try definining it as SqlDbType.Int?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

alex said:
this code below returns an int array (length=8) from the sql server 2K
proc. I have NO idea why... any suggestions?

--------------------------------------------------------------------------
Click to expand...
--
Address a = address;
SqlParameter[] param = new SqlParameter[12];
int x = 0;

AddParam(param, "@p_AddressTypeId", a.AddressTypeID, x++);
AddParam(param, "@p_POBox", a.POBox, 50, x++);
AddParam(param, "@p_AddressCO", a.CareOf, 50, x++);
AddParam(param, "@p_Address", a.AddressLine1, 50, x++);
AddParam(param, "@p_AddressLine2", a.AddressLine2, 50, x++);
AddParam(param, "@p_City", a.City, 50, x++);
AddParam(param, "@p_USState", a.USState, x++);
AddParam(param, "@p_Province", a.Province, 50, x++);
AddParam(param, "@p_PostalCode", a.PostalCode, 10, x++);
AddParam(param, "@p_ZIP4", a.ZIP4, 4, x++);
AddParam(param, "@p_CountryId", a.Country, x++);
AddParam(param, "@p_AddressId", null, x);

param[x].Direction = ParameterDirection.Output;

ExecuteNonQuery(CommandType.StoredProcedure, "prAddressAdd", param);

object val = param[x].Value; <== object is array!!!!

--------------------------------------------------------------------------
Click to expand...
--
create PROCEDURE prAddressAdd
@p_AddressTypeId TINYINT
,@p_POBox VARCHAR(50)
,@p_AddressCO VARCHAR(50)
,@p_Address VARCHAR(50)
,@p_AddressLine2 VARCHAR(50)
,@p_City VARCHAR(50)
,@p_USState TINYINT
,@p_Province VARCHAR(50)
,@p_PostalCode VARCHAR(10)
,@p_ZIP4 VARCHAR(4)
,@p_CountryId INT
,@p_AddressId INT OUTPUT
AS

SET @p_AddressId = NULL

INSERT INTO tb_ADDRESS(
AddressTypeId
,POBox
,AddressCO
,Address
,AddressLine2
,City
,USState
,Province
,PostalCode
,ZIP4
,CountryId
)
VALUES(
@p_AddressTypeId
,@p_POBox
,@p_AddressCO
,@p_Address
,@p_AddressLine2
,@p_City
,@p_USState
,@p_Province
,@p_PostalCode
,@p_ZIP4
,@p_CountryId
)

SET @p_AddressId = SCOPE_IDENTITY()

--------------------------------------------------------------------------
Click to expand...
--
Thanks for the help!
Click to expand...
Click to expand...
 
M

Miha Markic [MVP C#]

Hi Alex,

I am not sure what helper functions you are using, but I would use
constructor
param[x++] = new SqlParameter("@p_AddressId", SqlDbType.Int);

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Alex said:
sorry if I sound ignorant... could you show me how to grab the int from
the
code example below?

Thanks so much!

Alex.


Miha Markic said:
Did you try definining it as SqlDbType.Int?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

alex said:
this code below returns an int array (length=8) from the sql server 2K
proc. I have NO idea why... any suggestions?

--------------------------------------------------------------------------
Click to expand...
--
Address a = address;
SqlParameter[] param = new SqlParameter[12];
int x = 0;

AddParam(param, "@p_AddressTypeId", a.AddressTypeID, x++);
AddParam(param, "@p_POBox", a.POBox, 50, x++);
AddParam(param, "@p_AddressCO", a.CareOf, 50, x++);
AddParam(param, "@p_Address", a.AddressLine1, 50, x++);
AddParam(param, "@p_AddressLine2", a.AddressLine2, 50, x++);
AddParam(param, "@p_City", a.City, 50, x++);
AddParam(param, "@p_USState", a.USState, x++);
AddParam(param, "@p_Province", a.Province, 50, x++);
AddParam(param, "@p_PostalCode", a.PostalCode, 10, x++);
AddParam(param, "@p_ZIP4", a.ZIP4, 4, x++);
AddParam(param, "@p_CountryId", a.Country, x++);
AddParam(param, "@p_AddressId", null, x);

param[x].Direction = ParameterDirection.Output;

ExecuteNonQuery(CommandType.StoredProcedure, "prAddressAdd", param);

object val = param[x].Value; <== object is array!!!!

--------------------------------------------------------------------------
Click to expand...
--
create PROCEDURE prAddressAdd
@p_AddressTypeId TINYINT
,@p_POBox VARCHAR(50)
,@p_AddressCO VARCHAR(50)
,@p_Address VARCHAR(50)
,@p_AddressLine2 VARCHAR(50)
,@p_City VARCHAR(50)
,@p_USState TINYINT
,@p_Province VARCHAR(50)
,@p_PostalCode VARCHAR(10)
,@p_ZIP4 VARCHAR(4)
,@p_CountryId INT
,@p_AddressId INT OUTPUT
AS

SET @p_AddressId = NULL

INSERT INTO tb_ADDRESS(
AddressTypeId
,POBox
,AddressCO
,Address
,AddressLine2
,City
,USState
,Province
,PostalCode
,ZIP4
,CountryId
)
VALUES(
@p_AddressTypeId
,@p_POBox
,@p_AddressCO
,@p_Address
,@p_AddressLine2
,@p_City
,@p_USState
,@p_Province
,@p_PostalCode
,@p_ZIP4
,@p_CountryId
)

SET @p_AddressId = SCOPE_IDENTITY()

--------------------------------------------------------------------------
Click to expand...
--
Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 
Top