SQL Server Table

	SQL Server Data Entry

Creating a Table

In order to use this lesson, you should be familiar with Microsoft SQL Server as we have covered it already. We will use SQL to create our table for data entry.

Start Microsoft SQL Server and open the SQL Query Analyzer.
In the empty query window, type and execute the following command (you execute the command by pressing F5):
CREATE DATABASE LiquorStore
After database has been created and you receive confirmation, delete the previous line

type and execute the following instructions that create a table and enters four records:

USE LiquorStore
CREATE TABLE Employees
(
    EmployeeID    INT IDENTITY(1, 1) NOT NULL,
    EmployeeNo    CHAR(6)     NULL,
    DateHired     VARCHAR(40) NULL,
    FirstName     VARCHAR(20) NULL,
    MI            CHAR(1)     NULL,
    LastName      VARCHAR(20) NULL,
    Address       VARCHAR(40) NULL,
    City          VARCHAR(32) NULL,
    State         CHAR(2)     NULL,
    ZIPCode       VARCHAR(12) NULL,
    Country       VARCHAR(30) NULL,
    Salary	  MONEY       NULL,
    HomePhone 	  VARCHAR(16) NULL,
    EmailAddress  VARCHAR(40) NULL,
    MaritalStatus BIT         NULL,
    Notes	  Text        NULL
)
/* Data Entry */ 
-- First Record --
INSERT INTO Employees(EmployeeNo, DateHired, FirstName, MI, LastName, Address, City,
		      State, ZIPCode, Country, Salary, HomePhone, EmailAddress, MaritalStatus)
       VALUES('GT-882','10/05/1995','Geraldine','F','Thomas','802 Epsilon Ave',
              'Silver Spring','MD','20904','USA',12.55,'(301) 524-7822','gthomas@hotmail.com',1)
-- Second Record --
INSERT Employees(EmployeeNo, DateHired, FirstName, MI, LastName, Address, City,
		      State, ZIPCode, Country, Salary, HomePhone, EmailAddress, MaritalStatus)
       VALUES('EB-405','8-22-1996', 'Ernest','','Bilong','1060 Calisto Rd #D12',
              'Alexandria','VA','22231-1244','USA',8.72,'(703) 276-8676','ebilong@yahoo.com',0)
-- Third Record --
INSERT Employees(EmployeeNo, DateHired, FirstName, MI, LastName, Address, City,
		      State, ZIPCode, Country, Salary, HomePhone, EmailAddress, MaritalStatus)
       VALUES('DJ-614',8/22/1996,'James','D','Datts','','','DC','','USA',10.18,
              '','dattsj@netscape.com',1)
-- Fourth Record --
INSERT Employees(EmployeeNo, DateHired, FirstName, MI, LastName, Address, City,
		      State, ZIPCode, Country, Salary, HomePhone, EmailAddress, MaritalStatus)
       VALUES('BC-200',4/15/1998,'Catherine','','Bollack','12442 Lockwood Drive',
              'Rockville','MD','','USA',10.44,'','bollackc1288@csumd.edu',1)

Close SQL Query Analyzer.
When asked whether you want to save the text, click Yes.
Change the name of the file to LiquorStore and make sure that a desired folder, such as My Documents, is selected in the Save In combo box. Click Save.

Creating an ODBC Data Source

Here are the steps to create the needed data source:

Start or open Control Panel and, in Control Panel, double-click Administrative Tools.
In the Administrative Tools window, double-click Data Sources (ODBC). Alternatively, from the Taskbar, you could have clicked Start -> Programs -> Administrative Tools -> Data Sources (ODBC).
In the ODBC Data Source Administrator property sheet, click the Add button:
In the Create New Data Source wizard, scroll down in the list box and click SQL Server:
Click Finish
In the Name edit box of the Create A New Data Source To SQL Server wizard, type LiqStore and press Tab
In the Description edit box, type The Liquor Store Application
Click the arrow of the Server combo box and select the server where the above (LiquorStore) database resides:
Click Next
Select the desired authentication. For this example, I accept the Windows NT Authentication. I also accept to connect to SQL Server with Default Settings by leaving the check box untouched:
Click Next
In the new page of the Create A New Data Source To SQL Server wizard, click the Change The Default Database To check box.
Once the combo box is enabled, click its arrow and select LiquorStore:
For this example, leave the Attach Database FileName check box unchecked and accept the other defaults. Click Next
Unless you have any other reason, accept the default (language, encryption, etc) settings of the new page:
Click Finish
On the ODBC Microsoft SQL Server Setup dialog box, click the Test Data Source button:
When the SQL Server ODBC Data Source Test dialog box confirms that the TESTS COMPLETED SUCCESSFULLY!, click OK:
On the ODBC Microsoft SQL Server Setup dialog box, click OK.
In the User DSN property page of the ODBC Data Source Administrator dialog box, make sure that the LiqStore data store is listed in the User Data Sources list box:
Click OK

Creating the Application

