Listing 2: Code That Uses a Stored Procedure to Insert a Row and Return Its Identity Value
[BEGIN CALLOUT A]
Private Sub AutoIncrInsertSql(ByVal sServer As String, _
    ByVal sDB As String)
    Cursor.Current = Cursors.WaitCursor
    Dim sqlCn As SqlConnection = New SqlConnection("SERVER=" & _
        sServer & ";INTEGRATED SECURITY=True;DATABASE=" & sDB)
[END CALLOUT A]
[BEGIN CALLOUT B]
    Dim sqlDA As SqlDataAdapter = New SqlDataAdapter( _
        "SELECT * FROM Employees", sqlCn)
    sqlDA.InsertCommand = New SqlCommand("spEmployeesInsertCmd", _
        sqlCn)
    sqlDA.InsertCommand.CommandType = CommandType.StoredProcedure
[END CALLOUT B
[BEGIN CALLOUT C]
    Dim ds = New DataSet()
        Dim sqlParm = New SqlParameter()
    Try
        sqlDA.Fill(ds, "Employees")
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@LastName", SqlDbType.NChar, 20, _
            "LastName"))
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@FirstName", SqlDbType.NChar, 10, _
            "FirstName"))
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@Title", SqlDbType.NChar, 30, _
            "Title"))
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@Address", SqlDbType.NChar, 60, _
            "Address"))
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@City", SqlDbType.NChar, 15, _
            "City"))
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@Region", SqlDbType.NChar, 15, _
            "Region"))
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@PostalCode", SqlDbType.NChar, 10, _
            "PostalCode"))
        sqlParm = sqlDA.InsertCommand.Parameters.Add( _
            New SqlParameter("@Identity", SqlDbType.Int, 0, _
            "EmployeeID"))
        sqlParm.Direction = ParameterDirection.Output

[END CALLOUT C]
[BEGIN CALLOUT D]
        Dim sqlDR = ds.Tables("Employees").NewRow()
        sqlDR("LastName") = "Smith"
        sqlDR("FirstName") = "James"
        sqlDR("Title") = "Sales Temp"
        sqlDR("Address") = "1222 First St"
        sqlDR("City") = "Portland"
        sqlDR("Region") = "OR"
        sqlDR("PostalCode") = "97345"
        ds.Tables("Employees").Rows.Add(sqlDR)
[END CALLOUT D]
[BEGIN CALLOUT E]
        sqlDA.Update(ds, "Employees")
        Cursor.Current = Cursors.Default
    Catch e As Exception
        Cursor.Current = Cursors.Default
        MsgBox(e.Message)
    End Try
    'Show the returned identity value.
    sqlParm = SqlDA.InsertCommand.Parameters("@Identity")
    txtIdent.Text = SqlParm.Value
End Sub
[END CALLOUT E]