If you’re writing ASP code, it’s common to get stuck with issues when you are trying to do a database select, insert or update based on a form. Here is how to troubleshoot the issue.
Syntax Check
Always test first with a test string of insert SQL to guarantee your connections are working properly. If you don’t even know how SQL syntax works, you are going to be in trouble from the very beginning. Did you do a test insert with your normal database tool, to guarantee the syntax is correct? I have a collection of SQL Syntax Online and I highly recommend taking a course in database structure if you’ve never done this before. Designing database tables properly can have an INCREDIBLE impact both on how quickly your site runs, as well as helping you not have serious database integrity issues.
So start by using whatever database tool you use, and running a similar SQL syntax through it. Make sure it actually does what you want it to do.
Database Connection Check
OK, you have a SQL syntax that works properly. Next, you need to check the connection between your ASP and the database. Comment out whatever SQL line you are currently using as your execute string. Add in your test SQL – that you have proven will work – in your ASP script. Run the ASP script. Does the database actually receive that line and process it? If not, then something is wrong in the way you are connecting to your database. Check the username, password, database rights, all the usual suspects. If your ASP script isn’t talking to your database, it really doesn’t matter what you’re telling it to do. The command won’t be done.
Creation of Dynamic SQL
In many cases with ASP, you aren’t just executing the exact same command over and over again. You are using a form, letting a user choose an item or two (say an author name) and then executing your command based on that choice. So now you need to verify that the SQL syntax you are building on the fly is actually valid.
Intead of executing the SQL syntax build based on the user’s input, response.write it out to your screen. Then cut and paste that syntax and test it from your database untility that you use to manage your database. That will help to show you what is wrong with it, and let you fix it. If you write poor syntax, the database can’t execute it. I can’t emphasize enough how CRITICAL it is that you really understand and construct SQL syntax properly. One mis-formed SQL syntax could literally wipe out your entire database of content, with one line of code. A database is an incredibly powerful system – but it is completely at the mercy of what you type. If you UPDATE without understanding what you are doing, you could overwrite all content in a table, POOF.
I have a lot of content on this site to help you learn, but I also highly recommend taking courses in database design at your local college – they are WELL worth it!