Start Microsoft Visual C++
On the main menu, click File -> New
In the New dialog box, click Projects and click MFC AppWizard (exe)
In the Location edit box, select or type the desired folder for the application. In the Project Name edit box, type LiqStoreDB and click OK
In the MFC AppWizard - Step 1 dialog box, click the Single Document radio button and click Next
In the MFC AppWizard - Step 2 of 6, click the Database View Without File Support (because we will not need and will not use file support for this exercise).
Click the Data Source button.
In the Database Options dialog box, in the Datasource section, make sure the ODBC radio button is selected and click the arrow of its combo box. Select LiqStore
In the Recordset Type section, click the Dynaset radio button
Click OK
In the Select Database Tables, click dbo.Employees and click OK
In the MFC AppWizard - Step 2 of 6, click Next
In the MFC AppWizard - Step 3 of 6, accept all defaults and click Next
In the MFC AppWizard - Step 4 of 6, uncheck the Printing And Print Preview check box
Click the Advanced button and click the Window Styles property page. Uncheck the Maximize Box check box and click Close
Click Next
In the MFC AppWizard - Step 5 of 6, accept all defaults and click Next
In the MFC AppWizard - Step 6 of 6, accept all defaults and click Finish.
In the New Project Information dialog box, click OK.
In the Workspace, click the ResourceView tab, expand the String Table, and double-click String Table. Double-click IDR_MAINFRAME and replace its entire Caption with Four-Corner Liquor Store - Employees Records and close the floating window. Also close the String Table window.
Using the Controls window, design form as follows:
Set the IDs of the edit controls as follows: IDC_EMPLOYEEID (Read-Only), IDC_EMPLOYEENBR, IDC_DATEHIRED, IDC_FIRSTNAME, IDC_MI, IDC_LASTNAME, IDC_ADDRESS, IDC_CITY, IDC_STATE, IDC_ZIPCODE, IDC_COUNTRY, IDC_SALARY, IDC_EMAILADDRESS, IDC_HOMEPHONE, and IDC_NOTES (Multiline, Vertical Scroll, Want Return). Change the ID of the check box to IDC_MARITALSTATUS,
Save everything.
In the Workspace, click ClassView tab. Expand the LiqStoreDB classes node and double-click CLiqStoreDBSet. Notice that the CRecordset class already has variables declared for each control of our form.
To associate each control with the appropriate variable, on the main menu, click View -> ClassWizard...
In the MFC ClassWizard dialog box, click the Member Variables property sheet. In the Class Name combo box, select CLiqStoreDBView.
In the list box, double-click IDC_ADDRESS
In the Add Member Variable dialog box, click the arrow of the Member Variable Name combo box and select m_pSet->m_Address:
Make sure that the Variable Type is set to CString and click OK
Do the same for the other controls:
On the MFC ClassWizard, click OK
Test the application
Close the application
As you can see, this database was created without a single line of code.

Adding a Record

In the Workspace, click the ResourceView tab. Expand the LiqStoreDB Resource tree and expand the Menu node.
Double-click IDR_MAINFRAME and add an Add menu item:
Add a button on the toolbar and associate to it the same identifier as the Add menu item:
Press Ctrl + W to access the ClassWizard.
In the Class Name combo box, select CLiqStoreDBView and, in the Object IDs list box, select ID_RECORD_ADD
In the Messages list box, double-click COMMAND
Accept the suggested name of the function and click Edit Code

Implement the function as follows:

void CLiqStoreDBView::OnRecordAdd() 
{
	SetDlgItemText(IDC_EMPLOYEEID, "");
	SetDlgItemText(IDC_EMPLOYEENBR, "");
	SetDlgItemText(IDC_DATEHIRED, "");
	SetDlgItemText(IDC_FIRSTNAME, "");
	SetDlgItemText(IDC_MI, "");
	SetDlgItemText(IDC_LASTNAME, "");
	SetDlgItemText(IDC_ADDRESS, "");
	SetDlgItemText(IDC_CITY, "");
	SetDlgItemText(IDC_STATE, "");
	SetDlgItemText(IDC_ZIPCODE, "");
	SetDlgItemText(IDC_COUNTRY, "");
	SetDlgItemText(IDC_SALARY, "");
	SetDlgItemText(IDC_EMAILADDRESS, "");
	SetDlgItemText(IDC_HOMEPHONE, "");
	SetDlgItemText(IDC_NOTES, "");

	reinterpret_cast<CButton *>(GetDlgItem(IDC_MARITALSTATUS))->SetCheck(FALSE);
	
	m_pSet->AddNew();
	m_pSet->Update();
	m_pSet->Requery();
}

Test the application and return to MSVC

Deleting a Record

Add a Delete menu item:
On the toolbar, add a button that has the same identifier as the Delete menu item:

Implement its COMMAND function as follows:

void CLiqStoreDBView::OnRecordDelete() 
{
	m_pSet->Delete();
	m_pSet->MoveNext();
	if( m_pSet->IsEOF() )
		m_pSet->MoveLast();
}

Test the application