Inserting Table into Db & Login System Question (Newbie Help)

Hi all,

I've been struggling with this for a few months and would be grateful if someone could help me. I inherited a Coldfusion application at work but I'm a complete newbie in Coldfusion and SQL - I've actually always worked on the design side of things rather than programming, but with cost-cutting etc., it got dumped on me.

The application is only accessible to our clients and staff. Registration is done via a JMail form with form data emailed to me so that I can scan the registrants and verify that they really are clients, before issuing them with a username and password. Currently, the login process is done via a token system that our webhost provides - so it's entirely outside of the application. My bosses now want to track some statistics on logins (mainly to try and see if the passwords are being abused by an unsual spike in logins) and also to restrict some content so that only staff can see it. I thought the best way to handle this would be to rewrite the login so that it's within the Coldfusion application.

I've skimmed WACK and searched out a lot of free Coldfusion tutorial websites on the web so I think I'm familiar with the fundamentals. The way I thought I'd go about this would be to:

1) insert a table in the SQL database for users

2) write an "add user" page that only I can access, to insert the user data into the database (I'm basing it off the tutorial at http://tutorial1.easycfm.com/ )

3) write a login system that can track users ( http://webmonkey.wired.com/webmonkey/00/48/index2a.html )

4) write a "display statistics" page to view the number of logins for each user, etc. ( http://tutorial2.easycfm.com/ )

I think I can use the tutorials to do most of the above, but I'm stuck on how I can insert the user table into the SQL database. The application is running on a managed webhosting plan at WebCentral. I thought about downloading the SQL database, inserting a table via MS Access, then restoring the changed database. I downloaded the evaluation version of SQL Enterprise Manager, but I can't figure out how to connect and download the remote database to my local computer. I also want a copy of the database to upload on another account so that I can play around with my plan before running it on the live site, which is why I'm going with the "downloading a copy of the database" idea rather than running a query to insert the table directly.

Is there a tutorial that I can be directed to, or can anyone advise me on how to go about it? Or on a better way to do it? I would also *really* appreciate it if a Coldfusion/SQL expert can run an eye over my plan and advise if it's workable or if I'm just going to screw everything up. WebCentral charges a few hundred dollars to retrieve the tape backup.

Thanks for any help,
Vanessa

Vanessa,

First, you will need to define a DB connection from Cold Fusion administrator. If someone else is hosting your CF, then you will need to create your tables and upload to their server. Once you have completed this task, they will need to create a DSN connection. So, that whatever application you create, Cold Fusion will know where to look for that specific DB.

Another thing about the user tables. Once you create a table, you will be updating/deleting/inserting records. If it's simply a login interface, you should be able to it with MSAccess. Another important thing is some hosting companies charge extra if you need to use SQL Server 2000.

There is a book by Ben Forta and Nate Weiss called Macromedia Cold Fusion MX - Web Application Construction Kit 5.edition.
you may want to take a look at chapter 3 - chapter 6. borrow it from barnes and nobles as i do...

hope it helps

Thanks for the info, FALCONSEYE. I did work through a lot of the earlier chapters from the Web Application Construction Kit but I guess I didn't read closely enough. I'll take a look at it again.

But from what I did with WACK, I thought it was useful for learning the programming language and developing an application from scratch - my problem is that I'm working on an application that was developed by someone else. (Development of the application was outsourced but when it was finished, my bosses were hit with a bill that ran a few thousand dollars over budget so they pitched a fit and turned all future development over to me.) The database is already connected and has been populated and users are already accessing the application. It's on a hosted plan at WebCentral - and you're right - they charge extra for use of the SQL server.

That's why my plan was to download the populated database from WebCentral, insert the table, then restore it. I also want to install the database on a personal website so that I can make any mistakes on that before using the company's website. I downloaded the evaluation version of the SQL Enterprise Manager, but I'm completely lost on how to connect and download the remote database. Should I be doing it via the Coldfusion administrator instead?

Thanks for your patience.

If the database is access you should simply be able to FTP to the server, download the file, add a table, and then re-upload the file.

As an alternative, you could use DDL statements to create a table and not bother with downloading or uploading anything. Do a Google search for "SQL DDL" or "CREATE TABLE".

It's an SQL database. I'm such a noob to programming that I'm not even sure if I can edit the db with MS Access - that's what our IT officer told me but I don't think he knows what he's talking. I can access the SQL database via Query Analyser in MS SQL Server but am looking for instructions on how to connect via Enterprise Manager.

I'm using the evaluation version, so I don't know if it's limited in connecting to remote databases.

I would prefer to download the database so that I can install it on my personal account on another webhost (if that's possible?) to test out the rest of my plan on adding the login system. I'd like to play around with that account first rather than (inevitably) screwing up the live application on the company's webhost.

Thanks.

You can use the SQL Server management console to create a backup of the SQL database, and then restore it on your local development machine. You'll have to modify the paths when you restore because by default it will try to restore to the same directory that it was backed up from.