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 :-)