Calling Stored Procedures from ADO.NET 2.0-VB 2005 Express:(1) How to work with Input and Output Parameters, (2) report their Values in VB Forms

• Scott H. Chang

  SSCommitted

  Points: 1794

  More actions

  February 14, 2008 at 8:06 am

  #183846

  Hi all,

  I try to learn "How to Access Stored Procedures with ADO.NET 2.0-VB 2005 Express: (1) Handling the Input and Output Parameters, and (2) Reporting their Values in VB Forms". I found a goog article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbazine.com/sql/sql-articles/cook6. I downloaded the source in my VB 2005 Express:

  /////////--CookCallSpAdoNetVB2005.vb--//////////

  Imports System.Data

  Imports System.Data.SqlClient

  Imports System.Data.SqlTypes

  Public Class Form1

  Inherits System.Windows.Forms.Form

  #Region " Windows Form Designer generated code "

  Public Sub New()

  MyBase.New()

  'This call is required by the Windows Form Designer.

  InitializeComponent()

  'Add any initialization after the InitializeComponent() call

  End Sub

  'Form overrides dispose to clean up the component list.

  Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

  If disposing Then

  If Not (components Is Nothing) Then

  components.Dispose()

  End If

  End If

  MyBase.Dispose(disposing)

  End Sub

  'Required by the Windows Form Designer

  Private components As System.ComponentModel.IContainer

  'NOTE: The following procedure is required by the Windows Form Designer

  'It can be modified using the Windows Form Designer.

  'Do not modify it using the code editor.

  Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox

  Friend WithEvents labelPAF As System.Windows.Forms.Label

  Friend WithEvents labelNbrPrices As System.Windows.Forms.Label

  Friend WithEvents UpdatePrices As System.Windows.Forms.Button

  Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox

  Friend WithEvents TenMostExpensive As System.Windows.Forms.Button

  Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid

  Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox

  Private Sub InitializeComponent()

  Me.GroupBox1 = New System.Windows.Forms.GroupBox()

  Me.labelPAF = New System.Windows.Forms.Label()

  Me.labelNbrPrices = New System.Windows.Forms.Label()

  Me.textBoxPAF = New System.Windows.Forms.TextBox()

  Me.UpdatePrices = New System.Windows.Forms.Button()

  Me.groupBox2 = New System.Windows.Forms.GroupBox()

  Me.TenMostExpensive = New System.Windows.Forms.Button()

  Me.grdNorthwind = New System.Windows.Forms.DataGrid()

  Me.GroupBox1.SuspendLayout()

  Me.groupBox2.SuspendLayout()

  CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()

  Me.SuspendLayout()

  '

  'GroupBox1

  '

  Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})

  Me.GroupBox1.Location = New System.Drawing.Point(8, 8)

  Me.GroupBox1.Name = "GroupBox1"

  Me.GroupBox1.Size = New System.Drawing.Size(240, 112)

  Me.GroupBox1.TabIndex = 9

  Me.GroupBox1.TabStop = False

  '

  'labelPAF

  '

  Me.labelPAF.Location = New System.Drawing.Point(8, 16)

  Me.labelPAF.Name = "labelPAF"

  Me.labelPAF.Size = New System.Drawing.Size(112, 32)

  Me.labelPAF.TabIndex = 2

  Me.labelPAF.Text = "Enter Price Adjustment Factor"

  '

  'labelNbrPrices

  '

  Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)

  Me.labelNbrPrices.Name = "labelNbrPrices"

  Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)

  Me.labelNbrPrices.TabIndex = 5

  '

  'textBoxPAF

  '

  Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)

  Me.textBoxPAF.Name = "textBoxPAF"

  Me.textBoxPAF.TabIndex = 0

  Me.textBoxPAF.Text = ""

  '

  'UpdatePrices

  '

  Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)

  Me.UpdatePrices.Name = "UpdatePrices"

  Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)

  Me.UpdatePrices.TabIndex = 6

  Me.UpdatePrices.Text = "Update Prices"

  '

  'groupBox2

  '

  Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})

  Me.groupBox2.Location = New System.Drawing.Point(8, 128)

  Me.groupBox2.Name = "groupBox2"

  Me.groupBox2.Size = New System.Drawing.Size(312, 216)

  Me.groupBox2.TabIndex = 10

  Me.groupBox2.TabStop = False

  '

  'TenMostExpensive

  '

  Me.TenMostExpensive.Location = New System.Drawing.Point(8, 16)

  Me.TenMostExpensive.Name = "TenMostExpensive"

  Me.TenMostExpensive.Size = New System.Drawing.Size(168, 23)

  Me.TenMostExpensive.TabIndex = 8

  Me.TenMostExpensive.Text = "Ten Most Expensive Products"

  '

  'grdNorthwind

  '

  Me.grdNorthwind.DataMember = ""

  Me.grdNorthwind.HeaderForeColor = System.Drawing.SystemColors.ControlText

  Me.grdNorthwind.Location = New System.Drawing.Point(8, 48)

  Me.grdNorthwind.Name = "grdNorthwind"

  Me.grdNorthwind.Size = New System.Drawing.Size(296, 160)

  Me.grdNorthwind.TabIndex = 7

  '

  'Form1

  '

  Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

  Me.ClientSize = New System.Drawing.Size(328, 350)

  Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.groupBox2, Me.GroupBox1})

  Me.Name = "Form1"

  Me.Text = "Stored Procedures"

  Me.GroupBox1.ResumeLayout(False)

  Me.groupBox2.ResumeLayout(False)

  CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).EndInit()

  Me.ResumeLayout(False)

  End Sub

  #End Region

  Private Sub UpdatePrices_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdatePrices.Click

  ' version of code showing every single step

  'Dim sqlConnection As SqlConnection = New SqlConnection()

  'sqlConnection.ConnectionString = "server=(local);Integrated Security=SSPI;database=Northwind"

  'Dim sqlCommand As SqlCommand = New SqlCommand()

  'sqlCommand.Connection = sqlConnection

  'sqlCommand.CommandType = CommandType.StoredProcedure

  'sqlCommand.CommandText = "UpdatePrices"

  'Dim sqlParameter1 As SqlParameter = New SqlParameter()

  'sqlParameter1.ParameterName = "@return_value"

  'sqlParameter1.SqlDbType = SqlDbType.Int

  'sqlParameter1.Direction = ParameterDirection.ReturnValue

  'Dim sqlParameter2 As SqlParameter = New SqlParameter()

  'sqlParameter2.ParameterName = "@PriceMultiplier"

  'sqlParameter2.SqlDbType = SqlDbType.Decimal

  'sqlParameter2.Direction = ParameterDirection.Input

  'sqlParameter2.Value = textBoxPAF.Text

  'Dim sqlParameter3 As SqlParameter = New SqlParameter()

  'sqlParameter3.ParameterName = "@NbrRows"

  'sqlParameter3.SqlDbType = SqlDbType.Int

  'sqlParameter3.Direction = ParameterDirection.Output

  ' shortened version of code using constructor overloads

  Dim sqlConnection As SqlConnection = New SqlConnection("server=local;Integrated Security=SSPI;database=Northwind")

  Dim sqlCommand As SqlCommand = New SqlCommand("UpdatePrices", sqlConnection)

  sqlCommand.CommandType = CommandType.StoredProcedure

  Dim sqlParameter1 As SqlParameter = New SqlParameter("@return_value", SqlDbType.Int)

  sqlParameter1.Direction = ParameterDirection.ReturnValue

  Dim sqlParameter2 As SqlParameter = New SqlParameter("@PriceMultiplier", SqlDbType.Decimal)

  sqlParameter2.Direction = ParameterDirection.Input

  sqlParameter2.Value = textBoxPAF.Text

  Dim sqlParameter3 As SqlParameter = New SqlParameter("@NbrRows", SqlDbType.Int)

  sqlParameter3.Direction = ParameterDirection.Output

  sqlCommand.Parameters.Add(sqlParameter1) ' must be added first, parameter 0

  sqlCommand.Parameters.Add(sqlParameter2) ' parameter 1

  sqlCommand.Parameters.Add(sqlParameter3) ' parameter 2

  sqlConnection.Open()

  ' Lines 1 and 2 of 3 new lines added for explicit transaction support.

  Dim sqlTransaction As SqlTransaction = sqlConnection.BeginTransaction()

  sqlCommand.Transaction = sqlTransaction

  sqlCommand.ExecuteNonQuery() ' debugger can step into stored proc here

  Dim i As Integer = CInt(sqlCommand.Parameters("@return_value").Value)

  labelNbrPrices.Text = String.Concat(sqlCommand.Parameters("@NbrRows").Value.ToString(), " prices updated")

  ' Line 3 of 3 new lines added for explicit transaction support.

  sqlTransaction.Commit() ' or trans.Rollback(), must be before connection is closed

  sqlConnection.Close() ' alternatively, conn.Dispose

  End Sub

  Private Sub TenMostExpensive_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TenMostExpensive.Click

  Dim sqlConnection As SqlConnection = New SqlConnection("server=(local);Integrated Security=SSPI;database=Northwind")

  Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter("[Ten Most Expensive Products]", sqlConnection)

  sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

  ' Pass the name of the DataSet through the overloaded constructor of

  ' the DataSet class. When the DataSet is represented as XML, this name

  ' is used as the name of the XML document element.

  Dim dataSet As DataSet = New DataSet("Northwind")

  sqlConnection.Open()

  sqlDataAdapter.Fill(dataSet) ' debugger can step into stored proc here

  ' You could call a second stored procedure by using

  ' da.SelectCommand.CommandText followed by da.Fill

  sqlConnection.Close()

  grdNorthwind.DataSource = dataSet.Tables(0)

  End Sub

  End Class

  ////////////////////////////////////////////////////////////////////////

  I tried to run it - I got the following 3 errors:

  (1) 'Protected Overrides Sub Dispose (Depose As Boolean)' has multiple definition with identical signatures. Form1.Designer.vb Line 7

  (2) 'Private Sub InitializeComponent()' has multiple definitions with identical signatures. Form1.Designer.vb Line 24

  (3) 'Components' is already declared as 'Private Dim components As System.ComponentModel.Container' in this class Form1.vb Line 32

  ////////////////////////////////////////////////////////////////////////

  I have 3 questions to ask:

  (1) I know the author of the article wrote this set of source code for the regular versions of Visual Basic 2005, not for VB 2005 Express. Do I have to change any code statements to fit in my VB 2005 Express project?

  (2) The Northwind Database is in the Data Explorer of my VB 2005 Express (I do not have the Northwind Database in the Object Explorer of my SQL Server Management Studio Express). In this set of source code, there is a statement "Dim sqlConnection As SqlConnection=New SqlConnection('Server=local;Integrated Security=SSPI;database=Northwind"). Is the server=local in the above code statement right for connecting the Northwind Database in the Database Explorer? If it is not right, what is the right statement to do the connection task?

  (3) What are the causes for the 3 errors?

  Please help and advise.

  Thanks in advance,

  Scott Chang