Techadvice.com

Main,
Tech Info,
Glossary,
Companies,
Hardware
.
Software.
Windows 95, 98, ME, XP,
NT4, 2000, Errors,
Applications
,
Search products ,
Search FAQs.
Shop
Companies,
Products
,


IIS 3.0 Using SQL/IDC with Front Page 97 | Applications | Create ODBC DSN |


Please Note : this tutorial will be deleted shortly

Tutorial and examples on Connecting FrontPage97 web pages to SQL server 6.5

Beginner Examples: "Search All using IDC/Access" Example, "Search Some using IDC/Access" Example

In this tutorial we will build an IDC connected set of web pages to SQL 6.5 to create update and modify a Job logging function. All of the needed files are accessible from this tutorial page. Also since these examples are real live pages please try not to modify any of them as a courtesy to other users.

The completed set of web pages will be able to 1) add a new job listing, 2) display the current job listings in the database and allow you to change the information of any one job and 3) display the job listings and allow you to selectively delete them.

This tutorial is setup to operate on a Windows NT 4.0 server with FrontPage 97, but the basic ideas can be used in other configurations.

Overview

Note: This whole section is made so that you can save the pages directly into an Front Page 97 web folder

The pages will show as HTML code for HTX type files, but if you save them to your Front page web they will view properly.

The following pages are created in the home page of the web that users would access. All the pages are viewable by clicking the file name's hyperlink. For example clicking on the T_empl_index.htm below would show you that page. You should also be able to use the "File" "Save As" in Norton Navigator to save these pages to your computer if you would like to use them along with the example. And of course remember when viewing the examples to press the Back button on your BROWSER to return to the page you started from.

  1. The Starting page, pressing the link takes you to the empl_new.htm page
  2.  The second page is the employment page which points to the functions
  3. submitNewJob.htm ( have not entered tutorial for this item yet )

The HTX web pages were created, saved and moved to the "IDC_folder" folder ( see below ) keeping them all together. Remember this folder was modified to allow execution of jobs.

  1. db_deletedRecordConfirmScreen.htx ( deletes the record of the selected item )
  2. db_InsertedRecordConfirmScreen.htx ( Adds a new record after you type in the information )
  3. editAJob.htx ( shows all fields and allows you to change the information contained in the fields )
  4. tsjobsdetails.htx ( displays complete information of all fields for a single record you select )
  5. tsjobsdisplay.htx (displays all open jobs is a spreadsheet format )

The above 8 files (pages) will all be used in this tutorial. Each page can be created inside the Front Page97 Editor.

On the "empl_new.htmt" page there are two hyperlinks

1) The first "Display / Delete / Modify Job Entries" will call the IDC file "tsJobsDBconnector.idc" when the link is pressed. The IDC file will execute an SQL stored procedure "Get All Active Jobs" and place the results in a "database results template" file called "tsJobsDisplay.HTX".

The file "tsJobsDisplayDB.HTX" has a table within the database detail section, and in each column is a database column field. Several of the fields have hyperlinks to allow for "deleting" or "modifying" the row clicked as described in a), b) and c) below.

a) Viewing a single job:
The hyperlink calls the IDC file "tsjobsDBconnector1.idc?JobCount=<%JobCount%>" passing the record ID to the variable JobCount. The IDC in turn performs a SQL statement "select * from TechSrcDB.dbo.Jobs where JobCount=convert( int,'%JobCount%')" which selects only the matching JobCount (ID) row in the table.The result is displayed in the database template file "tsjobsdetails.htx"

b) EditJob
The hyperlink "Edit Job" calls the IDC file "TS_EditJobRecord.idc?JobCount=<%JobCount%>" passing to it the variable JobCount which is assigned the JobCount value of the current table record. The IDC in turn executes a SQL query "select * from .Jobs where JobCount=convert(int, '%JobCount%') order by JobCount" which retrieves only the matching record, and passes on the value to the result database HTX template "editAJob.htx".

On that page all fields of the selected record are displayed and can be edited. Finally pressing the <Submit> button on the bottom of the page will execute the IDC file "TS_SubmitEditedRecord.idc" which will submit the changes to SQL server and update the record.

The IDC file executes a stored procedure in SQL server ( exec TS_EditJobRecord %JobCount%, '%JobTitle%', '%JobDescription%', '%JobRequirements%', '%JobActive%', '%JobType%', '%JobLocation%', '%JobExperience%', '%JobDegreeNeeded%', '%JobCompensation%', '%JobStartDate%', '%JobDuration%' ) which passes all the field values and updates the record. Finally the IDC opens the page database results HTX page "db_InsertedRecordConfirmScreen.htx" which confirms the update.

c) Delete this Job:
Pressing "Delete this Job" runs the IDC file "MarkJobDeleted.idc?JobIDlu=<%JobCount%>" which executes the SQL stored procedure "exec TS_DeleteJob %JobIDlu%" and passes the JobCount to SQL in the variable JobIDlu. The record is found and deleted, then the database template file "db_deletedRecordConfirmScreen.htx" is displayed showing a confirmation.

