DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 306574 - Last Review: May 13, 2007 - Revision: 6.6

This article was previously published under Q306574

On This Page

SUMMARY

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.

The code sample in this article first checks whether the stored procedure that you will create exists in the database. If the stored procedure does not exist, the code creates a stored procedure that takes one parameter to search the Authors table based on the last name and returns the matching rows and number of rows that are returned in an output parameter.

This article also demonstrates how to create a Web Form that provides a simple user interface. The Web Form contains the following items:
  • A text box in which the user types the search condition.
  • A DataGrid control that displays the search results.
  • A Label control that displays the number of returned records.
  • A Button control that calls the stored procedure when the button is clicked.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • Microsoft SQL Server version 7.0 or later
  • Microsoft Visual Studio .NET
  • Microsoft Visual Basic .NET
  • Permissions to create the stored procedure in the database
This article assumes that you are familiar with the following topics:
  • SQL Server stored procedures

Create an ASP.NET Project and Add Controls

In this section, you create an ASP.NET project and build the basic user interface. Note that these steps use Microsoft Visual Basic .NET code. To create the project, follow these steps:
  1. Click Start, point to Programs, point to Microsoft Visual Studio .NET, and then click Microsoft Visual Studio .NET.
  2. On the Visual Studio .NET Start page, click New Project.
  3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
  4. In the Name box, type a name for your Web application, and then click OK.
  5. Add the following server controls to the Web Form, and set the properties as they are listed in the table:
    Collapse this tableExpand this table
    ControlID PropertyText Property
    LabellblLastNameType the Author's Last Name:
    TextBoxtxtLastName%
    ButtonbtnGetAuthorsGet Authors
    LabellblRowCount(Row Count)

  6. Drag a DataGrid server control from the toolbox to the Web Form, and then set the Name property to GrdAuthors.
  7. Right-click the grid, and then click Autoformat.
  8. Click Professional 1 for the scheme, and then click OK.

Create the GetAuthorsByLastName Stored Procedure

Use the following Transact-SQL code to create the GetAuthorsByLastName stored 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
				
This code includes two parameters: @au_lname and @RowCount. The @au_lname parameter is an input parameter that obtains the search string to perform a "like" search in the Authors table. The @RowCount parameter is an output parameter that uses the @@ROWCOUNT variable to obtain the affected rows.

Create and Run the Stored Procedure

To access SQL Server databases, you must import the System.Data.SqlClient namespace, which provides new objects such as the SqlDataReader and the SqlDataAdapter objects. You can use SqlDataReader to read a forward-only stream of rows from a SQL Server database. DataAdapter represents a set of data commands and a database connection that you can use to fill the DataSet object and to update a SQL Server database.

