Solved

Generic Web Question - SQL Access for reporting

Posted on 2001-07-10
10
212 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
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
 
LVL 19

Accepted Solution

by:
DreamMaster earned 200 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
The viewer will learn how to count occurrences of each item in an array.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now