Updating records in the database with ASP

We have already discussed how to create DSN and show database contents in the ASP page in our tutorial Accessing Database from ASP. Then we moved on to learn how to add records to the database in the tutorial Adding Records to the Database. We will now see how to update the records in the database.

Like we saw in the Adding Records to the Database tutorial, we have two ways to update records; one with Connection Object using SQL update command and the other with the Recordset Object.

SQL Update Statement
Allows us to update the records already present in the database.

update statement like insert statement takes 3 arguments. table_name is the name of the table you want to update, field1 is the name of the field ( column ) whose value you want updated and 'new value' is the new value that field1 will contain. Note where clause is optional, it is used to pick one specific record to update from the whole column. Following example will explain what I mean.

The above update statement will change the value of title field in the books table to 'Update Records' where author field contains 'Faiasl Khan'. Thus all titles will be converted to 'Update Records' where the author name in author field is 'Faisal Khan'.

Now since we have seen how to deal with update statement and how to update records in the database, it is time to move on to actually look at the Connection and Recordset Object on how to update the records with ASP.

We have two options to update database records with, one is with Connection Object using update SQL statement and the other one with Recordset Object. As you will see, updating records is as easy as adding or selecting records from the database and if you have read the Adding Records to the Database tutorial then you will find that the code is almost the same with just a slight to change to update the records instead of adding it.

Notice it is the SQL statement executed by Connection Object which determines the selection, addition, updating or deletion of the data in the database. Here we use a update statement to update the title field to 'Update Records' where author is 'Faisal Khan'. It is same example we used in the last page.

Via Recordset Object
Recordset Object offers another easy way to update records. Here is how we update records with Recordset Object :

As you saw we didn't use any SQL statements here. So what to use when updating records, Connection or Recordset Object ? well I personally use Recordset Object, which ever you choose is up to you.

Like we saw in the Adding Records to the Database tutorial, we have two ways to update records; one with Connection Object using SQL update command and the other with the Recordset Object.

SQL Update Statement
Allows us to update the records already present in the database.

Syntax :

	update table_name set field1 = 'new value'
		[where field2 = 'field2_value']

Example

	update books set title = 'Update Records' where author = 'Faisal Khan'

		' Before running the update statement
		Table Name = books
		Fields			Values
		author			Faisal Khan
		title			Add Records

		' After running the update statement
		Table Name = books
		Fields			Values
		author			Faisal Khan
		title			Update Records

Via Connection Object
Here is how we update records with Connection Object :

<%
	' Setting variables
	Dim con, sql_update, data_source
	data_source = myDSN
	sql_update = "update books set title = 'Update Records' _
		where author = 'Faisal Khan'"

	' Creating the Connection Object and opening the database
	Set con = Server.CreateObject("ADODB.Connection")
	con.Open data_source

	' Executing the sql update code
	con.Execute sql_update

	' Done. Now Close the connection
	con.Close
	Set con = Nothing
%>

Via Recordset Object
Recordset Object offers another easy way to update records. Here is how we update records with Recordset Object :

<%
	' Setting variables
	Dim rs, data_source
	data_source = myDSN

	' Creating Recordset Object and opening the database
	Set rs = Server.CreateObject("ADODB.Recordset")
	' Lets open books table
	rs.Open "books", data_source, 1, 2, adCmdTableDirect

	' No rs.AddNew command needed here since we are only
	' updating the records

	' Filtering records so that we get only those records
	' where author = 'Faisal Khan'
	rs.Filter = "author = 'Faisal Khan'"

	' Now updating records
	rs("title") = "Update Records"
	rs.Update

	' Done. Now Close the Connection
	rs.Close
	Set rs = Nothing
%>

As you saw we didn't use any SQL statements here. So what to use when updating records, Connection or Recordset Object ? well I personally use Recordset Object, which ever you choose is up to you.