ADO.NET also introduces the DataSet object, which is a memory-resident representation of data that provides a consistent, relational programming model regardless of the data source. The code in this section uses all of these objects.
  1. Double-click the Web Form.
  2. Add the following code to the Declaration section of your Web Form, which appears at the top of the Code window:
    Imports System.Data
    Imports System.Data.SqlClient
    					
  3. To make sure that the stored procedure exists and to create a new stored procedure, use a SqlCommand object with a SqlDataReader object. You can use SqlCommand to run any SQL commands against the database. Then call the ExecuteReader method of SqlCommand to return SqlDataReader, which contains matching rows for your query.

    Add the following code in the Page_Load event of the Web Form:
    'Only run this code the first time the page is loaded.
    'The code inside the IF statement is skipped when you resubmit the page.
    If Not IsPostBack Then
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlCommand
        Dim MyDataReader As SqlDataReader
    
        'Create a Connection object.
        MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
    
        'Create a Command object, and then set the connection.
        'The following SQL statements check whether a GetAuthorsByLastName stored procedure 
        'already exists.
        MyCommand = New SqlCommand("if object_id('pubs..GetAuthorsByLastName') is not null " + "begin" + " if objectproperty(object_id('pubs..GetAuthorsByLastName'), 'IsProcedure')= 1" + " select object_id('pubs..GetAuthorsByLastName')" + " else" + " return " + "end" + " else" + " return", MyConnection)
    
        With MyCommand
            'Set the command type that you will run.
            .CommandType = CommandType.Text
    
            'Open the connection.
            .Connection.Open()
    
            'Run the SQL statement, and then get the returned rows to the DataReader.
            MyDataReader = .ExecuteReader()
    
            'If any rows are retuned, the stored procedure that you are trying 
            'to create already exists. Therefore, try to create the stored procedure
            'only if it does not exist.
            If Not MyDataReader.Read() Then
                .CommandText = "create procedure GetAuthorsByLastName (@au_lname varchar(40), " & _ 
    
                                "@RowCount int output) " & _ 
    
                                " as select * from authors where au_lname like @au_lname; select @RowCount=@@ROWCOUNT"
                MyDataReader.Close()
                .ExecuteNonQuery()
            Else
                MyDataReader.Close()
            End If
    
            .Dispose()  'Dispose of the Command object.
            MyConnection.Close() 'Close the connection.
        End With
    End If
    					
  4. Call the stored procedure in the Click event of the btnGetAuthors button, and then use the SqlDataAdapter object to run your stored procedure. You must create parameters for the stored procedure and append it to the Parameters collection of the SqlDataAdapter object.

    Add the following code after the Page_Load event:
    Private Sub btnGetAuthors_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles btnGetAuthors.Click
        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 Label control.
        'lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
        lblRowCount.Text = MyDataAdapter.SelectCommand.Parameters(1).Value & " Rows Found!"
    
        'Set the data source for the DataGrid as the DataSet that holds the rows.
        Grdauthors.DataSource = DS.Tables("AuthorsByLastName").DefaultView
    
        'Bind the DataSet to the DataGrid. 
        'NOTE: If you do not call this method, the DataGrid is not displayed!
        Grdauthors.DataBind()
    
        MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
        MyConnection.Close() 'Close the connection.
    End Sub
    					
  5. In Solution Explorer, right-click the .aspx page, and then click Set as Start Page.
  6. Save the project, and then click Start in Visual Studio .NET. Notice that the project is compiled and that the default page runs.
  7. Type the author's last name in the text box, and then click Get Author. Notice that the stored procedure is called and that the returned rows populate the DataGrid.

    You can provide SQL Server-type search strings such as G%, which returns all the authors by last names that start with the letter "G."

Troubleshooting

  • If you cannot connect to the database, make sure that the ConnectionString properly points to the server that is running SQL Server.
  • If you can connect to the database, but if you experience problems when you try to create the stored procedure, make sure that you have the correct permissions to create stored procedures in the database to which you are connecting.

REFERENCES

For more information, see the following topics in the Microsoft .NET Framework Software Development Kit (SDK) documentation:
Overview of ADO.NET
http://msdn.microsoft.com/en-us/library/h43ks021.aspx (http://msdn.microsoft.com/en-us/library/h43ks021.aspx)

ADO.NET DataSet
http://msdn.microsoft.com/en-us/library/zb0sdh0b(VS.71).aspx (http://msdn.microsoft.com/en-us/library/zb0sdh0b(VS.71).aspx)

Using .NET Data Providers to Access Data
http://msdn2.microsoft.com/en-us/library/s7ee2dwt(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/s7ee2dwt(vs.71).aspx)
For more general information about ADO.NET or Visual Basic .NET, refer to the following MSDN newsgroups:
microsoft.public.dotnet.framework.adonet (http://msdn.microsoft.com/newsgroups/default.aspx?query=dotnet.framework.adonet&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us)

microsoft.public.dotnet.languages.vb (http://msdn.microsoft.com/newsgroups/default.aspx?query=dotnet.languages.vb&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us)
For more information, see the following book:
Wyke, R. Allen, and Sultan Rehman and Brad Leupen. XML Programming (Core Reference) (http://www.microsoft.com/mspress/books/sampchap/4798.aspx) . Microsoft Press, 2001.
For more information, see the following Microsoft Training & Certification course:
2389 Programming with ADO.NET (http://www.microsoft.com/learning/syllabi/en-us/2389Bfinal.mspx)
For additional information about how to perform this task by using Microsoft Active Server Pages, click the article number below to view the article in the Microsoft Knowledge Base:
300488  (http://support.microsoft.com/kb/300488/EN-US/ ) How To Run SQL Stored Procedures from an ASP Page

APPLIES TO
  • Microsoft ASP.NET 1.0
  • Microsoft ASP.NET 1.1
  • Microsoft ADO.NET 1.0
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
Keywords: 
kbhowtomaster kbsqlclient kbsystemdata KB306574
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support