Interacting with Databases - Were Any Rows Returned?
(Page 4 of 4 )
The question that your application needs to answer after sending a query to a DBMS is whether or not the DBMS found any information that matches your query. The easiest way to answer this question is to examine the HasRows property of the DataReader class.
The value of the HasRows property determines if any records were returned by the DBMS. It is true if records are returned; otherwise, the value of the HasRows property is false. It is important to remember that the HasRows property does not tell you the number of records that are returned. Instead, it simply states if any are returned.
Let’s modify the previous examples to include the HasRows property. We’ll begin with the Microsoft SQL Server example.
<% Import Namespace="System.Data.SqlClient" %>
<%
Dim custDb As SqlConnection
Dim cmdSelectCustomers As SqlCommand
Dim dtrCustomers As SqlDataReader
custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
custDb.Open()
cmdSelectCustomers = New SqlCommand( "Select custFirstName, custLastName From
custContact", custDb)
dtrCustomers = cmdSelectCustomers.ExecuteReader()
If dtrCustomers.HasRows Then
While dtrCustomers.Read()
Response.Write(CStr(dtrCustomers.Item("custFirstName")) & " " & _
CStr(dtrCustomers.Item("custLastName")))
Response.Write("<BR>")
End While
Else
Response.Write("There are no customers.")
End If
dtrCustomers.Close()
custDb.Close()
%>
Here’s how to do this with Microsoft Access:
<% Import Namespace="System.Data.OleDb" %>
<%
Dim custDb As OleDbConnection
Dim cmdSelectCustomers As OleDbCommand
Dim dtrCustomers As OleDbDataReader
custDb = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:
ust.mdb")
custDb.Open()
cmdSelectCustomers = New OleDbCommand( "Select custFirstName, custLastName From
custContact", custDb)
dtrCustomers = cmdSelectCustomers.ExecuteReader()
If dtrCustomers.HasRows Then
While dtrCustomers.Read()
Response.Write(CStr(dtrCustomers.Item("custFirstName")) & " " & _
CStr(dtrCustomers.Item("custLastName")))
Response.Write("<BR>")
End While
Else
Response.Write("There are no customers.")
End If
dtrCustomers.Close()
custDb.Close()
%>
Retrieving a Specific Row
It is very common that you’ll need to look for particular information stored in a database such as a customer number. To do this, you’ll need to include a Where clause in your query. The Where clause requires two pieces of information: a search value and the column that contains the search value.
Let’s say that you want to retrieve the customer number and customer name for customer number 1234. Here’s the query that you’ll need to write:
Select custNumber, custFirstName, custLastName
From custContact
Where custNumber = '1234'
The Select statement is nearly identical to the query you wrote earlier in this chapter, except we’ve included the custNumber column. Remember that columns that appear in the Select statement are returned by the DBMS.
The From clause is the same as in other queries in that it tells the DBMS to use the custContact table.
The Where clause is new to the query. It tells the DBMS to search for 1234 in the custNumber column. Only rows that have 1234 in the custNumber column are returned by the DBMS. There is only one row in our example that has 1234 as a customer number, so only that row is returned.
Replace the query in the previous examples with this query and run the application to retrieve customer Bob Smith from the DBMS.
Please check back next week for the continuation of this article.
