Database Management Utility for MySQL in ASP

[Gary Williams]

Hi All,

I am looking for/trying to write a utility, in ASP, that will work with a MySQL database (containing several tables) to display all records in a chosen table by automatically writing the html.

In my tables, row #1 holds the labels to be displayed for each field in any report requested. So if field #1 has the name "fFirst_Name", its' corresponding label (which is displayed at the top of the respective column in the report) maybe "First Name" and so on for all fields. It's the row #1 values that are used for the column heading of the html table. That makes for a much nicer table to read.

To achieve this using a looping programme is reasonable straight forward but I have to write one for each table in the database. What I want to achieve is to go a bit further by developing a utility that will::

1. Determine the number of fields in the selected table and then create the html to display the records.

2. Add an extra 2 columns to the table, one containing an "Edit" button that calls up that record in its' own table for editing, and the second column contains a "Delete" button.

3. Automatically create and "Add Record" feature for the selected table.

Has anyone come across such a utility?

Regards

Gary

[Old Pedant]

Pardon me for saying this, but I think you have a bad design.

In my tables, row #1 holds the labels to be displayed for each field in any report requested.

That *IMPLIES* that *ALL* you columns must be TEXT columns!!! Because only a textual column could possibly hold a "label to be displayed".

And this means that you can't have columns that hold dates or times or currency or latitudes or longitudes or ages or or or...

Oh, sure, you *CAN* store a date or number into a text column. But you can't then easily do such things as COMPARE dates or number (because dates-as-strings and numbers-as-strings do *NOT* compare correctly!!), and that means you can't even use ORDER BY properly.

You have, effectively, either severely limited the usefulness of your database or you have forced yourself to write much more complex than otherwise necessary SQL queries in order to compensate for this all-textual choice of yours.

If you must adopt a self-naming scheme such as this, then just do it by way of the actual field names themselves! MySQL allows virtually ANY name for a field name, though if the field name contains spaces or odd characters or matches a keyword you have to then enclose it in `...` (back-tick marks) in your SQL queries. Which can sometimes be irritating, but at least there is zero performance penalty for it.

And, finally, if you do that, then you can dump ANY table as simply as this in ASP code:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=171

So I plead with you to rethink what you are doing.

p.s.: You know, if you moved to ASP.NET, you could use a DataGrid to do ALL that you have asked for without writing any code at all. Yes, including the Add/Edit/Delete stuff.

But including the Add/Edit/Delete isn't that hard in old fashioned ASP, though the MySQL driver doesn't allow ADO editing so it's a tad more work than with Access or SQL Server.

[Gary Williams]

Hi Old Pendant,

Yes, you are correct - Bad Design:( I realised what I had described when driving down the M1 this afternoon. I'll reword my request more clearly and post in a bit.

Regards

Gary