Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 20, 2000

A Review of ASP-db - a product by USIntertech, Inc.

By Steven Wynkoop

Databases are typically a challenge to develop.  With ASP, you get to play with the new approaches - Advanced Data Objects (ADO), VBScript and the other native features of developing with ASP really make ASP and the scripting languages the tools of the trade when you're developing web-based solutions.

If you've been around the PC industry, or any portion of the computer industry for that matter, and you've been working as a programmer, you may have come to realize that programming and User Interface creation are NOT the same.  Programming is an art, UI creation is an art.  Typically people have one, but not both desires when the develop applications.  In fact, there are many times when a programmer has no business at all writing the code that will drive the UI!

But when you develop applications, the client doesn't say - "I don't care if it's incredibly ugly, just make it technologically outstanding!"  Too bad.  :)

Instead, you get a requirement to mix your skills at developing that really cool snippet of code with formatting querying the database, and more. 

That's where the promise of ASP-db comes in.  ASP-db is a database access and output formatting object that you can register on the server and then call from your ASP code.  It takes the building of tables for output, forms for editing and navigation approaches completely out of the equation.  ASP-db promises to take away the details of making the connection to the database, querying the database with your SQL statement (dynamic or otherwise) and showing the results, formatted as you wish.

So, that means that code like the following:

mySWYNK.Open Session("SWYNK_ConnectionString"),Session("SWYNK_RuntimeUserName"), Session("SWYNK_RuntimePassword")
Set mycmdTemp = Server.CreateObject("ADODB.Command")
Set myRSPartners = Server.CreateObject("ADODB.Recordset")
mycmdTemp.CommandText = strSQL
mycmdTemp.CommandType = 1
Set mycmdTemp.ActiveConnection = mySWYNK
myRSPartners.Open mycmdTemp, , 1, 3

goes away.  In addition, all that table code you've been dreading won't have to be written anymore, right?  Here's what we did to put ASP-db to the test. 

  • We re-wrote our forums with ASP-db, looking to gain significant speed enhancements from both ASP-db and our improved stored procedures, views, etc.
  • We used ASP-db to provide the default.asp pages for the forums
  • We used ASP-db in our administrative mode (actually, ASP-db made this possible in the first place)
  • We tested their support lines when we got stuck...

Designing the Forums and Building the Default Page
The forums we had before were pure ASP.  The forums loaded the messages into an internal variable array, parsed first the top level messages, then the next level, and so forth when the display was created.  It led to an initial display that was comprehensive, but slooooooow.  The problem was that the entire discussion forum was being read from the db just to show what amounted to as a table of contents. 

Our code would connect to the database, build the query string, parse the rows, build the table code on the fly, etc.  We replaced that code with the following (I've removed color setting and what-not so you can see what it really takes):

Set MyDb = Server.CreateObject("AspDB.Pro")
MyDb.dbDSN="dsn=blah;uid=blah;pwd=blah;database=blah"
MyDb.dbImageDir="/aspdb/images/"
MyDb.dbSQL = sqlquery
MyDb.dbBoolText="<center><img src=""/images/checkbox.gif""></center>," 'Show centered checkbox.gif if TRUE else blank
MyDb.dbNavigation="both" 'Show nav bars at top and the bottom
MyDb.dbNavigationIcon="std" 'Use the std "blue" navigation icon GIF files
mydb.dbGridInc=50 'Show 50 records at a time

'Use dbMagicCell to "remap" the fields to have hot-links to the getmessage program & pass the contents of the ID field.
MyDb.dbMagicCell="replies,,<a href=""getmessage.asp?rootid=#id#&id=#id#"">Replies</a>;" & _
    "subject,,<a href=""getmessage.asp?rootid=#id#&id=#id#"">#subject#</a>;" & _
    "msgfrom,,<a href=""mailto:#email#"">#msgfrom#</a>;"

MyDb.dbEditflds = "subject,msgfrom,msgdate,email,body" 'Choose which fields to show on EDIT screen
MyDb.dbNameMap = "msgFrom,Posted By;msgDate,Date;lastupdate,Last Upd;Replies,Replies?"
MyDb.aspDBPro

One important note: most of this is custom formatting – you really only need to give it the sql statement, give it the connection info and GO.

There are several neat things happening here. First, you'll notice that the properties are easy to understand and you can pretty much follow through this to see what's happening.  The sql statement is placed into sqlquery and the balance is just telling ASP-db what you need it to do.  The administrative mode just takes a command-line parameter and turns on the download and editing forms.  As messages are edited, triggers on the SQL 7 server update referring rows automatically. 

There are some REALLY cool things that ASP-db does for you - check out the dbMagicCell - you can format a field to do literally ANYTHING HTML-wise, and you can remap values, change look and feel, etc.  In our case, we use dbMagicCell to help with the customized URL that calls up messages to be read.  In addition, it adds the HREF parameters to the other fields, making them clickable on the display.

dbMagicCell made ASP-db fun - we played with all sorts of things - formatting and otherwise with this feature - it was outstanding!  I've personally not seen anything so versatile in building ASP pages and database connections.  

We also used the dbBoolText property to show the little check marks, indicating that replies had been received for a given thread.  This was great too, and worked flawlessly.  It shows the checkmark (or not) based on the value of the column, automatically.  No IF... statement, no CASE statements.  It just works. 

Technical Support
We worked extensively with ASP-db's tech support in asking questions, suggesting things for future releases (keep your eyes peeled!) and tweaking new features as our understanding grew.  I have to say that I've never experienced better tech support - when you ask a question, they not only come back with help, but also code samples, often built around your specific code.  The support was accurate, prompt and available via email or phone, something I appreciate in crunch times.

Conclusion
ASP-db is outstanding if you're building database-enabled sites.  One of the real benefits of ASP-db is that it drives its presentation from the database, not the code.   If you add new columns to the returning results set, you don't have to code anything at all - you can just re-run the page and ASP-db will see the new values, build the tables and forms to support them and you're up and running.  At worst it's a great tool to build web apps quickly and get them out in the user community.  At best it's a new tool for your development, one that will significantly decrease development time for dynamic web content, saving you the pain and agony of building tables with ASP code. 

It's available as a demonstration (row-limited) download from their web site.  If you build database-enabled web sites with ASP, I strongly urge you to download it and give it a spin.  I don't think you'll be sorry.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date