2) The second is submitNewJob.htm...(still working on this item)

 

Details

There are several preparations you must take.

1) Create a database table in SQL server

Start the SQL server Enterprise Manager

Create a Database called TechSrcDB

Create a table called Jobs using the following parameters

CREATE TABLE dbo.jobs (
JobID varchar (10) NOT NULL ,
JobTitle varchar (50) NULL ,
JobDescription varchar (255) NULL ,
JobRequirements varchar (255) NULL ,
JobActive char (1) NULL ,
JobType varchar (30) NULL ,
JobLocation varchar (50) NULL ,
JobExperience varchar (100) NULL ,
JobDegreeNeeded varchar (25) NULL ,
JobCompensation varchar (25) NULL ,
JobStartDate varchar (25) NULL ,
JobDuration varchar (255) NULL ,
JobCount int IDENTITY (1, 1) NOT NULL ,
JobDate datetime NOT NULL

Create the following stored procedure

a) for Adding records:

CREATE PROCEDURE TS_AddJobRecord

@JobTitle_ varchar(50),
@JobDescription_ varchar(255),
@JobRequirements_ varchar(255),
@JobActive_ char(1),
@JobType_ varchar(30),
@JobLocation_ varchar(50),
@JobExperience_ varchar(100),
@JobDegreeNeeded_ varchar(25),
@JobCompensation_ varchar(25),
@JobStartDate_ varchar(25),
@JobDuration_ varchar(255)

b) For deleting records

CREATE PROCEDURE TS_DeleteJob
@name1 int

AS

update jobs set
JobActive = "N"
where JobCount = @name1

c) For Editing records

CREATE PROCEDURE TS_EditJobRecord

@JobCount_ int,
@JobTitle_ varchar(50),
@JobDescription_ varchar(255),
@JobRequirements_ varchar(255),
@JobActive_ char(1),
@JobType_ varchar(30),
@JobLocation_ varchar(50),
@JobExperience_ varchar(100),
@JobDegreeNeeded_ varchar(25),
@JobCompensation_ varchar(25),
@JobStartDate_ varchar(25),
@JobDuration_ varchar(255)

d) For retrieving all active records

CREATE PROCEDURE TS_GetAllActiveJobs AS

Select JobCount, JobID = 'TS'+ right(convert(char, datepart(yy,JobDate)),2) + right('000'+ convert(varchar(10), JobCount),4), JobTitle, JobDescription, JobRequirements, JobActive, JobType, JobLocation, JobExperience, JobDegreeNeeded, JobCompensation, JobStartDate, JobDuration from Jobs
where JobActive in('y','Y')
order by JobID
GO

e) For getting a single record that the hyperlink specifies

CREATE PROCEDURE TS_GetOneActiveJob AS

Select JobCount, JobID = 'TS'+ right(convert(char, datepart(yy,JobDate)),2) + right('000'+ convert(varchar(10), JobCount),4), JobTitle, JobDescription, JobRequirements, JobActive, JobType, JobLocation, JobExperience, JobDegreeNeeded, JobCompensation, JobStartDate, JobDuration from Jobs
where JobActive in('y','Y')

These procedures will be called by IDC from the web page.

2) Create a DSN

Next create an ODBC System DSN in Windows NT which will be used later on for the IDC connection.

 

3) Open Front Page 97

Next load Front Page and create a new web ( or if you have one that you can use go ahead and open it).

Creating a new web would be much better since it will be easy to follow the work.

Go to the Front Page Explorer and create a new folder.

This is done by selecting File, New, Folder

After you click on Folder the folder will be created and placed on the right half of the page.

Click the mouse on top of "New Folder" and delete the words . and replace them with "IDC_folder" and press the enter key.

The message "renaming the folder" will appear and the folder will show up on the left side of the screen.

Note: The above instructions are very basic and will not be repeated further along the tutorial.

Next (and this is a very critical step) we need to give files inside the folder the ability to execute programs.

On the left half of the screen locate and right click on the "IDC_folder" folder

 

When the Properties box pops up select properties with the mouse

The "IDC Folder Properties" window will be displayed.

With the mouse click on the "Allow Scripts or programs to be run" check box so the check mark shows and click the OK button .

4) Open the Front Page Editor

Now the web pages needed for this tutorial will be built. Most of the work is done in the Front Page Editor, and the instructions will assume you know how to create and save new pages.

Create the pages shown in the overview above. The HTX pages are normally created prior to making the IDC pages. The Home and Employment pages are created next and finally the links and the IDC pages.

It's not easy to explain because its a complicated process. All of the needed pages, links, HTX and IDC files are provided through the hyperlinks in the overview section and can be viewed and copied

There is a funny connection (almost a bug ) that I found in using DSN files. It seems at times when a server name is included in the DSN file, it does not work. Subsequently going to the control panes and retyping the server name (or selecting Local) seems to fix the problem.

 

 


 

You are at the www.techadvice.com site which is not associated with the company or products shown on this page.
Contact Us,
Disclaimer
Advertisers
, ExoticRecipes
 

---