Stored Procedure Output Paramter

  • Thread starter Thread starter Tamer Ibrahim
  • Start date Start date
T

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.
 
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.
Click to expand...

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?
 
Tamer said:
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.
Click to expand...
your problem must be that you havent passed an

itemcode empty parameter

check it using a parameter for itemcode
and direction as output
 
Back
Top