Major problem with @@IDENTITY, Treeview and GUID

Hi,

I'm trying to insert data into locally stored database (SQL Server).
The data I want inserted, is presented in a Treeview control and the data is fetched from a Webservice. The data is returned in form of a dataset.
The treeview contains checkboxes allowing a user to select what to install in the locally stored database.

To sum up:

1. Get data from a webservice' not my problem
2. Present data in a Treview control' not my problem
3. Allow to user to select which data to install' not my problem
4. Insert data that the user has selected into my db' MY PROBLEM!!!!

The Treeview looks like this.

- Group1
| | -- Rule1.1
| | -- Rule1.2
|
- Group2
| | -- Rule2.1
| | -- Rule2.2
| | -- Rule2.3
....

The Treeview is generated with DataRelations between Group and Rule.

My locally stored database is designed by a third party provider and therefore the database must not be altered.
The table I want to store data in is called "Groups" and it looks like this:

GroupID uniqueidentifier ' (newid())
GroupName nvarchar(50)
ParentGroupID uniqueidentifier' if grouptype = 0 then ParentGroupID must have a value.
GroupType tinyint ' 0 = subgroup, 1 = "top"group
Data in the table "Groups" would look like this:
GroupID GroupNameParentGroupIDGroupType
------------------
{000001...}Group1<NULL>1
{000011...}Rule1.1{000001...}0
{000012...}Rule1.2{000001...}0
{000002...}Group2<NULL>1
{000021...}Rule2.1{000002...}0
{000022...}Rule2.2{000002...}0
{000023...}Rule2.3{000002...}0

The third party also created a stored procedure called pr_AddGroup taking the following parameters:

@.GroupName ' can be both the RuleName and the GroupName
@.GroupType ' can be 0 for subgroup or 1 for "top"group
@.ParentGroup ' GUID

The problem with this stored procedure is that it does not have return value, which is here my problem actually lies.
If it returned @.@.IDENTITY I could use this as the parameter for @.ParentGroup.
Instead I figure I must create two sqlCommand's (one calling pr_AddGroup and another calling SELECT @.@.IDENTITY to get the newly created record).

My SQL Commands look like this

Dim cmd As SqlCommand
Dim Conn As SqlConnection = New SqlConnection
Conn.ConnectionString = "Data Source=myServer;Initial Catalog=myTable;Integrated Security=SSPI"
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = Conn
cmd.CommandText = "pr_AddGroup"
cmd.Parameters.Add(New SqlParameter("@.GroupName", SqlDbType.NVarChar, 50, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.GroupType", SqlDbType.TinyInt, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.ParentGroup", SqlDbType.UniqueIdentifier, ParameterDirection.Input))
dim cmd2 as SqlCommand
cmd2 = new SqlCommand
cmd2.commandtype = commandtype.Text
cmd2.commandtext = "SELECT @.@.IDENTITY as ID FROM Groups"
cmd2.connection = Conn
dim ParentGroupGUID as system.guid

To get the data inserted in the Groups table I would something like the following, but the code is very ugly
(and it doesn't work either);

 For Each Group In TreeView1.Nodes ' Loop through Groups
 If Group.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.ExecuteNonQuery()
 ParentGroupGUID = cmd2.executescalar()
 End If
 For Each Rule In Group.Nodes ' Loop through Rules.
 If Rule.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.Parameters("@.ParentGroup").value = ParentGroupGUID
 cmd.ExecuteNonquery()
 End If
 Next
 Next

I've spent the last 5 hours figuring out this problem, so ANY help is appreciated :-)I'd recommend getting the stored proc changes because the time gap between running the stored proc and the select stmt can return a wrong ID for multiple users. So to main integrity of the stored proc and the ID its best to add the select stmt inside the stored proc itself. Also use SCOPE_IDENTITY() instead of @.@.IDENTITY.

hth|||Hi ndinakar,

Thanks for replying - but what do mean by:
"I'd recommend getting the stored proc changes"

I'm not allowed to change the stored proc because it's provided by a third party and if I modify the stored proc, the third party application is likely to break (and I don't wanna risk this).

??|||I meant the change be made in the stored proc it self to get accurate results. Even if you make a call to the table to return the latest Id there is no guratantee you would get the id for the isnert you did. if there are 5 ppl doing the insert at the same time the id's returned could get mixed up. so if you need to get the accurate id that was generated due to your insert..do the select immediately after the insert IN the stored proc itself. you need to xplain to the third party people and sort it out with them. else take the risk of not getting the right value.

hth