How to return the ID through a dataset?

chobo2 · June 25th, 2008, 02:27 AM

Hi

I have made a dataset.xsd and I made a store procedure for the insert.

Code:

ALTER PROCEDURE [dbo].[QuickLink_Insert]
(
    @UserID uniqueidentifier,
    @QuickLinkName varchar(50),
    @Repetitions int
)
AS
BEGIN
    DECLARE @QuickLinkID int
    INSERT INTO QuickLinks ( UserID , QuickLinkName, Repetitions ) 
    VALUES ( @UserID , @QuickLinkName, @Repetitions )

    set @QuickLinkID = @@identity
    RETURN @QuickLinkID
End

Now I made a BLL class and I have this in it.

Code:

    [System.ComponentModel.DataObjectMethodAttribute
          (System.ComponentModel.DataObjectMethodType.Insert, true)]
    public void insertQuickLink(Guid userID, string quickLinkName, int reps )
    {
      QuickLinksAdapter.Insert(userID, quickLinkName, reps);


    }

Now how do I get that returned value from my stored procedure?

samjudson · June 25th, 2008, 07:18 AM

A search of google revealed the following possible solution:

By default, the TableAdapter will not add SELECT statement after insert or
update. However, you can do the following to make the designer add it for
you.

1. Right click on the TableAdapter and select configure... from the pop up
menu.
2. In Advanced Options, check Refresh the datatable, and click OK.
3. Click Finish.

Make sure that a primary key field exists in the table, or the SELECT
statement cannot be generated.

/- Sam Judson : Wrox Technical Editor -/

chobo2 · June 25th, 2008, 03:42 PM

How does this help me return the ID value?

samjudson · June 25th, 2008, 04:24 PM

OK, I just created a table with three fields, 'ID', the primary key and an identity column, 'Type' and 'Name two nvarchar fields.

When I check the insert method on my table adapter it takes two arguments, Type and Name, and returns an integer - which I'm gonna assume is the identity.

Is this not the case with your tableadapter?

/- Sam Judson : Wrox Technical Editor -/

Old Pedant · June 25th, 2008, 04:49 PM

What you are doing is completely different than what chobo2 is doing and wanting to do.

You are just doing a simple INSERT followed by SELECT @@IDENTITY, as two separate operations.

He wants to encapsulate the whole thing into a *single* stored procedure.

I certainly know how to do this using a SqlCommand object: Just have the SP end with
    SELECT @@IDENTITY
and then use
    SqlCommand.ExecuteScalar
presto.

But how do you make a DataAdapter do the same thing??

I *THINK* you can actually do it the same way, no?

    MySqlAdapter.InsertCommand.ExecuteScaler()

If I am right, then a minor change to the SP:

Code:

ALTER PROCEDURE [dbo].[QuickLink_Insert]
(
    @UserID uniqueidentifier,
    @QuickLinkName varchar(50),
    @Repetitions int
)
AS
BEGIN
    INSERT INTO QuickLinks ( UserID , QuickLinkName, Repetitions ) 
    VALUES ( @UserID , @QuickLinkName, @Repetitions )

    SELECT @@identity
End

and then rework the method:

Code:

public int insertQuickLink(Guid userID, string quickLinkName, int reps )
{
    SQLCommand ins = QuickLinksAdapter.InsertCommand;
    ins.CommandType = CommandType.Text;
    ins.CommandText = "dbo.QuickLink_Insert";

    //Set the Parameter Values here.
    ins.Parameters.Add("@UserID", ...)
    ins.Parameters.Add("@QuickLinkName", ...)
    ins.Parameters.Add("@Repetitions", ...)

    return (Int32) ins.ExecuteScalar();
}

??? But I would hope there's an easier way?

chobo2 · June 25th, 2008, 05:35 PM

Ya I found a easier way but it is not using the Return keyword. It is using the "select" and I am not sure what the difference between the 2 are.

whats the difference. The return value thing should work the same way as I did it with the "select" but it does not work.

So now I am using "select".

What you needed to do is change the method to "scaler" then do this:

int new_productID = Convert.ToInt32(productsAdapter.InsertProduct ("New Product", 1, 1, "12 tins per carton", 14.95m, 10, 0, 10, false));

you can find more details at:

http://www.asp.net/learn/data-access...ial-01-cs.aspx

In step 4.

Old Pedant · June 25th, 2008, 06:02 PM

There's no *practical* difference between using
    SELECT @@IDENTITY
and
    RETURN @@IDENTITY

The difference is in how the two values are handled. With the SELECT, you *do* get a result set from SQL Server (consisting of one record with one field). With the RETURN, the value is returned same as an error code or other return code would be. And whether your code has access to that value or not depends on the component used to make the SP call.

With a SqlDataSource, you have to declare a parameter with
    Direction = "ReturnValue"
in order to get the value from RETURN. Here's an example:
http://dotnetjunkies.com/QuickStartv...ramsInsert.src

At the command and parameters level:
http://msdn.microsoft.com/en-us/library/5a10hy4y(VS.80).aspx
and then
http://msdn.microsoft.com/en-us/library/system.data.parameterdirection(VS.80).aspx
So you'd use
    ParameterDirection.ReturnValue

For most coding, the SELECT is easier to code, thanks to the ExecuteScalar method.

chobo2 · June 25th, 2008, 06:59 PM

Quote:

quote:Originally posted by Old Pedant
There's no *practical* difference between using
    SELECT @@IDENTITY
and
    RETURN @@IDENTITY

The difference is in how the two values are handled.Ã Ã With the SELECT, you *do* get a result set from SQL Server (consisting of one record with one field).Ã Ã With the RETURN, the value is returned same as an error code or other return code would be.Ã Ã And whether your code has access to that value or not depends on the component used to make the SP call.

With a SqlDataSource, you have to declare a parameter with
    Direction = "ReturnValue"
in order to get the value from RETURN.Ã Ã Here's an example:
http://dotnetjunkies.com/QuickStartv...ramsInsert.src

At the command and parameters level:
http://msdn.microsoft.com/en-us/library/5a10hy4y(VS.80).aspx
and then
http://msdn.microsoft.com/en-us/library/system.data.parameterdirection(VS.80).aspx
So you'd use
    ParameterDirection.ReturnValue

For most coding, the SELECT is easier to code, thanks to the ExecuteScalar method.

and it seem the only way I can't get it to work. Like I would love to find out how to use the Return and get the save results or to use the "out".

Like the methods with the Parameter Direction I used those before but I really do not want to use that with the data set(to me it defeats the purpose of them) plus there should be away to do it.

Old Pedant · June 25th, 2008, 07:07 PM

chobo2 said:
"and it seem the only way I can't get it to work..."

Ummm...in the prior message you said that you DID get it working with SELECT. You said the wasy to it was to "...change the method to "scaler"..."

So which is it???? Working or not??

If SELECT and changing to "scaler" works, then I see nothing wrong with doing this, since MS gave us that as by far the easier way to do this.

chobo2 · June 25th, 2008, 07:45 PM

Quote:

quote:Originally posted by Old Pedant
chobo2 said:
"and it seem the only way I can't get it to work..."

Ummm...in the prior message you said that you DID get it working with SELECT.Ã Ã You said the wasy to it was to "...change the method to "scaler"..."

So which is it????Ã Ã Working or not??

If SELECT and changing to "scaler" works, then I see nothing wrong with doing this, since MS gave us that as by far the easier way to do this.

Sorry I meant to say that is the only "way I CAN" get it to work.

Can "Select" return multiple values like "out" could?