?
Solved

Generic Web Question - SQL Access for reporting

Posted on 2001-07-10
10
Medium Priority
?
225 Views
Last Modified: 2008-03-06
I am a VB 6.0 Database programmer dog who is trying to learn some new tricks!

I have a project that I am going to be working on which I could use some guidance on. I have a couple of tables in a MS SQL database which I need to generate some reports on. These reports will vary based on the users input in requesting the report (e.g. date ranges).

Thinking that it is time to work with some new tools and tricks (at least to me), I would like to pursue this project via the WEB; setting up a page that the user could fill in for their report parameters, submitting a resulting query to the server and then formatting a page with the resultant data.

I am somewhat familiar with HTML from the standpoint that I have written a number of VB apps which in turn generate static HTML pages for some reports. I am looking for something more elegant here.

I would like someone to outline a method to do the above task; e.g. which tools to use (CGI, PERL, ASP), server side or client side processing, that sort of thing.

Will appreciate all responses and will be generous with points!

Thanks,

GTNOVOSE
0
Comment
Question by:gtnovose
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Author Comment

by:gtnovose
ID: 6269701
Some additional info:


This project will operate on company intranet only. We have MS NT 4.0 Servers with IIS. MS SQL Server 7.0. Majority of clients use MS IE 5.0 with a few using various Netscape.

Thanks again!
0
 
LVL 1

Expert Comment

by:tkuppinen
ID: 6269717
You're background in VB and the fact that you have IIS webservers already installed leads me to believe that using ASP is the way to go for you.  You can use satic HTML forms to submit to ASP which you can then use to generate your reports.

To get started look at
www.4guysfromrolla.com 
www.asp101.com
www.microsoft.com
0
 
LVL 1

Expert Comment

by:rpolocz
ID: 6269729
You'll need some sort of server side processing to dynamically build the SQL for the query.  Pick whatever technology you want for the server side (Perl, ASP, JSP, XSQL, ColdFusion, etc.)  Have them submit a form with whatever information they want, build the SQL string based on that info, do the query, and build the output page.  It's not really that hard, so long as you know what the options are, and can build the SQL to match.  Probably the biggest suggestion I have is limit what they can choose!  Give them the basic report with the ability to add or remove fields, or change their display order.  If you overdo it on the freeform stuff, you'll spend too much time coding to handle cases that no one will use.  Of course, if you need that much flexibility, try Crystal Reports.
0
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
LVL 19

Accepted Solution

by:
DreamMaster earned 800 total points
ID: 6272453
Yep...

I suggest as well for you to use ASP and can give you the basic ASP code for connecting to a SQL server database:

The easiest way is to make a DB.asp page that you can include in any of your pages when you need to..

Example DB.asp
==============
<%

DIM DB,RS

SUB DBOPEN
  Set DB = server.createobject("adodb.Connection")
  DB.Open("Provider=sqloledb;Data Source=SERVER_NAME;Initial Catalog=DATABASE_TEST;User Id=sa;Password=; ")
END SUB

SUB DBCLOSE
  SET RS=nothing
  SET DB=nothing
END SUB

SUB DBRUN(strSQL)
  SET RS=DB.Execute(strSQL)
END SUB

%>

Then whenever you create a page that uses the database include the file as follows:

SAMPLE PAGE THAT USES DB.asp
=================
<!-- #include file="includes/DB.asp" //-->
<html>
<head>
<title>This is a test page</title>
</head>
<body>
<%
  DBOPEN
  sSQL = "select * from myTable"
  DBRUN (sSQL)
  if not RS.EOF then
    do while not RS.EOF
      ' loop your recordset
      RS.movenext
    loop
   end if
   DBCLOSE
%>
</body>
</html>

Hope this gives you the basic idea...

Max.
0
 
LVL 15

Expert Comment

by:a.marsh
ID: 6524791
This question has been open for some time and is now in
need of wrapping up.

Please DO NOT accept this comment as the answer to the question, it is purely an alert to the fact that this question is still open.

It would be greatly appreciated if any of the participants could comment back here in order to give assistance in what the status of this question is and what should be done with it (delete, 0 PAQ, award points etc).

Kindest Regards

Ant
0
 
LVL 19

Expert Comment

by:DreamMaster
ID: 6524989
I think my comment was pretty helpfull....but cannot say that for sure because gtnovose didn't come back to comment on this...

Max.
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 6829166
It is time to clean this abandoned question up.  

I am putting it on a clean up list for CS.

<recommendation>
points to DreamMaster

</recommendation>

If anyone participating in the Q disagrees with the recommendation,
please leave a comment for the mods.

Cd&
0
 

Author Comment

by:gtnovose
ID: 6830086
Thanks DreamMaster and sorry to all for not accepting this sooner!

Went the VB/ASP route and found it a VERY easy solution to pursue. Was fun to learn and work with.

Thanks again (everyone)!

GTNOVOSE
0
 
LVL 19

Expert Comment

by:DreamMaster
ID: 6831259
Anytime gtnovose,

Glad you made it in here by yourself to close the question...just looks better then a force accept.. :)

Max.
0
 

Author Comment

by:gtnovose
ID: 6831837
Yeah, TG for the automatic notifications.

Unfortunately do not have much time to follow the questions here and had simply forgotten that I had an outstanding one.

Still, it is a great forum and EE has bailed me out a number of times now!

Thanks again,

GTNOVOSE
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question