Progress Monitor for IdealProgrammer.com

Take Swift, Intelligent, Massive, Planned, Loving, Effective (S.I.M.P.L.E.) Action to Transform Self into Ideal

Skip Repetitive Navigational Links

Home

News

Presenters

Contact

Please login

VB.NET Sql Command Update Statement Source Code Example

Description: Illustrates using Sql Command Update statement and checking the result.

Example Class:

 
Imports System
Imports System.Data
Imports System.Data.SqlClient
 
 
Public Class clsSqlCommandUpdate
    'Create Connection
    Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
        "integrated security=sspi;database=Northwind")
 
 
 
 
    Sub Main()
 
        OpenConnection()
 
        'Insert Rows to make sure they exist
        Console.WriteLine(vbCrLf)
        Console.WriteLine("***Insert Rows to make sure they exist***")
        InsertRows()
 
        'Display Rows Before Update
        Console.WriteLine(vbCrLf)
        Console.WriteLine("***Display Rows Before Update***")
        SelectRows()
 
        'Update Rows
        Console.WriteLine(vbCrLf)
        Console.WriteLine("***Perform Update***")
        UpdateRows()
 
        'Display Rows after update
        Console.WriteLine(vbCrLf)
        Console.WriteLine("***Display Rows After Update***")
        SelectRows()
 
        'Clean up with delete of all inserted rows
        Console.WriteLine(vbCrLf)
        Console.WriteLine("***Clean Up By Deleting Inserted Rows***")
        DeleteRows()
 
        ' Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
 
        Console.ReadLine()
    End Sub
    Sub OpenConnection()
        Try
            ' Open Connection
            thisConnection.Open()
            Console.WriteLine("Connection Opened")
        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        End Try
    End Sub
    Sub SelectRows()
 
        Try
            ' Sql Select Query 
            Dim sql As String = "SELECT * FROM Employees"
            Dim cmd As SqlCommand = New SqlCommand(sql, thisConnection)
 
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            Dim strEmployeeID As String = "EmployeeID"
            Dim strFirstName As String = "FirstName"
            Dim strLastName As String = "LastName"
 
            Console.WriteLine("{0} | {1} | {2}", _
                   strEmployeeID.PadRight(10), _
                   strFirstName.PadRight(10), _
                   strLastName)
            Console.WriteLine("==========================================")
            While dr.Read()
                'reading from the datareader
                Console.WriteLine("{0} | {1} | {2}", _
                   dr("EmployeeID").ToString().PadRight(10), _
                   dr("FirstName").ToString().PadRight(10), _
                   dr("LastName"))
            End While
            dr.Close()
            Console.WriteLine("==========================================")
 
        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        End Try
 
    End Sub
 
    Sub InsertRows()
 
 
        'Insert Rows to make sure row exists before updating
        'Create Command object
        Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()
 
        Try
 
            ' Create INSERT statement with named parameters
            nonqueryCommand.CommandText = _
               "INSERT  INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName)"
 
            ' Add Parameters to Command Parameters collection
            nonqueryCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10)
            nonqueryCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 20)
 
            ' Prepare command for repeated execution
            nonqueryCommand.Prepare()
 
            ' Data to be inserted
            Dim names() As String = {"Wade", "David", "Charlie"}
            For i As Integer = 0 To 2
                nonqueryCommand.Parameters("@FirstName").Value = names(i)
                nonqueryCommand.Parameters("@LastName").Value = names(i)
 
                Console.WriteLine("Executing {0}", _
                   nonqueryCommand.CommandText)
                Console.WriteLine("Number of rows affected : {0}", _
                   nonqueryCommand.ExecuteNonQuery())
            Next i
        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        Finally
 
 
        End Try
 
    End Sub
 
    Sub UpdateRows()
 
        Try
            ' 1. Create Command
            ' Sql Update Statement
            Dim updateSql As String = _
               "UPDATE Employees " & _
               "SET LastName = @LastName " & _
               "WHERE FirstName = @FirstName"
            Dim UpdateCmd As New SqlCommand(updateSql, thisConnection)
 
            ' 2. Map Parameters
 
            UpdateCmd.Parameters.Add("@FirstName", _
               SqlDbType.NVarChar, 10, "FirstName")
 
            UpdateCmd.Parameters.Add("@LastName", _
               SqlDbType.NVarChar, 20, "LastName")
 
            UpdateCmd.Parameters("@FirstName").Value = "Wade"
            UpdateCmd.Parameters("@LastName").Value = "Harvey"
 
            UpdateCmd.ExecuteNonQuery()
 
        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        End Try
 
    End Sub
 
    Sub DeleteRows()
 
        Try
            'Create Command objects
            Dim scalarCommand As New SqlCommand("SELECT COUNT(*) FROM Employees", thisConnection)
 
            ' Execute Scalar Query
            Console.WriteLine("Before Delete, Number of Employees = {0}", _
               scalarCommand.ExecuteScalar())
 
 
            ' Set up and execute DELETE Command
            'Create Command object
            Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()
            nonqueryCommand.CommandText = _
               "DELETE FROM Employees WHERE " & _
               "Firstname='Wade'  or " & _
               "Firstname='Charlie' AND Lastname='Charlie' or " & _
               "Firstname='David' AND Lastname='David' "
            Console.WriteLine("Executing {0}", nonqueryCommand.CommandText)
            Console.WriteLine("Number of rows affected : {0}", _
               nonqueryCommand.ExecuteNonQuery())
 
            ' Execute Scalar Query
            Console.WriteLine("After Delete, Number of Employee = {0}", _
               scalarCommand.ExecuteScalar())
 
        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        End Try
 
    End Sub
 
End Class

Example Module:

Module Module1
 
    Sub Main()
        '***** DataBaseADONET *************
 
        Dim mySqlCommandUpdate As New clsSqlCommandUpdate
        mySqlCommandUpdate.Main()
 
 
    End Sub
 
End Module