Visual Basic Code Snippet - Get Scalar using open connection
(VB) Visual Basic code snippet connects to SQL server and executes SQL statement and returns the first column of the first row in the result set returned by the query as a object. ExecuteScalar returns an object using open database connection and SQL statement.
Bookmark:
Visual Basic Code Snippet - Get Scalar using open connection
This .Net Visual Basic code snippet connects to SQL server and executes SQL statement and returns the first column of the first row in the result set returned by the query as a object. Additional columns or rows are ignored. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlCommand. Modify the exception handling section to as your project requirements.
Public Function ExecuteScalar(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As Object Dim _SqlRetVal As Object = Nothing Try ' Executes the query, and returns the first column of the first row in the result ' set returned by the query. Additional columns or rows are ignored. Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection) _SqlRetVal = _SqlCommand.ExecuteScalar() ' 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 Nothing End Try Return _SqlRetVal End Function
Here is a simple example showing how to use above function (ExecuteScalar) to connect to SQL database and get first column of the first row in the result set returned by the query as a object.
' 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 ' Lets call above function to execute scalar ' using open database connection and SQL statement ' Pass open database connection to function ' Pass SQL statement to create SqlDataReader Dim _object As Object = ExecuteScalar(_SqlConnection, "SELECT name FROM sampletable") ' Check we have data If _object IsNot Nothing Then Console.WriteLine(_object.ToString()) End If ' close database connection _SqlConnection.Close(); End If
VB Keywords Used:
- ExecuteScalar
- SqlConnection
- ConnectionString
- SqlCommand
- Exception
Code Snippet Information:
- Applies To: .Net, VB, Visual Basic, CLI, SQL, SqlCommand, ExecuteScalar, SQL Server, SQL Client, Connection String, Database Connection, SQL Data Reader
- Programming Language : Visual Basic (VB)
External Resources: