Simple Database Command Tester : SqlCommand Parameter « Database ADO.net « VB.Net

1. VB.Net
2. Database ADO.net
3. SqlCommand Parameter

Simple Database Command Tester

  

Imports System.Data.SqlClient
Imports System.Windows.Forms

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class TestForm
    Inherits System.Windows.Forms.Form

    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.cmdParameterized = New System.Windows.Forms.Button
        Me.cmdInsert = New System.Windows.Forms.Button
        Me.UpdateDB = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        Me.cmdParameterized.Location = New System.Drawing.Point(12, 124)
        Me.cmdParameterized.Name = "cmdParameterized"
        Me.cmdParameterized.Size = New System.Drawing.Size(161, 42)
        Me.cmdParameterized.TabIndex = 5
        Me.cmdParameterized.Text = "Perform Insert with Parameterized Command"
        '
        Me.cmdInsert.Location = New System.Drawing.Point(12, 75)
        Me.cmdInsert.Name = "cmdInsert"
        Me.cmdInsert.Size = New System.Drawing.Size(161, 43)
        Me.cmdInsert.TabIndex = 4
        Me.cmdInsert.Text = "Perform Insert with Stored Procedure"
        '
        Me.UpdateDB.Location = New System.Drawing.Point(12, 27)
        Me.UpdateDB.Size = New System.Drawing.Size(161, 42)
        Me.UpdateDB.TabIndex = 3
        Me.UpdateDB.Text = "Perform Direct Update"
        '
        'TestForm
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Controls.Add(Me.cmdParameterized)
        Me.Controls.Add(Me.cmdInsert)
        Me.Controls.Add(Me.UpdateDB)
        Me.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.ResumeLayout(False)

    End Sub
    Friend WithEvents cmdParameterized As System.Windows.Forms.Button
    Friend WithEvents cmdInsert As System.Windows.Forms.Button
    Friend WithEvents UpdateDB As System.Windows.Forms.Button

    Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateDB.Click
        Dim Connect As String = "Settings.Northwind"
        Dim con As New SqlConnection(Connect)
        con.Open()
        Dim SQL As String = "UPDATE Orders SET ShipCountry='O' WHERE OrderID='11'"
        Dim cmd As New SqlCommand(SQL, con)
        Dim NumAffected As Integer
        NumAffected = cmd.ExecuteNonQuery()
        con.Close()
        MessageBox.Show(NumAffected.ToString & " records updated", "Results", MessageBoxButtons.OK)
    End Sub

    Private Sub cmdInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInsert.Click
        Dim Connect As String = "My.Settings.Northwind"
        Dim con As New SqlConnection(Connect)
        con.Open()

        Dim cmd As New SqlCommand("AddNewCustomer", con)
        cmd.CommandType = CommandType.StoredProcedure

        Dim param As SqlParameter

        param = cmd.Parameters.AddWithValue("@CustomerID", "111")
        param = cmd.Parameters.AddWithValue("@CompanyName", "A")
        param = cmd.Parameters.AddWithValue("@ContactName", "M")

        Dim NumAffected As Integer
        NumAffected = cmd.ExecuteNonQuery()

        con.Close()
        MessageBox.Show(NumAffected.ToString & " records inserted", "Results", MessageBoxButtons.OK)
    End Sub

    Private Sub cmdParameterized_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdParameterized.Click
        Dim Connect As String = "My.Settings.Northwind"
        Dim con As New SqlConnection(Connect)
        con.Open()

        Dim SQL As String = "UPDATE Orders SET ShipCountry=@ShipCountry WHERE OrderID=@OrderID"
        Dim cmd As New SqlCommand(SQL, con)
        cmd.Parameters.AddWithValue("@ShipCountry", "O")
        cmd.Parameters.AddWithValue("@OrderID", 1)

        Dim NumAffected As Integer
        NumAffected = cmd.ExecuteNonQuery()
        con.Close()
        MessageBox.Show(NumAffected.ToString & " records inserted", "Results", MessageBoxButtons.OK)
    End Sub
End Class

   
    
  

Related examples in the same category

1.	Use Parameters in your sql command
2.	Pass two parameters
3.	Pass Parameter into SQL command