VB.NET Sql Command Update Statement Source Code Example

Posted by on Tuesday, December 8, 2009 · Leave a Comment 

VB.NET Sql Command Update Statement Source Code Example

Purpose: – Illustrates using and checking the result.
Prerequistes:

  1. Install Visual Basic (Express or Standard Edition)
  2. Install SQL Server Express
  3. Download Northwind Database
  4. Attach Northwind Database to Databases in Sql Express

Notes:

  • Console Application is used to simplify things, but Windows Forms or Web Forms could also be used
  • You can build a library of syntax examples by using same project over and over and just commenting out what you do not want to execute in Module1.vb

Instructions:

  1. Use VB 2008 (Express or Standard) Edition
  2. Create new project; select Console Application; name of Project could be VBNET_Syntax.
  3. Right-click project name in solution explorer; add new folder; name of folder could be DatabaseADONET
  4. Right-click folder; add class; class name could be clsSqlCommandUpdate.vb
  5. Copy code into clsSqlCommandUpdate.vb
  6. Copy code into Module1.vb
  7. Click green arrow to start with debugging

Step 1: Use View Plain to Cut-n-paste code into clsSqlCommandUpdate.vb

 
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

Step 2: Use View Plain to Cut-n-paste code into Module1.vb

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

Filed under Code Samples · Tagged with , , , ,