Visual Basic Code Snippet - ExecuteNonQuery against the SQL Server Database

Visual Basic Code Snippet - ExecuteNonQuery against the SQL Server Database

This .Net Visual Basic code snippet connects to SQL server and executes a SQL statement and returns the number of rows affected. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to executes a Transact-SQL statement against the connection and returns the number of rows affected. Modify the exception handling section for your project requirements.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. If SQL statement fail to execute it returns 0. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Public Function ExecuteNonQuery(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As Integer
    Dim _SqlRetVal As Integer = 0

    Try
        ' Executes a Transact-SQL statement against the connection 
        ' and returns the number of rows affected.
        Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)

        _SqlRetVal = _SqlCommand.ExecuteNonQuery()

        ' Dispose command
        _SqlCommand.Dispose()
        _SqlCommand = Nothing
    Catch _Exception As Exception
        ' Error occurred while trying to execute reader
        ' send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message)

        Return 0
    End Try

    Return _SqlRetVal
End Function

Here is a simple example showing how to use above function (ExecuteNonQuery) to login to SQL server and insert new record to existing table in the SQL server database table and update existing record in the database table.

' set temporary variable for database connection
Dim _SqlConnection As New System.Data.SqlClient.SqlConnection()

' assign database connection string
_SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;"

' Connect to database
Try
    _SqlConnection.Open()
Catch _Exception As Exception
    ' Error occurred while trying to connect to database
    Console.WriteLine(_Exception.Message)
End Try

' Check for valid open database connection before query database
If _SqlConnection IsNot Nothing AndAlso _SqlConnection.State = ConnectionState.Open Then
    ' add new record to database table
    If ExecuteNonQuery(_SqlConnection, "INSERT INTO sampletable (name, price) VALUES ('sample product name', 22.75)") >0 Then
        ' Record successfully insert into database
        Console.WriteLine("Record added to database table")
    Else
        ' Record failed to insert into database
        Console.WriteLine("Failed to add new record to database table")
    End If


    ' Update record
    If ExecuteNonQuery(_SqlConnection, "UPDATE sampletable SET price = 34.25 WHERE productid = 2") > 0 Then
        ' update record successfull
        Console.WriteLine("Record updated.")
    Else
        ' Record failed to update
        Console.WriteLine("Failed to update record")
    End If

    ' close database connection
    _SqlConnection.Close();
End If

VB Keywords Used:

• ExecuteNonQuery
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

• Applies To: .Net, VB, Visual Basic, CLI, SQL, ExecuteNonQuery, SQL Server, SQL Client, Connection String, Database Connection
• Programming Language : Visual Basic (VB)

External Resources:

• SqlCommand.ExecuteNonQuery Method
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class

Leave a comment