Connecting asp.net to Sql server 2005 stored procedures into dataset

This article demonstrates how to use ASP.NET and ADO.NET with Visual Basic .NET to create and to call a Microsoft SQL Server stored procedure with an input parameter and an output parameter.

1 step create store procedure

Create Procedure GetAuthorsByLastName1 (@au_lname varchar(40), @RowCount int output)
as

select * from authors where au_lname like @au_lname;

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
select @RowCount=@@ROWCOUNT

Call the stored procedure in asp.net code

  Private Function getDataset() As DataSet
        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter

        'Create a connection to the SQL Server.
        MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

        'Create a DataAdapter, and then provide the name of the stored procedure.
        MyDataAdapter = New SqlDataAdapter("GetAuthorsByLastName", MyConnection)

        'Set the command type as StoredProcedure.
        MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

        'Create and add a parameter to Parameters collection for the stored procedure.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@au_lname", _
       SqlDbType.VarChar, 40))

        'Assign the search value to the parameter.
        MyDataAdapter.SelectCommand.Parameters("@au_lname").Value = Trim(txtLastName.Text)

        'Create and add an output parameter to Parameters collection.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", _
        SqlDbType.Int, 4))

        'Set the direction for the parameter. This parameter returns the Rows returned.
        MyDataAdapter.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output

        DS = New DataSet() 'Create a new DataSet to hold the records.
        MyDataAdapter.Fill(DS, "AuthorsByLastName") 'Fill the DataSet with the rows returned.

        'Get the number of rows returned, and then assign it to the variable
        Dim rowCount As String
        rowCount = MyDataAdapter.SelectCommand.Parameters(1).Value.ToString & " Rows Found!"

        MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
        MyConnection.Close() 'Close the connection.

        Return DS

    End Function