Visual C++ Main Page

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.

 

  1. Start Microsoft SQL Server and open the SQL Query Analyzer.
  2. In the empty query window, type and execute the following command (you execute the command by pressing F5):
     
    CREATE DATABASE LiquorStore
  3. After database has been created and you receive confirmation, delete the previous line
  4. 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)
  5. Close SQL Query Analyzer.
  6. When asked whether you want to save the text, click Yes.
  7. 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:

  1. Start or open Control Panel and, in Control Panel, double-click Administrative Tools.
  2. 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).
  3. In the ODBC Data Source Administrator property sheet, click the Add button:
     
    ODBC Data Source Administrator
  4. In the Create New Data Source wizard, scroll down in the list box and click SQL Server:
     
    Create A New Data Source
  5. Click Finish
  6. In the Name edit box of the Create A New Data Source To SQL Server wizard, type LiqStore and press Tab
  7. In the Description edit box, type The Liquor Store Application
  8. Click the arrow of the Server combo box and select the server where the above (LiquorStore) database resides:
     
    Create A New Data Source To SQL Server
  9. Click Next
  10. 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:
     
    Create A New Data Source To SQL Server
  11. Click Next
  12. In the new page of the Create A New Data Source To SQL Server wizard, click the Change The Default Database To check box.
  13. Once the combo box is enabled, click its arrow and select LiquorStore:
     
    Create A New Data Source To SQL Server
  14. For this example, leave the Attach Database FileName check box unchecked and accept the other defaults. Click Next
  15. Unless you have any other reason, accept the default (language, encryption, etc) settings of the new page:
     
    Create A New Data Source To SQL Server
  16. Click Finish
  17. On the ODBC Microsoft SQL Server Setup dialog box, click the Test Data Source button:
     
    ODBC Microsoft SQL Server Setup
  18. When the SQL Server ODBC Data Source Test dialog box confirms that the TESTS COMPLETED SUCCESSFULLY!, click OK:
     
    SQL Server ODBC Data Source Test
  19. On the ODBC Microsoft SQL Server Setup dialog box, click OK.
  20. 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:
     
    ODBC Data Source Administrator
  21. Click OK

Creating the Application

  1. Start Microsoft Visual C++
  2. On the main menu, click File -> New
  3. In the New dialog box, click Projects and click MFC AppWizard (exe)
  4. 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
  5. In the MFC AppWizard - Step 1 dialog box, click the Single Document radio button and click Next
  6. 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).
  7. Click the Data Source button.
  8. 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
  9. In the Recordset Type section, click the Dynaset radio button
     
    Database Options
  10. Click OK
     
    Select Database Tables
  11. In the Select Database Tables, click dbo.Employees and click OK
  12. In the MFC AppWizard - Step 2 of 6, click Next
  13. In the MFC AppWizard - Step 3 of 6, accept all defaults and click Next
  14. In the MFC AppWizard - Step 4 of 6, uncheck the Printing And Print Preview check box
  15. Click the Advanced button and click the Window Styles property page. Uncheck the Maximize Box check box and click Close
  16. Click Next
  17. In the MFC AppWizard - Step 5 of 6, accept all defaults and click Next
  18. In the MFC AppWizard - Step 6 of 6, accept all defaults and click Finish.
  19. In the New Project Information dialog box, click OK.
  20. 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.
  21. Using the Controls window, design form as follows:
     
    Form Design
  22. 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
  23. Save everything.
  24. 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.
  25. To associate each control with the appropriate variable, on the main menu, click View -> ClassWizard...
  26. In the MFC ClassWizard dialog box, click the Member Variables property sheet. In the Class Name combo box, select CLiqStoreDBView.
  27. In the list box, double-click IDC_ADDRESS
  28. In the Add Member Variable dialog box, click the arrow of the Member Variable Name combo box and select m_pSet->m_Address:
     
    Add Member Variable
  29. Make sure that the Variable Type is set to CString and click OK
  30. Do the same for the other controls:
     
    MFC AppWizard
  31. On the MFC ClassWizard, click OK
  32. Test the application
     
    Liquor Store - Employees Records
  33. Close the application
    As you can see, this database was created without a single line of code.

 

Adding a Record

  1. In the Workspace, click the ResourceView tab. Expand the LiqStoreDB Resource tree and expand the Menu node.
  2. Double-click IDR_MAINFRAME and add an Add menu item:
     
  3. Add a button on the toolbar and associate to it the same identifier as the Add menu item:
     
  4. Press Ctrl + W to access the ClassWizard.
  5. In the Class Name combo box, select CLiqStoreDBView and, in the Object IDs list box, select ID_RECORD_ADD
  6. In the Messages list box, double-click COMMAND
  7. Accept the suggested name of the function and click Edit Code
  8. 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();
    }
  9. Test the application and return to MSVC

Deleting a Record

  1. Add a Delete menu item:
     
  2. On the toolbar, add a button that has the same identifier as the Delete menu item:
     
  3. Implement its COMMAND function as follows:
     
    void CLiqStoreDBView::OnRecordDelete() 
    {
    	m_pSet->Delete();
    	m_pSet->MoveNext();
    	if( m_pSet->IsEOF() )
    		m_pSet->MoveLast();
    }
  4. Test the application
 

Copyright © 2003-2005 FunctionX, Inc.