How to return the newid() from a TableAdapter.Insert() command

is there an identity column on your table?

declare @new uniqueidentifier
select @new = newid()
INSERT INTO [BandDetails] ([BandId], [BandName]]) VALUES (@new, @BandName)

you can try to use the new output clause of sql server 2005
try

INSERT INTO [BandDetails] ([BandId], [BandName]])
output @othervar = inserted.bandid
VALUES (DEFAULT, @BandName)


then @otherval will have the new assigned value

chapmandew: BandId is the identity column, no?

aneeshattingal: I'm using the visual studio query configuration window... if I add that code to the statement and run it I get "The Declare cursor SQL construct or statement is not supported."

Momi: When I add your code to the SQL statement it also doesn't recognise it.

I don't think that it is.  It looks to me like Aneesh's comment is your best bet...get the ID first, and then insert it.

Bugger... looks like the visual studio sql builder wizard doesn't accept declarations.
http://forums.asp.net/p/1034609/1427965.aspx

I would use this algorithm, this is done programmatically like the following

datatable dt = ...; //Declare table,
datarow dr = dt.NewRow(); //create row
dr[...] = ....;
dr[...] = ....;
dr[...] = ....;
dr[...] = ....;

adapter.Update(); //insert into database,  this adapter must be set to 'refill after insert'

now, dr[uniqIdColumn] will contain the new UniqueIdentifier.

Let me know how it works.

kdiederi:

Here's what I have now...

1. BandDetailsTableAdapters.BandDetailsTableAdapter BandAdapter = new BandDetailsTableAdapters.BandDetailsTableAdapter();

2. BandDetails.BandDetailsDataTable BandTable = new BandDetails.BandDetailsDataTable();

3. DataRow dr = BandTable.NewRow();

4.  dr["BandName"] = txt_Bandname.Text;

5. BandAdapter.Update(BandTable);

6.  lbl_Test.Text = "new id:"+ dr["BandId"].ToString();

Unfortunately it's not Updating the database with a new record. Can you see where I'm going wrong?Do I have to set the dr["BandId"]?

Also, where do I set the Adapter to refill after insert?

Many thanks.

Sorry forgot to add the row to the table first.

Still a problem though...

When I 'INSERT' a new record it automatically generates a new 'BandId' from the default newid() method in the database. But if I don't add a dr["BandId"] value in the row to be updated I get...

Column 'BandId' does not allow nulls.

How to I tell it to automatically generate a new id for dr["BandId] when I use Update(BandTable)?

Thanks.

Check if you have the default 'newid()' for BandId

CREATE TABLE [GuidTable](
    [BandID] [uniqueidentifier] DEFAULT (newid())
...

yes... it's definitely set to newid() in the database.

In the tableadapter however, the column BandId has  as the default value. If I try to put newid() in there as well it says 'property value is not valid'

If I set the tableadapter column 'BandId' to allow NULL values it inserts the record and returns the id but the GUID is 00000000-0000-0000-0000-000000000000.

Close! It's just not automatically generating a GUID with the newid() method at the database.

Regenerate your dataset

There is a known bug which MSFT is trying to fix in the next SP with respect to this GUID.

Whats your environment?

SQL Server 2005 Express.

Microsoft .NET Framework 2.0 Service Pack 1... i think

Thanks so much for the help. This has been driving me bonkers all day!