INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Tools to build ASP

Development practices when using SQL/Interfaces by onpnt
Posted: 29 Feb 04

Hi all!

I've been out for awhile and I'm not certain to the last few weeks of posting (sorry, we all need a break form TT once and while), but I noticed one as always common thing.  In the last day I have been checking all out, the same errors with SQL statements have been coming up out of the old rusty nail holes in the floor boards.

So I'll iterate a great tip a wise old PA told me ages ago and is practiced in crucial development all over this great wide earth by PA's using DB's for data sources.

First note, I have yet to find a database that does not have a <quote> SQL Analyzer</quote> built into them.  This tool is your bread and butter to alleviated SQL issues on the interface side of things.  So use them!!!

So hereÆs the way to do that to improve productivity of your interface development, relief of debugging sessions and relief of your headaches.  We will run through an example here using two of the most common.  
SQL Server  
M$ Access

Before you ever insert a SQL statement into your interface (ASP script, PHP script, JSP script or whatever) you should test it in SQL Server's "SQL Analyzer" or M$ Access's "SQL View"

Example situation: You have to join three tables on a unique ID of 1
Table names are projectInformation, projectTimes, projectDetail
A syntax error prone task when attempts are made to build in your scripts form scratch with little of even moderate SQL skills by developers.

All right, with that said, do you start hacking away in ASP using vbscript or jscript?  NO!!!

HereÆs what you should do

SQL Server:
1) Open Enterprise Manager
2) Expand to the database and select it
3) Top menu click Tools
4) Scroll and select "SQL Query Analyzer"

Analyzer opens....
Enter your statement.  In our case something like this

CODE


SELECT *
FROM projectInformation

INNER JOIN projectTimes ON
    projectInformation.projectLeaderID = projectTimes.projectID

INNER JOIN projectDetail ON
    projectTimes.timeID = projectDetail.timeID

WHERE projectInformation.projectLeaderID = 1;

Hit F5 and you will either get your view or get a syntax error (fix as needed)
Now just add in your conversions to a string that can be read by the server
e.g.:

CODE



SQLText = "SELECT * " &_
"FROM projectInformation " &_
"INNER JOIN projectTimes ON " &_
"projectInformation.projectLeaderID = projectTimes.projectID " &_
"INNER JOIN projectDetail ON " &_
"projectTimes.timeID = projectDetail.timeID " &_
"WHERE projectInformation.projectLeaderID = 1"

THEN before doing any hits to the DB do a

CODE


Response.Write SQLText
Response.End

Copy/Paste the output in the browser window in SQL Analyzer and hit F5.  
If it ran you are set and won't go through he** debugging typos and syntax mistakes
sense you spend all your time thinking of what your SQL needs to be in a SQL syntax
related tool and not the server interpreting ASP scripts.

And as I said we'll do M$ Access
1) Open the Database
2) Go to Queries
3) Click New
4) Choose Design View
5) Add the three tables (in our examples case)
6) Right click on the title "Query1"
7) Scroll to "SQL View" and select it

Two choices here.  Select all the SQL generated to this point and delete it
or adjust it for the join.  (BTW: this is how you do full procedural SQL statement in Access) ;)

So you enter your statement with the obvious changes to write it in JetSQL over TSQL


CODE


SELECT *
FROM projectInformation
INNER JOIN (projectTimes
INNER JOIN projectDetail ON projectTimes.timeID = projectDetail.timeID)
ON projectInformation.projectLeaderID = projectTimes.projectID
WHERE projectInformation.projectLeaderID = 1;


Hit the big red ! In the tool bar.
It runs?!? Now do your syntax changes just as prior
Response.Write It
Response.End It

copy/paste back to your SQL View and hit that big red ! In the tool bar again.
It runs you're good to go.

So what do you get out of this?
1) All questions that are SQL related go to the SQL related forums
e.g.:
Microsoft SQL Server: Programming  Forum183
Microsoft: Access Queries and JET SQL  Forum701
NCR: Teradata  Forum328
ANSI_SQL  Forum220
Oracle has twenty of them for each version errr.. So I'll post the latest
Oracle: Oracle release - 9i Forum759

2) no more thinking of SQL in your interfaces, but thinking of what you should be thinking
of at this stage of the sdlc; THE INTERFACE and simple syntax concerns with the language of choice

3) The most important!!!! No more headaches.

Good luck and happy programming from the little people

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close