SQL Recommendations

Question

SQL Recommendations

Douceur

Grasshopper

Points: 11
More actions
December 22, 2003 at 9:43 am

#83992

New to SQL
Looking for recommendations on creating GUI ASP pages to look at SQL data.
I am using FrontPage to access SQL queries from an ASP page. Creating the form isn't that flexible. Using Crystal Reports, but the reports are flat and not dynamic.
Working on getting VisualStudio, which I think will solve this.
- - - - -
What might one use to pull info out of SQL via an interactive GUI form?
Thanks so much!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

I use ASP to query the db and pull stuff out. Visual Studio will help here. I'm sure there are some commercial utilites (http://www.mylittletools.net/scripts/en/home/) and others that can help.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 2

Hi,

I do quite a bit of this sort of thing.

All I do is keep it very simple.

I design all my forms from scratch using ASP and HTML, using a basic text editor such as NoteTab. Once you have done one bit of code it can be reused for various forms.

You can retrieve/post data to and from the database using stored procedures with parameters. That way the information is returned to the page very quickly.

You are right some apps aren't very flexible which is why I do my own. If you don't want to get into raw coding Visual Studio is more than capable (I believe) in doing this.

Merry Xmas

Graeme

Ferguson SSCertifiable Points: 5837 More actions · Answer 3

As Graeme said, keep it simple. Front page may not be the best way, if you aren't ready for Dot.Net (and the hurdle to get into it is higher than might be worth it), and have an IIS platform to work from, ASP can be written by hand and work quite nicely with SQL Server. We did our first draft Intranet in ASP and it was about 95% to deliver SQL Content.

If you're building for a huge deployment delve into all the complexities, but if this is a smallish deployment then just little simple pages that open a connection, build a SQL Statement from selection criteria OR pass parameters to a stored procedure from selection criteria, then build a table from the results.

There are also a pile of samples for simple objects that will take an ADO recordset and build a Table from the results for you. But as a first cut we found it simpler to just do a FOR statement in ASP and RESPONSE.WRITE the results in our first versions, this also lets you do dynamic formatting.

After doing a few dozen such pages drop back and build some infrastructure around it and/or consider ASP.Net.

swjohnson Hall of Fame Points: 3254 More actions · Answer 4

We do this quite a bit and have built a bit of a foundation around it like include files with the functions to build tables and such.

Several programmers here use different tools from Visual Studio to Homesite to Notepad to build the ASP pages. We even have one kid who uses a DOS text editor.

A couple of things to keep in mind as you build this though...

1) try to use stored procedures where possible. It will help with performance on your server--ASP can just pass it the variables from your form and the stored procedure will return the necessary data. This will also ensure your security and permissions.

2) be cautious of sql injections (especially if you open this up to a larger audience)

3) as a safety, make sure to have some sort of logging/authentication. This way you know who did what and when via the ASP interface.

4) not sure if you are at this stage yet or not but take a look at Reporting Services (now in Beta) from MS. Good report and has some dynamic capability. But you will have to be pretty good with ASP and .Net.

Good luck!

Shawn

Accepting all invites

bnordberg SSCertifiable Points: 5379 More actions · Answer 5

I to struggled with creating a flexible reporting format for the web. I moved to the datawarehouse model and most users love it. I have an example here http://www.utcodes.org/queryabledata/cube/default.htm I no longer work there and they trashed my tutorial, but the ASP query engine still works. Essentially drag and drop to either a column or row.

I built this using Analysis Services and the Thin web client on the SQL 2000 resource kit. If your data are in good shape, you can build the warehouse very quickly, then copy the files for the thin web client to a virtual web, modify the connection string and whala.

bigdmoore SSChasing Mays Points: 630 More actions · Answer 6

SQL Reporting Services looks very interesting. Here's a good article and link to Microsoft's site. Although I haven't used it yet, it appears to be very user-friendly and the report designer is similar to MS Access.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=40529

http://www.microsoft.com/sql/reporting/default.asp

It will be free as an add-in to existing SQL 2000 licenses. However you will need to have Visual Studio .NET 2003 in order to use the report designer.