How to return data through Output Parameter in stored procedure in asp.net?

Introduction:  In this article I have explained with example How to use Output parameter in Stored Procedure to return value e.g. status in this article.  In previous article i explained How to Pass parameter to stored procedure using SqlDataAdapter and check login and 20 main differences between Stored procedures and Functions in Sql Server  and Delete all stored procedures from sql server database and Bind gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in Asp.net.

Description: This example demonstrate the use of output parameter in the stored procedure with an example of user registration. User has to enter username, email id and password for registration. Username and Email id will be unique in this concept.

So whenever a same username or Email id is entered again then it prompts user to enter another username(if already exists in the database) or  password(if already exists in the database).  Checking username and email id for duplicity and providing status accordingly is handled through Stored Procedure.

Implementation: Let's create a demo website to understand the concept.

  • Create a table as shown below and name itUSER_REGISTRATION”
http://webcodeexpert.blogspot.in/

Note: Set Is_Identity=True to make the USER_REG_ID_PK the primary key in the table.
  • Now create a Stored Procedure as:
 CREATE PROCEDURE USER_REGISTRATION_SP
(
@Username varchar(50),
@EmailId varchar(100),
@Password varchar(50),
@Status varchar(200) OUTPUT
)
AS
BEGIN
                                IF NOT EXISTS(SELECT * FROM  USER_REGISTRATION WHERE USERNAME=@Username)
                BEGIN
                                IF NOT EXISTS(SELECT * FROM  USER_REGISTRATION WHERE EMAIL_ID=@EmailId)
                                                BEGIN
                                                                INSERT INTO USER_REGISTRATION(USERNAME,EMAIL_ID,PASSWORD) VALUES(@Username,@EmailId,@Password)
                                                                SET @Status = 'Username ' + @Username+ ' with Email id ' + @EmailId +  ' Registerd Successfully'
                                                END
                                ELSE
                                BEGIN
                                                SET @Status ='Email Id '+ @EmailId + ' Already Exits.Please select another.'
                                END
                END
ELSE
                BEGIN
                                SET @Status ='Username ' + @Username+' Already Exits. Please select another.'
                END
END 
  • Now in the web.con fig file create the  connection string to connect the asp.net website with the sql server  as:
<connectionStrings>
                <add name="MyDbCon" connectionString="Data Source=Lalit;Initial   Catalog=MyDataBase;Integrated Security=True"/>
</connectionStrings>

Note:  Replace the Data Source name “Lalit” with your Data Source and Catalog(i.e. Database name)=”MyDataBase“  with your Database name.

C#.Net Code
  • Now in the design page (.aspx) place three text boxes for Username, EmailId and password and also place a Button control as:
<table>
            <tr>
                <td>
                    Username</td>
                <td>
                    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Email Id</td>
                <td>
                    <asp:TextBox ID="txtEmailId" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Password</td>
                <td>
                    <asp:TextBox ID="txtPwd" TextMode="Password" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                   <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />
                    <asp:Label ID="lblStatus" runat="server" Text=""></asp:Label>
                </td>
            </tr>
        </table>
  • Now in the code behind file (.aspx.cs) write the Submit button code as:
First of all include these namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

protected void btnSubmit_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbCon"].ConnectionString.ToString());
        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand cmd = new SqlCommand("USER_REGISTRATION_SP", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Username", txtUserName.Text.Trim());
            cmd.Parameters.AddWithValue("@EmailId", txtEmailId.Text.Trim());
            cmd.Parameters.AddWithValue("@Password", txtPwd.Text.Trim());
            cmd.Parameters.Add("@Status", SqlDbType.VarChar, 500);
            cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            txtUserName.Text = string.Empty;
            txtEmailId.Text = string.Empty;
            txtPwd.Text = string.Empty;
            lblStatus.Text =Convert.ToString(cmd.Parameters["@Status"].Value);          
        }
        catch (Exception ex)
        {
            lblStatus.Text = "ErrorOccured: " + ex.Message.ToString();
        }
        finally
        {
            con.Close();
        }      
      }

VB.Net Code
  • Now in the design page (.aspx) place three text boxes for Username, EmailId and password and also place a Button control as:
<table>
            <tr>
                <td>
                    Username</td>
                <td>
                    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Email Id</td>
                <td>
                    <asp:TextBox ID="txtEmailId" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Password</td>
                <td>
                    <asp:TextBox ID="txtPwd" TextMode="Password" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="btnSubmit" runat="server" Text="Submit" />
                    <asp:Label ID="lblStatus" runat="server" Text=""></asp:Label>
                </td>
            </tr>
        </table>
  • Now in the code behind file (.aspx.vb) write the Submit button code as:

First of all import these namespaces:


Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDbCon").ConnectionString.ToString())
        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Dim cmd As New SqlCommand("USER_REGISTRATION_SP", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Username", txtUserName.Text.Trim())
            cmd.Parameters.AddWithValue("@EmailId", txtEmailId.Text.Trim())
            cmd.Parameters.AddWithValue("@Password", txtPwd.Text.Trim())
            cmd.Parameters.Add("@Status", SqlDbType.VarChar, 500)
            cmd.Parameters("@Status").Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()
            txtUserName.Text = String.Empty
            txtEmailId.Text = String.Empty
            txtPwd.Text = String.Empty
            lblStatus.Text = Convert.ToString(cmd.Parameters("@Status").Value)
        Catch ex As Exception
            lblStatus.Text = "Error Occured: " & ex.Message.ToString()
        Finally
            con.Close()
        End Try
    End Sub

Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."
Previous
Next Post »

10 comments

Click here for comments
Anonymous
admin
August 13, 2013 ×

Good article. Helped me solve my problem. Thanks!

Reply
avatar
August 13, 2013 ×

i am glad my article helped you in resolving your problem..Stay tuned and subscribe for more updates

Reply
avatar
Anonymous
admin
September 09, 2013 ×

how to make store procedure in asp.net or sql server

Reply
avatar
September 09, 2013 ×

Open Sql server management studio -> Expand your database -> Expand Programmability ->Right Click on Stored procedures -> New Stored procedure and create your stored procedure here.

Reply
avatar
Anonymous
admin
September 09, 2013 ×

Do you have post on how to get data from database using txtbox to search?

Reply
avatar
September 09, 2013 ×

yes, read the article http://www.webcodeexpert.com/2013/09/how-to-bind-and-implement-search.html

Reply
avatar
Avinash
admin
October 29, 2013 ×

Hi all,

1.I have input parameters like @age,@date, @time And @gender and output parameter was @paid amt.
2. I have a conditions like
a)between monday to friday(upto 9 pm) the doctor consultation fee was like
Above 10 years -- 200 rs
between 5-10 years -- 100 r.s
below 5 years-- 0 r.s
b) On saturdays the doctor consultation fee was like
between 0-10 years -- 300 r.s
above 10 years-- 500 r.s
c) On sundays the doctor consultation fee was like
between 0-10 years -- 300 r.s
above 10 years-- 700 r.s
d) on nights the consultation fee was like
between 0-10 years -- 200 r.s
above 10 years-- 500 r.s

so i want to write validation in a storedprocedure to met the above conditions.

Reply
avatar
Anonymous
admin
January 23, 2014 ×

How can I send output of the PLSQL procedure over email from database? And also How can I schedule that procedure to run every day at particular time?

Reply
avatar
March 05, 2014 ×

Thankyou Somuch..Mr.Lalit....Gr8 content..

Reply
avatar
March 05, 2014 ×

thanks sai ram nagothu for appreciating and proving feedback ....stay connected and keep reading for more useful updates like this..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..