Home

Locating a Record

 

Introduction

Locating a record consists of selecting one among many others. To do this, you can create a SELECT statement that would use a condition that makes it possible to isolate a unique record that responds to that condition. For example, imagine you are creating a database for a bank. In that database, you would include a list of customers. Each customer's record can include the customer's account number and possibly some other relevant pieces of information. Here is an example of such a table:

-- =============================================
-- Database:     ynb1
-- For:          Yugo National Bank
-- Author:       FunctionX
-- Date Created: Monday 18 April 2007
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'ynb1'
)
DROP DATABASE ynb1
GO
CREATE DATABASE ynb1
GO
-- =========================================
-- Database: ynb1
-- For:      Yugo National Bank
-- Table:    Customers
-- =========================================
USE ynb1;
GO
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
  DROP TABLE dbo.Customers
GO
CREATE TABLE Customers
(
    CustomerID int Identity(1,1) NOT NULL,
    AccountNumber varchar(20),
    AccountName varchar(50) NOT NULL,
    Address varchar(120) NOT NULL,
    City varchar(40) NOT NULL,
    State varchar(40) NOT NULL,
    ZIPCode varchar(12) NOT NULL,
    Country varchar(50) DEFAULT('USA'),
    HomePhone varchar(20) NOT NULL,
    Notes text, 
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
);
GO

INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('28-3782-84', 'James Carlton Brokeridge', 
       '1022 Arlington Rd', 'Arlington',
       'VA', '20164', '(703) 645-1492');
INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('92-3782-43', 'Chrissy Arlene McMahon',
       '845 Arcadia Ave. #1512', 'Rockville',
       'MD', '20872', '(301) 684-2828');
INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('38-4227-52', 'James Norris',
       '1277 Cecil Maurice Av.', 'Chevy Chase',
       'MD', '20870', '(301) 768-4024');
INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('68-6434-56', 'Eldridge Powers',
       '273 S. Independence Ave.', 'Alexandria',
       'VA', '20185', '(703) 622-7188');
INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('83-4654-77', 'Hobert Umbro Spampinato',
       '8254 12th St. N.E.', 'Washington',
       'DC', '20008', '(202) 927-1040');
INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('47-4783-25', 'Gloria Aline Wright', 
       '15328 Crystal St.', 'Hyattsville',
       'MD', '20782', '(301) 723-5656');
INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('82-3763-24', 'Liliana Wellie Ortez',
       '4445 Blue Oak St. #6A', 'Chevy Chase',
       'MD', '20875', '(301) 821-4990');
INSERT INTO Customers(AccountNumber, AccountName,
       Address, City, State, ZIPCode, HomePhone)
VALUES('72-3474-24', 'Ornella Maiwand',
       '2888 Gwett Richards Av.', 'Rockville',
       'MD', '20815', '(301) 478-8244');
GO

To view the information related to a customer, after the user has entered an account number in a form, you can locate the record associated with that account. Here is an example:

Now, when the user enters an account number and the text box loses focus, you can search the account number in the table of customers and display the result if that account is found:

System::Void txtAccountNumber_Leave(System::Object^  sender, System::EventArgs^  e)
{
    SqlConnection ^ conDatabase = nullptr;

    try {
	String ^ sqlSelect =
		 String::Concat(L"SELECT * FROM Customers WHERE AccountNumber = '",
				 this->txtAccountNumber->Text, "';");
	conDatabase = gcnew SqlConnection(L"Data Source=(local);Database='ynb1';"
			                  L"Integrated Security=true");
	SqlCommand ^ cmdDatabase = gcnew SqlCommand(sqlSelect, conDatabase);

	try {
	    DataSet ^dsCustomers = gcnew DataSet(L"CustomersSet");
	    SqlDataAdapter ^ sdaCustomers = gcnew SqlDataAdapter();
	    sdaCustomers->SelectCommand = cmdDatabase;
	    sdaCustomers->Fill(dsCustomers);

	    DataRow ^ recCustomer = dsCustomers->Tables[0]->Rows[0];

	    if( recCustomer->IsNull(L"CustomerID") )
		throw gcnew IndexOutOfRangeException("Invalid Account Number");
						 
	    this->txtAccountName->Text =
		 dynamic_cast<String ^>(recCustomer[L"AccountName"]);
	  this->txtAddress->Text = dynamic_cast<String ^>(recCustomer[L"Address"]);
	  this->txtCity->Text = dynamic_cast<String ^>(recCustomer[L"City"]);
	  this->txtState->Text = dynamic_cast<String ^>(recCustomer[L"State"]);
	  this->txtZIPCode->Text = dynamic_cast<String ^>(recCustomer[L"ZIPCode"]);
	  this->txtCountry->Text = dynamic_cast<String ^>(recCustomer[L"Country"]);
	  this->txtHomePhone->Text = 
	    dynamic_cast<String ^>(recCustomer[L"HomePhone"]);
	  this->txtNotes->Text = dynamic_cast<String ^>(recCustomer[L"Notes"]);
	}
	catch(IndexOutOfRangeException ^)
	{
	    MessageBox::Show(L"The account number you entered is not valid");
	    this->txtAccountName->Text = L"";
	    this->txtAddress->Text = L"";
	    this->txtCity->Text = L"";
	    this->txtState->Text = L"";
	    this->txtZIPCode->Text = L"";
	    this->txtCountry->Text = L"";
	    this->txtHomePhone->Text = L"";
	    this->txtNotes->Text = L"";
	}
    }
    finally
    {
	conDatabase->Close();
    }
}

Here is an example of running the program:

 

 

Home Copyright © 2007-2012 FunctionX