SQL determine INSERT ID's

[HeavenCore]

hi, quick sql question really

just doing a simple insert in ado:

             Dim SQLstatement2 As New SqlClient.SqlCommand("Insert Into tblEnclosureTasks (TaskStartTime,TaskEndTime,TaskType_fk) VALUES (@StartTime,@EndTime,@Type)", theDatabase)
                SQLstatement2.Parameters.AddWithValue("StartTime", DateTimePicker1.Value)
                SQLstatement2.Parameters.AddWithValue("EndTime", DateTimePicker2.Value)
                SQLstatement2.Parameters.AddWithValue("Type", lblNewTaskType.Text)
                QueryResults = SQLstatement2.ExecuteReader()

the ID of this table is an 'identity specification yes-1,1' and thus auto-increments, when i do an insert is there an efficient way of reading back the generated ID?

I thought of doing a pre-emptive query ie max(id) + 1 and inserting this ID, but this table is being accessed by multiple people and this is not exactly sensible, any ideas?

 

[HeavenCore]

nevermind, i figured it out, just affix this to the query:

 SELECT @@IDENTITY AS 'ident'

thus making:

                Dim SQLstatement2 As New SqlClient.SqlCommand("Insert Into tblEnclosureTasks (TaskStartTime,TaskEndTime,TaskType_fk) VALUES (@StartTime,@EndTime,@Type) SELECT @@IDENTITY AS 'ident'", theDatabase)
                SQLstatement2.Parameters.AddWithValue("StartTime", DateTimePicker1.Value)
                SQLstatement2.Parameters.AddWithValue("EndTime", DateTimePicker2.Value)
                SQLstatement2.Parameters.AddWithValue("Type", lblNewTaskType.Text)
                QueryResults = SQLstatement2.ExecuteReader()
                Do While QueryResults.Read()
                    Try
                        NewTaskID = QueryResults("ident")
                    Catch ex As Exception 'just catch dbNull etc
                    End Try
                Loop

 

[cjard]

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

 

[cjard]

That said... the DataSet designer can usually do all this automatically for you.. (define a table with an id col, its sent off to the db, the id value is written back to the datatable etc..) Howcome you dont use it?