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
    (
        EmployeeNo    CHAR(6)     NOT NULL,
        DateHired     DATETIME    NULL,
        FirstName     VARCHAR(20) NULL,
        MI            CHAR(1)     NULL,
        LastName      VARCHAR(20) NOT 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
    )
    /* Data Entry */ 
    -- First Record --
    INSERT Employees VALUES('GT-882','10/05/1995',
    	'Geraldine','F','Thomas','802 Espilon Ave',
    	'Silver Spring','MD','20904','USA',12.55,
    	'(301) 524-7822','gthomas@hotmail.com',1)
    -- Second Record --
    INSERT Employees 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 VALUES('DJ-614',8/22/1996,
    	'James','D','Datts','','','DC','','USA',10.18,'',
    	'dattsj@netscape.com',1)
    -- Fourth Record --
    INSERT Employees VALUES('BC-200',4/15/1998,
    	'Catherine','','Bollack','','','',
    	'','',0,'','','')
  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 LiqStore 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:
     
    Data Source
  4. In the Create New Data Source wizard, scroll down in the list box and click SQL Server:
     
  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:
     
  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:
     
  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:
     
  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:
     
  16. Click Finish
  17. On the ODBC Microsoft SQL Server Setup dialog box, click the Test Data Source button:
     
  18. When the SQL Server ODBC Data Source Test dialog box confirms that the TESTS COMPLETED SUCCESSFULLY!, click OK:
     
  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:
     
  21. Click OK

Creating an Application

  1. Start Borland C++ Builder or Delphi with the default form
  2. On the Object Inspector, click Caption, type Four Corner Liquor Store - Employees Records and press Enter
  3. From the ADO tab of the Component Palette, double-click the ADOTable button
  4. While the ADOTable1 component is still selected on the form, on the Object Inspector, click ConnectionString and click its ellipsis button.
  5. In the Form1->ADOTable1 ConnectionString dialog box, make sure the Use Connection String radio button is selected and click the Build button:
     
  6. In the Data Link Properties dialog box, make sure the Provider property page is selected and make sure the Microsoft OLE DB Provider For ODBC Drivers is selected. Click Next
  7. In the Connection property page, make sure the Use Data Source Name radio button is selected. Click the arrow of its combo box and select LiqStore:
     
  8. Accept all defaults on the other controls and click Test Connection
     
  9. After receiving that the Test Connection Succeeded, click OK. Then click OK on the Data Link Properties dialog box:
     
  10. Click OK on the Form1->ADOTable1 ConnectionString dialog box.
  11. In the Object Inspector, click TableName and click the arrow of its combo box. Select Employees
  12. On the Component Palette, click the Data Access tab and double-click DataSource
  13. While the DataSource1 component is still selected on the form, on the Object Inspector, click DataSet and select ADOTable1 from its combo box.
  14. To design the form, on the Standard tab of the Component Palette, click Label and click on the form, then change its Caption on the Object Inspector. Add a label for all controls except for the check box
  15. From the Data Controls tab of the Component Palette, click the desired control such as DBEdit or DBCheckBox and click on the form
  16. From the Standard tab of the Component Palette, click Panel and click in the lower section of the form. Delete its caption
  17. From the Data Controls tab of the Component Palette, click DBNavigator and click the Panel1 component on the form
     
  18. Select each database component on the form and, on the Object Inspector, change its Data Source to DataSource1, then, except for the DBNavigator1 component, select the corresponding field in the DataField property.
  19. On the form, click the ADOTable1 component and, on the Object Inspector, set its Active property to true
     
  20. Test the application. You may receive a warning of "Could Not Convert Variant Of Type...". In that case click OK and press F9 to display the form.

 

 

Copyright 2003-2007 FunctionX, Inc.