ADO.NET call Stored Procedure In VB.NET

Here we will see how to create a stored procedure in database and calling stored procedure from database to asp.net using vb language.
  • 8189
 

Stored procedure

A stored procedure is a batch of Transact-SQL statements compiled into a single execution and then reutilizes the execution plan. when we write SQL statements, like select, inserts, updates to access your data from database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure.Every time you write a query it is parsed in database. If you have written a stored procedure for it, it will be compiled once and can be executed  number of times.Stored procedures can also improve performance.

Step-1

Creating a table in database

create table logintab

(

username varchar(50),

password varchar(40)

)

Step-2

Creating Stored procedure

create procedure sploginproc

(

@username varchar(40),

@password varchar(20)

)

as

insert into logintab values(@username,@password )

Step-3

Calling stored procedure in VB.NET

Taking two TextBox, one Button and Label control on the form.

microsoft-visual-studio-in-vb.net.gif

Now double click on the Save button control and add the following code.

Imports System.Data.SqlClient

Public Class WebForm1

    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        Dim str As String = "Data Source=.;uid=sa;pwd=123;database=master"

        Dim con As New SqlConnection(str)

        Dim com As SqlCommand = New SqlCommand("sploginproc", con)

        com.Parameters.Add("@username", SqlDbType.VarChar).Value = TextBox1.Text

        com.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text

        com.CommandType = CommandType.StoredProcedure

        con.Open()

        com.ExecuteNonQuery()

        Label1.Text = "record has been saved"

        con.Close()

    End Sub

End Class

 

Now run the application and enter the username and password.

Windows-internet-explorer-login-in-vb.net.gif

Now click on the save Button.

Windows-internet-explorer--in-vb.net.gif

Now open the database and test it.

database-list-in-vb.net.gif

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.