Skip to main content

Calling stored procedure from Asp.Net code behind Vb.Net

Calling Stored Procedure Asp Vb.Net
                            We already seen how to write stored procedure and how to connect sql server database. The stored procedure make sql command reusable and we can call the same stored procedure at multiple page. Instead individual sql command in Asp pages we can maintain single stored procedure.  we can modify stored procedure it will reflect to every Asp page and thus easy to update the database. The stored procedure are much better than normal sql queries as it was precompiled and cached.
STORED PROCEDURE

CREATE PROCEDURE [dbo].[insertcollegedetails]
-- Add the parameters for the stored procedure here
  @t1 int,
  @t2 int,
  @name nvarchar(max),
  @code nvarchar(max),
  @prin nvarchar(max),
  @sec  nvarchar(max),
  @place nvarchar(max)    
  AS
BEGIN
SET NOCOUNT ON;
    -- Insert statements for procedure here
insert into tbcollege (Name,code,principalname,Secretary,place,type,ctype) values
(@name,@code,@prin,@sec,@place,@t1,@t2);
END
In this stored procedure we declare variable  @t1, @t2, @name, @code,@prin,@sec, @place.  The @ symbol defined the user variable. This variable act as parameter for this stored procedure. so when stored procedure call we should pass this parameter also otherwise error may occurs.  

Code Behind Page VB.NET

imports System.Data.SqlClient
Imports System.Data
    Protected Sub btt_submit_Click(sender As Object, e As EventArgs)
        ' ss_date = DateTime.ParseExt(txt_date2.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture)
        Dim strconn As String
        strconn = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        ' for opening db connection
        Dim con As New SqlConnection(strconn)
        con.Open()
        Dim cmd As New SqlCommand
        cmd.Connection = con
        cmd.CommandText = "insertcollegedetails"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("@t1", dd_type.SelectedValue))
        cmd.Parameters.Add(New SqlParameter("@t2", dd_ctype.SelectedValue))
        cmd.Parameters.Add(New SqlParameter("@name", txt_collegename.Text))
        cmd.Parameters.Add(New SqlParameter("@code", txt_code.Text))
        cmd.Parameters.Add(New SqlParameter("@prin", txt_principal.Text))
        cmd.Parameters.Add(New SqlParameter("@sec", txt_secretary.Text))
        cmd.Parameters.Add(New SqlParameter("@place", txt_location.Text))
        cmd.ExecuteNonQuery()
        con.Close()
        Response.Write("<script LANGUAGE='JavaScript' >alert('Successful')</script>")
        Response.Redirect("suc.aspx")
    End Sub
We already seen how to connect sql server database.  The following step have detailed about how to call sql stored procedure in Asp vb,net code behind page.
  • We maintain connection to sql server through connection object con.  
  • The SqlCommand object cmd is map to con object to write command.
  • The stored procedure  is called by CommandText Property through cmd object.  
  • cmd.CommandType = CommandType.StoredProcedure is defined that command type is stored procedure. 
  • cmd.CommandText = "insertcollegedetails" insertcollegedetails is name of stored procedure.
  • cmd.Parameters.Add(New SqlParameter("@t1", dd_type.SelectedValue))  Using cmd.Parameter we pass parameter to stored procedure. The add property allows to add parameter to stored procedure.
  • cmd.Parameters.Add(New SqlParameter("@code", txt_code.Text)) we pass stored procedure variable @code and we map value from Asp.net TextBox component  txt_code.Text.
  • cmd.ExecuteNonQuery() execute the stored procedure.
  • con.Close will close sql server connection.
  • Response.Write("<script LANGUAGE='JavaScript' >alert('Successful')</script>") will pop successful message.



Thank You. Like, Share and Subscribe 👍 Followlect.com

Comments