70-516 Question #221: Real Exam Question with Answer & Explanation

You use Microsoft Visual Studio 2010 and Microsoft .NET Framework 4 to create an application. You create a stored procedure to insert a new record in the Categories table according to following code segment. CREATE PROCEDURE dbo.InsertCategory @CategoryName nvarchar(15), @Identity int OUT AS INSERT INTO Categories (CategoryName) VALUES(@CategoryName) SET @Identity = SCOPE_IDENTITY() RETURN @@ROWCOUNT You write the following code segment. (Line numbers are included for reference only). 1 Private Shared Sub ReturnIdentity(connectionString As String) 2 Using connection As New SqlConnection(connectionString) 3 Dim adapter As New SqlDataAdapter("SELECT CategoryID, CategoryName FROM dbo.Categories", connection) 4 adapter.InsertCommand = New SqlCommand("InsertCategory", connection) 5 adapter.InsertCommand.CommandType = CommandType.StoredProcedure 6 Dim rowcountParameter As SqlParameter = adapter.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int) 8 adapter.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName") 9 Dim identityParameter As SqlParameter = 10 adapter.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID") 12 Dim categories As New DataTable() 13 adapter.Fill(categories) 14 Dim categoryRow As DataRow = categories.NewRow() 15 categoryRow("CategoryName") = "New Beverages" 16 categories.Rows.Add(categoryRow) 17 adapter.Update(categories) 18 Dim rowCount As Int32 = 19 DirectCast(adapter.InsertCommand.Parameters("@RowCount").Value, Int32) 20 End Using 21 End Sub You need to retrieve the identity of the new record. You also need to retrieve the row count. What should you do?