ADO.NET 101: SqlDataReader - Stored Procedure with one input and one output parameter

(Page 6 of 7 )

This Stored Procedure YtdSales requires an input variable @title and the result of the query is returned in another variable @ytd_sales. Notice that the OUTPUT variable is clearly marked in the Stored Procedure. Also note the ParameterDirection attribute.

Create Procedure YtdSales
@title varchar(50), @ytd_sales int OUTPUT
as
Select @ytd_sales=ytd_sales
from titles
where title=@title
return

1. Establish a connection as before

2. Place a textbox to receive the input value, a TextBox to display the output and a button to initiate retrieval.

3. In the click event of the button insert the code shown in step 4.

4. In this case there is no need to loop the returned rows as there will be only one result returned.

   Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
        'declare a connection of type SQLConnection
        Dim SqlConnection1 = New SqlClient.SqlConnection
        'define the connection string for this connection
        SqlConnection1. _
        connectionString = "workstation id= XPHTEK;" & _
        "packet size=4096;" & _
            "integrated security=SSPI;data source=XPHTEK;" & _
            "persist security info=False;initial catalog=pubs"
        'open the connection
        SqlConnection1.Open()
        'declare and define the SQLCommand to process a
        'stored procedure
        Dim cmd As New SqlClient.SqlCommand
        With cmd
            .Connection = SqlConnection1
            .CommandType = CommandType.StoredProcedure
            .CommandText = "YtdSales"
        End With
        'declare a new SQLParamter
        Dim param1 As New SqlClient.SqlParameter
        'add this parameter to the collection
        param1 = cmd.Parameters.Add _
        ("@title", SqlDbType.VarChar)
        'this parameter is the input
        param1.Direction = ParameterDirection.Input
        'declare another paramter
        Dim param2 As New SqlClient.SqlParameter
        'add this second parameter to the collection
        param2 = cmd.Parameters.Add _
        ("@ytd_sales", SqlDbType.Int)
        'this one is the output
        param2.Direction = ParameterDirection.Output
        'assign the input parameter
        cmd.Parameters("@title").Value = "Is Anger the Enemy?"

        Dim dr As SqlClient.SqlDataReader
        'Perform ExecuteReader()
        dr = cmd.ExecuteReader()
        '
        TextBox1.Text = param1.Value
          TextBox2.Text = param2.Value
        Response.Write(cmd.Parameters.Count())
    End Sub

 
5. When the code is run you should see the following:

Next: SQLCommand's ExecuteXMLReader() Method >>
 

More Database Articles
More By Jayaram Krishnaswamy

Comments On: ADO.NET 101: SqlDataReader

>>> Be the FIRST to comment on this article!