Stored Procedure

Posted Date: 03 Sep 2008	Resource Type: Code Snippets	Category: ADO.NET
Author: Bunty	Member Level: Diamond
Rating:	Points: 10

This code sample shows how to call a stored procedure which returns a value from VB.NET.


Create procedure prc_getBal(@cid int,@bal money output)
As
Begin
Select @bal=bal from customers
where cid=@cid
End

Code in VB.NET


Imports System.Data.SQLClient

public Class Form
Dim con as SQLConnection
Dim cmd as SQLCommand

Under FindBalance Button:


Dim cid as integer=InputBox("Enter Cid")
con=new SQLConnection("user id=sa;password=123;database=")
cmd=new SQLCommand("prc_getBal")
cmd.CommandType=CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@cid",cid)
cmd.Parameters.AddWithValue("@bal",SQLDbType.Money).Direction=ParameterDirection.Output
con.open()
cmd.ExecuteNonQuery()
MessageBox.Show(cmd.Parameters("@bal").Value)
con.Close()

Responses

Author: sheetal 15 Feb 2009 Member Level: Bronze Points : 2

Sample Code for Stored Procedure in ASP.net
=====Creating customers table====
CREATE TABLE [dbo].[customers](
[cid] [int] NOT NULL,
[bal] [money] NULL
)

=======Creating a stored procedure=======
Create procedure prc_getBal(@cid int,@bal money output)
As
Begin
Select @bal=bal from customers
where cid=@cid
End

=====Calling the Stored Procedure======
------------------
------------------
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("User ID=sa;Password=pass;Initial Catalog=test;Data Source=Personal");
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
con.Open();
}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
int cid=Convert.ToInt16(TxtID.Text);
cmd=new SqlCommand("prc_getBal",con);

cmd.Parameters.AddWithValue("@cid",cid);
cmd.Parameters.AddWithValue("@bal",SqlDbType.Money).Direction=ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
Response.Write(cmd.Parameters["@bal"].Value);
con.Close();
}
}

Author: Sasikumar 18 Feb 2009 Member Level: Gold Points : 2

Feedbacks

Popular Tags

What are tags ?

Search Tags

Calling Stored Procedure from a .NET Application. .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Bulk Copy - Using Transcation - Moving Data From One Server to Another Server (Central)
	Previous Resource: Common Database Class for .Net Application (C#)
	Return to Discussion Resource Index
	Post New Resource
	Category: ADO.NET

Post resources and earn money!

More Resources