Basic ASP operations on databases

1. Add record

Method 1:Execute method of connection object

Set con = server. Createobject ("ADODB. Connection ")
Insertstr = "insert into Table Name (Field 1, Field 2,...) values (value1, value2,...) 'insert record SQL statement
Con. Open constr
Con. Execute (insertstr)
Con. Close
Set con = nothing
Bytes -------------------------------------------------------------------------------------------
Method 2:Addnew method of the recordset object

Set con = server. Createobject ("ADODB. Connection ")
Set rs = server. Createobject ("ADODB. recordset ")
Querystr = "select * from table name" 'query record SQL statement
Con. Open constr
Rs. Open querystr, Con, 1, 3

Rs. addnew
RS ("Field 1") = value1
RS ("Field 2") = value2
......
Rs. Update

Rs. Close
Set rs = nothing
Con. Close
Set con = nothing

 

Ii. query records

Set con = server. Createobject ("ADODB. Connection ")
Set rs = server. Createobject ("ADODB. recordset ")
Querystr = "select field from table name where..." 'query record SQL statement
Con. Open constr
Rs. Open querystr, Con, 1, 1

Do while not Rs. EOF do
'Read data
Variable 1 = RS ("Field 1 ")
Variable 2 = RS ("Field 2 ")
...
Loop

Rs. Close
Set rs = nothing
Con. Close
Set con = nothing

 3. Modify records

Method 1:Execute method of connection object

Set con = server. Createobject ("ADODB. Connection ")
Con. Open constr
Updatestr = "Update table name set field 1 = value1, Field 2 = value2 where id =..." 'update record SQL statement
Con. Execute (updatestr)
Con. Close
Set con = nothing
----------------------------------------------------------------------
Method 2:Update method of recordset object
 
Set con = server. Createobject ("ADODB. Connection ")
Set rs = server. Createobject ("ADODB. recordset ")
Querystr = "select field from table name where..." 'query record SQL statement
Con. Open constr
Rs. Open querystr, Con, 1, 3
RS ("Field 1") = value1
RS ("Field 2") = value2
...
Rs. Update

Rs. Close
Set rs = nothing
Con. Close
Set con = nothing

 

Iv. delete records

Set con = server. Createobject ("ADODB. Connection ")
Deletestr = "delete from table name where id =" & id' SQL statement for deleting records
Con. Open constr
Con. Execute (deletestr)
Con. Close
Set con = nothing