Visual Basic Code Snippet - Check Record Exists in SQL Server Database

Visual Basic Code Snippet - Check Record Exists in SQL Server Database

This .Net Visual Basic code snippet connects to SQL server and executes SQL statement to determine whether the given record exists in the database. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to execute sql statement and return logical (True/False) result to check record exists or not in the database. Modify the exception handling section for your project requirements.

Public Function RecordExists(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As Boolean
    Dim _SqlDataReader As System.Data.SqlClient.SqlDataReader = Nothing

    Try
        ' Pass the connection to a command object
        Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)

        ' get query results
        _SqlDataReader = _SqlCommand.ExecuteReader()

    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 False
    End Try

    If _SqlDataReader IsNot Nothing AndAlso _SqlDataReader.Read() Then
        ' close sql reader before exit
        If _SqlDataReader IsNot Nothing Then
            _SqlDataReader.Close()
            _SqlDataReader.Dispose()
        End If

        ' record found
        Return True
    Else
        ' close sql reader before exit
        If _SqlDataReader IsNot Nothing Then
            _SqlDataReader.Close()
            _SqlDataReader.Dispose()
        End If

        ' record not found
        Return False
    End If
End Function

Here is a simple example showing how to use above function (RecordExists) to login to SQL server and check whether the record exists in the database.

' 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
    If RecordExists(_SqlConnection, "SELECT name FROM sampletable WHERE productid = 2") Then
        ' record found in DB, lets do record found task
        Console.WriteLine("Record exists")
    Else
        ' record not found in DB, lets do record not found task
        Console.WriteLine("Record not found")
    End If

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

VB Keywords Used:

• SqlDataReader
• SqlConnection
• ConnectionString
• SqlCommand
• ExecuteReader
• Exception

Code Snippet Information:

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

External Resources:

• SqlDataReader Class
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• SqlCommand.ExecuteReader Method
• Exception Class

sunil jadhav :: March 31-2011 :: 12:58 PM

hi i have created one project in vb 6.0 with sql server 2000 and my database is in remote system and i want to create sql server 2000 database back using vb 6.0 button code please help me ......... thank you ?

Leave a comment