Updating a SQL Database with ASP
Often, you'll want to use ASP to update a database record using SQL. Here is how it is done!
Let's say you are writing ad rotation code, and you have already done the Select of the Ad. Now you want to update the database row so that the ad's "view counter" is incremented by one. That is, if that particular ad, say for google.com, had previously been shown 99 times, you now want it to say that it has been shown 100 times.
So, after you run that previous code to get the ADID and ADCODE, you would display the ad code on the screen however you choose to use it. And then it's time to increment. You would use:
Set objCmd4 = Server.CreateObject ("ADODB.Command")
SQLText = "update ads set hit_count = hit_count + 1 where ad_id = " & AdID
objCmd4.ActiveConnection = strConnect
objCmd4.CommandType = &H0001
objCmd4.CommandText = SQLText
Set objCmd4 = Nothing
This creates the connection, writes the update statement, submits it, and then shuts down the connection. You can use this to do any sort of command to the database - from inserting records, deleting records, updating existing records, and more!
If you're using character/string values, be sure to read about Handling Apostrophes in Input Fields to make sure your input fields are ready for use in SQL.
Advanced SQL Update Command: Using Two Tables
SQL Command Listing