How to get return value or output value after insert new record (Scott's tutor used BLL)

I ve created a DAL and BLL following Scott s tutorials and I don't know how to get return value or output value after insert new record.

In Stored Procudure: INSERT INTO .... RETURN SCOPE_IDENTITY()

or : I declare out put variable: @newRecordId INT OUTPUT

then at the end SP: SET @newRecordId = SCOPE_IDENTITY()

in the BLL, I have used addnew function following the Scott' tutor.

But I don't know how to get newRecordId.

Someone can help me,

Thank a lot 

 

0
thinhhangdieu
8/27/2007 10:10:46 AM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

9 Replies
2325 Views

Similar Articles

[PageSpeed] 11

Here's an example for you:

ASPX 

<asp:detailsview id="dvSuppliers" runat="server" autogenerateinsertbutton="True"
	autogeneraterows="False" datakeynames="ShipperID" datasourceid="sdsSuppliers"
	defaultmode="Insert">
	<headertemplate>
		Add Shipper
	</headertemplate>
	<fields>
		<asp:boundfield datafield="ShipperID" headertext="ShipperID" insertvisible="False"
			readonly="True" />
		<asp:boundfield datafield="CompanyName" headertext="CompanyName" />
		<asp:boundfield datafield="Phone" headertext="Phone" />
	</fields>
</asp:detailsview>
<asp:sqldatasource id="sdsSuppliers" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
	insertcommand="sp_Shippers_Insert" insertcommandtype="StoredProcedure" oninserted="sdsSuppliers_Inserted"
	selectcommand="sp_Shippers_GetAll" selectcommandtype="StoredProcedure">
	<insertparameters>
		<asp:parameter name="CompanyName" />
		<asp:parameter name="Phone" />
		<asp:parameter direction="ReturnValue" name="ShipperID" type="Int32" />
	</insertparameters>
</asp:sqldatasource>
<asp:label id="lblShipperId" runat="server" />

CODE-BEHIND 

protected void sdsSuppliers_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
	if (e.Exception == null)
	{
		lblShipperId.Text = String.Concat("New ShipperID: ", e.Command.Parameters["@ShipperID"].Value);
	}
}

SP 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Shippers_Insert]
(
	@CompanyName AS NVARCHAR(40),
	@Phone AS NVARCHAR(24) = NULL
)

AS

BEGIN
	INSERT INTO [dbo].[Shippers]
	(
		[CompanyName], 
		[Phone]
	)
    VALUES
	(
		@CompanyName,
		@Phone
	)

	RETURN SCOPE_IDENTITY()
END

Thanks, Ed

Microsoft MVP - ASP/ASP.NET

0
ecbruck
8/27/2007 12:43:21 PM

Hi  ecbruck, thank you very much!

But I have used ObjectDataSource then this ODS call down insert function in BLL, I did not use SQLDatasource

So could you show me how to get new id when used  ObjectDataSource and BLL(Scott's tutor method) to Insert new record ?

This my insert function in BLL:

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] 

public bool addNewRec( string entName, string address)
    {

        MyDataSet.MyDataTable myDataTable = new MyDataSet.MyDataTable();
        MyDataSet.MyRow myRow = myDataTable.NewMyRow();
       
        if (myRow == null)
            myRow .SetentNameNull();
        else
            myRow .entName = entName;

        if (address == null)
            myRow .SetaddressNull();
        else
            myRow .address = address;

        /   /add new row

        myDataTable.AddMyRow(myRow);

        int rowsAffected = myAdapter.Update(myDataTable);
       
        return rowsAffected == 1; 

    } 

In this way, I don't know how to get new Id inserted. Show me please!
Thanks!

PS: Now perhaps I will use your method, which is used SQLDataSource.

0
thinhhangdieu
8/28/2007 1:44:54 AM

One more example:

ASPX 

<asp:detailsview id="dvSuppliers" runat="server" autogenerateinsertbutton="True"
	autogeneraterows="False" datakeynames="ShipperID" datasourceid="odsSuppliers"
	defaultmode="Insert">
	<headertemplate>
		Add Shipper
	</headertemplate>
	<fields>
		<asp:boundfield datafield="ShipperID" headertext="ShipperID" insertvisible="False"
			readonly="True" />
		<asp:boundfield datafield="CompanyName" headertext="CompanyName" />
		<asp:boundfield datafield="Phone" headertext="Phone" />
	</fields>
