Example 20 is same as example 19 but with two differences.
Firstly the System.Data.OleDb namespace is imported at the top of the page rather in the System. Data.SqlClient namespace.
Secondly,Instead of SQL classes OleDb classes are used in all the statements.
After you finish displaying the records from a DataReader, be sure to explicitly close it (using the Close () method).
Retrieving a Single Database Record
The developers generally need a single record from a database. Therefore it is very important to know how to retrieve it. For example, given a username you might have to look up someone's password or retrieve a phone number for John from a database table.
Another common situation which requires retrieval of single database record is aggregate functions like MAX (), MIN (), COUNT () etc.
The previous section dealt with the use of a DataReader representing the results of a database query. But if there is a need, to retrieve a single result from a query, the ExecuteScalar () method is more efficient than the ExecuteReader () method, as it requires less coding.
The following example counts the no. of records from Employees table. We use the ExecuteScalar () method to retrieve and display the value of the SQL Count (*) aggregate function.
Example 21 SqlExecuteScalarCount.aspx
<%@ Import Namespace="System.Data.SqlClient" %>
Dim conNorthwind As SqlConnection
Dim cmdSelectCount As SqlCommand
conNorthwind = New SqlConnection( “Server=localhost;UID=sa;PWD=secret;database=Northwind" )
cmdSelectCount = New SqlCommand( "Select Count(*) From Employees", conNorthwind )
lblEmp.Text = cmdSelectCount.ExecuteScalar()
Employees in this Table
The output is shown below:
To execute the page with an Access or Oracle database, classes have to be used from the System.Data.OleDb or System.Data.OracleClient namespace rather than the System.Data.SqlClient namespace as shown