Stored Procedure Output Paramter

[Tamer Ibrahim]

Hi,
I'm writing a web application using Scott Mitchell's Data Access Tutorials
as my principal guide.
In my BLL I'm trying to write a AddGiftItem method that has ItemCode as an
output paramter
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert,
true)]

public string AddGiftItem(string title, string detail, DateTime createdOn,
Byte sectionID, string categoryID)

{

DataSetViewGrp.GiftItemsDataTable giftItems = new
DataSetViewGrp.GiftItemsDataTable();

DataSetViewGrp.GiftItemsRow giftItem = giftItems.NewGiftItemsRow();

if (title == null) giftItem.SetTitleNull(); else giftItem.Title = title;

if (detail == null) giftItem.SetDetailNull(); else giftItem.Detail = detail;

giftItem.CreatedOn = createdOn;

giftItem.SectionID = sectionID;

giftItem.CategoryID = categoryID;

giftItems.AddGiftItemsRow(giftItem);

string itemCode = Adapter.Update(giftItems).ToString();

return itemCode ;

}

and here is my stored procedure that do the insert

ALTER PROCEDURE dbo.spGiftItemsInsert

(

@Title nvarchar(300),

@Detail nvarchar(4000),

@CreatedOn datetime,

@SectionID tinyint,

@ItemCode nvarchar(6) output,

@CategoryID nvarchar(2)

)

AS

SET NOCOUNT OFF;

BEGIN TRAN

INSERT INTO [Contents] ([Title], [Detail], [CreatedOn], [SectionID]) VALUES
(@Title, @Detail, @CreatedOn, @SectionID);

INSERT INTO GiftItems (ContentID, ItemCode, CategoryID) VALUES
(SCOPE_IDENTITY(),dbo.fnItemCode(@CategoryID),@CategoryID);


SET @ItemCode = dbo.fnItemCode(@CategoryID);

COMMIT TRAN

How can I get this work out ?

Thank You.

 

[Guest]

Hi,
I'm writing a web application using Scott Mitchell's Data Access Tutorials
as my principal guide.
In my BLL I'm trying to write a AddGiftItem method that has ItemCode as an
output paramter
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.Data­ObjectMethodType.Insert,
true)]

public string AddGiftItem(string title, string detail, DateTime createdOn,
Byte sectionID, string categoryID)

{

DataSetViewGrp.GiftItemsDataTable giftItems = new
DataSetViewGrp.GiftItemsDataTable();

DataSetViewGrp.GiftItemsRow giftItem = giftItems.NewGiftItemsRow();

if (title == null) giftItem.SetTitleNull(); else giftItem.Title = title;

if (detail == null) giftItem.SetDetailNull(); else giftItem.Detail = detail;

giftItem.CreatedOn = createdOn;

giftItem.SectionID = sectionID;

giftItem.CategoryID = categoryID;

giftItems.AddGiftItemsRow(giftItem);

string itemCode = Adapter.Update(giftItems).ToString();

return itemCode ;

}

and here is my stored procedure that do the insert

ALTER PROCEDURE dbo.spGiftItemsInsert

(

@Title nvarchar(300),

@Detail nvarchar(4000),

@CreatedOn datetime,

@SectionID tinyint,

@ItemCode nvarchar(6) output,

@CategoryID nvarchar(2)

)

AS

SET NOCOUNT OFF;

BEGIN TRAN

INSERT INTO [Contents] ([Title], [Detail], [CreatedOn], [SectionID]) VALUES
(@Title, @Detail, @CreatedOn, @SectionID);

INSERT INTO GiftItems (ContentID, ItemCode, CategoryID) VALUES
(SCOPE_IDENTITY(),dbo.fnItemCode(@CategoryID),@CategoryID);

SET @ItemCode = dbo.fnItemCode(@CategoryID);

COMMIT TRAN

How can I get this work out ?

Thank You.

Hi Tamer,

What is the problem here exactly? Does your stored procedure work (you
can check it against database)? Does your code return an error?

 

[Murtaza Iqbal Gandhi]

Hi,
I'm writing a web application using Scott Mitchell's Data Access Tutorials
as my principal guide.
In my BLL I'm trying to write a AddGiftItem method that has ItemCode as an
output paramter
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert,
true)]

public string AddGiftItem(string title, string detail, DateTime createdOn,
Byte sectionID, string categoryID)

{

DataSetViewGrp.GiftItemsDataTable giftItems = new
DataSetViewGrp.GiftItemsDataTable();

DataSetViewGrp.GiftItemsRow giftItem = giftItems.NewGiftItemsRow();

if (title == null) giftItem.SetTitleNull(); else giftItem.Title = title;

if (detail == null) giftItem.SetDetailNull(); else giftItem.Detail = detail;

giftItem.CreatedOn = createdOn;

giftItem.SectionID = sectionID;

giftItem.CategoryID = categoryID;

giftItems.AddGiftItemsRow(giftItem);

string itemCode = Adapter.Update(giftItems).ToString();

return itemCode ;

}

and here is my stored procedure that do the insert

ALTER PROCEDURE dbo.spGiftItemsInsert

(

@Title nvarchar(300),

@Detail nvarchar(4000),

@CreatedOn datetime,

@SectionID tinyint,

@ItemCode nvarchar(6) output,

@CategoryID nvarchar(2)

)

AS

SET NOCOUNT OFF;

BEGIN TRAN

INSERT INTO [Contents] ([Title], [Detail], [CreatedOn], [SectionID]) VALUES
(@Title, @Detail, @CreatedOn, @SectionID);

INSERT INTO GiftItems (ContentID, ItemCode, CategoryID) VALUES
(SCOPE_IDENTITY(),dbo.fnItemCode(@CategoryID),@CategoryID);


SET @ItemCode = dbo.fnItemCode(@CategoryID);

COMMIT TRAN

How can I get this work out ?

Thank You.

your problem must be that you havent passed an

itemcode empty parameter

check it using a parameter for itemcode
and direction as output