How To - ExecuteScalar ()

When To Use:

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

ExecuteScalar is a fast way to fetch one value.


Results:

If ExecuteScalar return 0 (zero) means no similar record found in database (According to condition is defined in WHERE clause).


Code Snippets:

Snippet 1:

Scenario: Avoid entry of duplicate user names by using ExecuteScalar method.

DBConnection.Open()
SQLString = "SELECT Count(*) FROM tblLogin " & _
WHERE username = '" & txtUserName.Text & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
lblError.Text = "Sorry. User Name Already Present."
End If
DBConnection.Close()


Snippet 2:

Scenario: Check whether User Name and Password is present in database by using ExecuteScalar method.

DBConnection.Open()
SQLString = "SELECT Count(*) FROM tblLogin " & _
WHERE username = '" & txtUserName.Text & "' AND Password='" & txtPassword.Text & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
'IF condition satisfied means User Name and Password is correct.
' write code below here.
End If
DBConnection.Close()

Conclusion:

Use to get result of Queries like count, min, max...