</asp:detailsview>
<asp:objectdatasource id="odsSuppliers" runat="server" insertmethod="AddShipper"
	oninserted="odsSuppliers_Inserted" selectmethod="GetAllShippers" typename="ShippersBLL">
	<insertparameters>
		<asp:parameter name="companyName" type="String" />
		<asp:parameter name="phone" type="String" />
	</insertparameters>
</asp:objectdatasource>
<asp:label id="lblShipperId" runat="server" />

CODE-BEHIND 

protected void odsSuppliers_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
{
	if (e.Exception == null)
	{
		lblShipperId.Text = String.Concat("New ShipperID: ", e.ReturnValue);
	}
}

BLL 

[DataObjectMethod(DataObjectMethodType.Insert, true)]
public int AddShipper(string companyName, string phone)
{
	SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

	SqlCommand command = new SqlCommand("sp_Shippers_Insert", connection);
	command.CommandType = CommandType.StoredProcedure;
	command.Parameters.AddWithValue("CompanyName", companyName);
	command.Parameters.AddWithValue("Phone", phone);
	command.Connection.Open();

	try
	{
		return Convert.ToInt32(command.ExecuteScalar());
	}
	finally
	{
		if (connection != null) { connection.Close(); }
	}
} // AddShipper

STORED PROCEDURE 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Shippers_Insert]
(
	@CompanyName AS NVARCHAR(40),
	@Phone AS NVARCHAR(24) = NULL
)

AS

BEGIN
	INSERT INTO [dbo].[Shippers]
	(
		[CompanyName], 
		[Phone]
	)
    VALUES
	(
		@CompanyName,
		@Phone
	)

	SELECT SCOPE_IDENTITY()
END

Thanks, Ed

Microsoft MVP - ASP/ASP.NET

0
ecbruck
8/28/2007 12:43:50 PM

Hi!

Are you using a dataset (*.xsd)?
If yes you only have to add a new "INSERT" Query on the tableadapter. There you set the ExecuteMode on "Scalar".
(same as Scott shows on http://www.asp.net/learn/data-access/tutorial-01-vb.aspx)
After that you make in the BLL a function (sorry I am on VB)

Public Function InsertProductsWithIDBack(........) As Integer

Dim newID As Integer = Adapter.InsertProducts(........)

Return newID

End Function

I think you cant get the ID with adding a new row in the table and Adapter.Update().
You have to use your own insert query.

Hope this helps!

0
Stefan
8/28/2007 1:09:38 PM

Thank Stefan  and ecbruck very much.

I use  Stefan's method, because I want to use Adapter which was created in .xsd file.

In the BLL:

//insert has return value SCOPE_IDENTITY()
    [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
    public int addNewWithRetVal( string entName, string address)
    {

       int retVal = Convert.ToInt32(myAdapter.INSERT(entName, address));
        return retVal ;

    } 

and in code behide:

protected void MyODS_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
    {
       
        lblMsg.Text ="You've added a new record with id is: " +  e.ReturnValue.ToString();
       
    }

The Insert query in the TableAdapter has ExcuteMode is "Scalar".

And there is a note that in the Stored Procedure I must used last statement like: SELECT SCOPE_IDENTITY() instead of RETURN SCOPE_IDENTITY() to get newId return.

So I didn't know how to do if I used  RETURN SCOPE_IDENTITY() statement in SP???

Thank you all very much!

 

0
thinhhangdieu
8/30/2007 6:45:33 AM

It also doesn't work with SELECT SCOPE_IDENTITY()?

Maybe you can do this with an Output Paramter in the SP.

0
Stefan
8/31/2007 11:13:08 AM

Hi Stefan,

It work with  SELECT SCOPE_IDENTITY() but not work with RETURN SCOPE_IDENTITY()

 

0
thinhhangdieu
9/1/2007 10:38:38 AM

I have the same question ...using Scott's tutorial - created biz layer dal etc. But there's no example of how to get the id in the BLL after row insert.

eg.,

[System.ComponentModel.DataObjectMethodAttribute

(System.ComponentModel.DataObjectMethodType.Insert, true)]

public bool AddReport(string reportName, string reportDescription,

string districtCode)

{

// Create a new ReportRow instance

CompMon.ReportsDataTable reports = new CompMon.ReportsDataTable();CompMon.ReportsRow report = reports.NewReportsRow();

report.ReportName = reportName;

report.ReportDescription = reportDescription;

report.DistrictCode = districtCode;

// Add the new report

reports.AddReportsRow(report);

int rowsAffected = Adapter.Update(reports);

// Return true if precisely one row was inserted,

// otherwise false

return rowsAffected == 1;

}

 Where in this code can I access the ID of the newly created db row in the Reports table. Which has Select Scope_Identity() in the DAL insert command.

-1
spherop2
9/6/2007 12:44:00 AM

Hi!

You have to add a new "INSERT" Query (name = InsertReport) on your tableadapter ReportsDataTable. Like that:

INSERT INTO [reports] ([reportName], [reportDescription], [districtCode]) VALUES (@reportName, @reportDescription, @districtCode);
select SCOPE_IDENTITY()

There you set the ExecuteMode on "Scalar".

After that you add a new function in the BLL:

public int AddReportWithIDBack(string reportName, string reportDescription, string districtCode)
{
int reportID = Adapter.InsertReport(reportName, reportDescription, districtCode);
return reportID;
}

 Hope that helps you.

0
Stefan
9/6/2007 6:58:52 AM
Reply:

Similar Artilces:

How to get store procedure's return value and output parameters from BLL?
ex: myprocedure(@Cusname varchar(50), @Cusid int output)as Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY  i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter { get {if (_CuslistAdapter == null) _CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter; } } Now how i write function in BLL to receive output paramter from creatcustomer function? Hi minhpg, Not very sure about what your code mean. Maybe you should e...

new to .net, need help setting db values from checkbox's then retrieving value to a label control
Hi, Being new to asp.net i know this possible i just can't quite get it right!  I need a page with 5 checkbox's that when submitted sets the bit values in DB fields to checked. Then i need another page that retrieves the records of the checkboxes, but instead of having the db pre-set checkboxes on the second page i want it to turn on label web controls depending on if the check box is set.. I hope that makes sense?!! Can anyone point me in the right direction or to a suitable tutorial? Thanks in advance Ben Excellent article here.http://www.heliosfx.com...

Default value for Dropdownlist that get's it's value from Database.
Hi, I am using an Object Data Souce to retrieve the value of a Dropdownlist from a table on MS SQL server 2005. I want to make a default value appear on the Dropdownlist intially, should i enter the default value into the database table or is that a better way of doing it? Any suggestions will be very much appreciated. E  No need for database just set sekected value after databingind protected void Page_Load(object sender, EventArgs e) { DataBind(); ListItem defaultItem = new ListItem("Select a Person", "-1"); th...

How to get return value or output value from stored procedure with sqldatasource control?
I created a user registration web page with sqldatasource, some textbox controls or submit button web control, I applied below stored procedure in insert command of sqldatasource control, but how can I get return value from stored procedure after clicking submit button, I want to judge whether data has been saved into database by return value, then give user a message. Who can tell me? thanks much. ALTER PROCEDURE UserInsert ( @Email nvarchar(50), @Password nvarchar(50), @FullName nvarchar(50), @Phone nvarchar(50), @Mobile nvarchar(50), @Address nvarchar(100), ...

Viewstate : Comparing a controls previous value to it's new value
I'd like to compare the original values of a pages web controls to their new values upon postback. The problem is I can't seem to get any values from viewstate. for example some psudocode; sub pageinit() myVar = cint(viewstate("dropdownListcontrolID")) myvar2 = cint(viewstate("textBoxControLID")) end sub This doesn't error out, but all values = 1, which cannot be the case. I must be missing something. Viewstate seems like the most logical place to look for the controls previous values. Or do you recommend I do it a different way? ...

Use value(s) from gridview to insert new record into table.
asp.net2 (vb) I have a gridview and would like to insert new record using data from a current one. The only problem Im having is actually getting a nice reference to a current record. It needs to create a record using a sub group within a main group.  So I have the gridview, it works, I can insert new data, it works.  But I'd like to be able to click on my custom Add button (which is against each row) to insert another record based on the one which had the "Add" button clicked. Do I need to add the CommandName="select" to the "Add" button, to force the selected value, then do t...

get external process's output *and* return value
Is there a way to get an external process's output *and* return value? I have a program that opens a pipe to 'make', process all the input, and then checks the return value when the loop ends (pipe is broken/closed): open MAKE, "make |" or die "Can't open MAKE pipe"; while(<MAKE>) { # Process input } if($make_return_value) { # An error occured with 'make' } How do I get that return value? -- Andrew Gaffney Network Administrator Skyline Aeronautics, LLC. 636-357-1548 > Is there a way to get an external ...

ObjectDataSource 'odsUser' has no values to insert. Check that the 'values' dictionary contains values
Hi All! I am currently trying to pull 2 values from two different sources (Details View & a Session) to use in a Stored Proceedure.  I have spent quite a bit of time trying to determine what is occuring without any luck. However, by the Subject Line you can tell that it's not getting anything for some reason?  Here are the components:Object Datasource:<asp:ObjectDataSource ID="odsUser" runat="server" DataObjectTypeName="System.Nullable`1[[System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=************]]"DeleteMethod="RemoveEnrollment...

Accessing return value of BLL's method using objectdatasource.....
Hi, I have a method in BLL  named 'GetProductCount()'  which return a int value(total numbers of products). How can i access this return value in my presentation layer using objectdatasource.  Thanks.“Do not go where the path may lead, go instead where there is no path and leave a trail.”- Waldo Emerson Hi WebEye, Based on your description, I guess you may use the paging feature in ObjectDataSource, right? If so, a good practice is to cache the int value when calling the GetProductCount method in BLL. There're two benifits here: Y...

Getting the identity integer value of a newly inserted record when using the formview control
I am using Visual Studio 2005, ASP.NET 2.0 and SQL Server 2005I have a table with the first column as an identity integer. When using the formview control and I click the insert button to insert a new record how can I get the identity integers value of the newly inserted record?? Thanks Check out the ExecuteScalar method of the SQLCommand objecthttp://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspxMcKelt Solutions Thanks All we had to do in ASP.NET 1.1 was DataSetCorporateForm1.tblCorporate(0).CorpID after a record was inserted, SqlDataAdapter...

How can I get a TypedDataSet's AutoIncrement value after inserting new row?
I have a TypedDataSet in my project. There is [Id] column with AutoIncrement  == True. After inserting new row in the one (data - TypedDataSet): data.Directories.AddDirectoriesRow(dir.Name, parentId); how can I get  [Id] (auto incremented) of the new row?"A successful person is one who can lay a firm foundation with the bricks that others throw at him." http://www.russiangeek.com/ I have a walkthrough here that talks about using TypedDataSets: http://weblogs.asp.net/scottgu/archive/2006/01/15/435498.aspx One thing you should be able to-do is append a "select @...

I can't get the return value from a Stored Procedure using a DAL and BLL
HI All: I can't get the return value from a Stored Procedure using a DAL and BLL. I created a DAL with a method that call a Stored procedure that insert a row and return 1 if was succefully and 0 if not. Then I created a BLL that call the DAL method but I can't get the return value.   [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] public Object NewCountry(int CountryId, string CountryName, string Abbrev, string UserId) { Int obj; obj = Adapter.NewCountry(CountryId, CountryName, Abbrev, UserId); return obj; } Can...

Net::LDAP::Control returns 'ASN' value?
Hello, I've got a question about the behavior of Net::LDAP::Control and it's child classes. =20 Should a call to $control->value() return an ASN value? I instantiated a few controls to see their behavior and they are all the same. They return an ASN value. I guess I expected the original, untainted, value to be returned. (The value of Net::LDAP::Control->value() is untouched but I suspect that is because it is a simplified stub for subclasses.) Thanks, -David Faltermier david.faltermier@hp.com #--------------------------------------- # Testing Net::LDAP:...

How to call an 'all' items value using a dropdownlist controlled by both a datasource and user defined values
Hi all, I have an access database datasource controlling a dropdown menu that i want to use in combination with a datalist controlled by another datasource (same database) to bring up results.   So far the user can select an option from my frontend menu which brings up the results in the datalist using a where statement which works fine. When i attached the dropdown menu using another 'where' statement i can use it to select the other values in the dropdown menu and the results are ammended correctly which is exactly what i want. The simple problem is...

Web resources about - How to get return value or output value after insert new record (Scott's tutor used BLL) - asp.net.presentation-controls

Resources last updated: 2/11/2016 1:40:54 PM