Call Stored Procedure with Parameter By VB.Net

The SqlConnection Object is Handling the part of physical communication between the application and the SQL Server Database. An instance of the SqlConnection class in .NET Framework is supported the Data Provider for SQL Server Database. The SqlConnection instance takes Connection String as argument and pass the value to the Constructor statement. When the connection is established , SQL Commands may be executed, with the help of the Connection Object, to retrieve or manipulate data in the database. Once the Database activities over , Connection should be closed and release the database resources .

The Close() method in SqlConnection class is used to close the Database Connection. The Close method rolls back any pending transactions and releases the Connection from the SQL Server Database.

Imports System.Data.SqlClient
Public Class Form1  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click    
       Dim connetionString As String         
       Dim cnn As SqlConnection        
       connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;UserID=UserName;Password=Password"
       cnn = New SqlConnection(connetionString)
       Try  
           cnn.Open()   
           MsgBox("Connection Open ! ")
           Dim cmd As New SqlCommand
           cmd.Connection = cnn
           cmd.CommandType = CommandType.StoredProcedure
           cmd.CommandText = "search_itemcode"
           cmd.Parameters.Add("@PRDocno", SqlDbType.VarChar).Value = CStr(TextBox1.Text)    
           Dim dr As SqlDataReader = cmd.ExecuteReader  
          ''release resources
           cnn.Close() 
           Catch ex As Exception     
                       MsgBox("Can not open connection ! ")
       End Try
End Sub
End Class

This My Stored Procedure

 ALTER PROCEDURE [dbo].[Search_itemcode]
     @PrDocno varchar(15)
     AS
     BEGIN
           SET NOCOUNT ON;
           DECLARE  @docno varchar(15),@itemcode varchar(15)
           -- declare the cursor
           DECLARE C CURSOR FOR
           select docno,itemcode
           from itemprsub
           where DocNo = @PrDocno
           OPEN C
           FETCH C INTO @docno,@itemcode
     -- start the main processing loop.
          WHILE @@Fetch_Status = 0
          BEGIN
                  EXEC input_last3buy @itemno = @itemcode,@PrDocno = @docno
                  EXEC input_last6sale @itemcodesale = @itemcode
                  FETCH C INTO @docno,@itemcode
          END
          CLOSE C
          DEALLOCATE C
          EXEC input_MonthName
          RETURN
END