ASP ADO Connection Object An implicit connection is
ASP & ADO
Connection Object • An implicit connection is created when we open a recordset without a connection object. – rs. open “Customer”, "DSN = Sales” • Opening multiple recordsets without connection object create multiple connections • Multiple Recordsets on One Connection Object (p 192). – Demo: Rec. Set. Conn. ASP
Creating Recordset Using Recordset Object • Recordset. Name. Open Source, Active. Connection, Cursor. Type, Lock. Type, Options • Source: Command object name, an SQL statement, a table name, a stored procedure. • Active. Connection: Connection string or DSN. Needed if no connection object defined. • Cursor. Type: ad. Open. Forward. Only - 0, ad. Open. Key. Set - 1, ad. Open. Dynamic - 2, ad. Open. Static - 3 • Lock. Type: ad. Lock. Read. Only - 1, ad. Lock. Pessimistic - 2, ad. Lock. Optimistic - 3, ad. Lock. Batch. Optimistic - 4
Example of Recordset Source • Table Name: – rs. open “customer”, “DSN=Sales” • SQL: – rs. open “select * from customer”, “DSN = Sales” • Stored procedure: – Rs. open “query. Customer”, “DSN=Sales” • Command Object – Rs. Open Cmd. Obj
Command Objects Properties & Methods • Command object properties: – Active. Connection: Current connection – Command. Type: ad. Cmd. Text - 1, ad. Cmd. Table 2, ad. Cmd. Stored. Proc - 4, etc. – Command. Text: Table name if command type is ad. Cmd. Table, SQL statement if command type is ad. Cmd. Text. • Command object methods: – Execute
Example of Using Command Obj <% dim conn, cmd, ad. Cmd. Table, rs set conn=server. create. Object("adodb. connection") conn. open "dsn=sales" set cmd=server. create. Object("adodb. command") cmd. Active. Connection=conn cmd. Command. Text="customer" cmd. Command. Type=2 ad. Cmd. Table=2 cmd. Command. Type=ad. Cmd. Table Set rs= Sever. Create. Object(“adodb. recordset”) Rs. open cmd %>
Creating a Recordset with a Forward Only cursor and a Read Only Lock <% Dim ad. Open. Forward. Only, ad. Load. Read. Only, RS ad. Open. Forward. Only=0 ad. Load. Read. Only=1 Set rs=server. create. Object(“adodb. recordset) Rs. open “customer”, “dsn=sales”, ad. Open. Forward. Only, ad. Lock. Read. Only
Using VB Constants, p 294 • • <body> <!--#INCLUDE FILE=“ADOVBS. INC”--> <% …
Recordset Find Method • Find : p 303 – Demo: findcust. htm
Processing Updates with ADO • Add. New: p 304 – Cursor and lock type – Demo: addcust. htm • Update • Delete
Add. New Example <body> <!--#include file="adovbs. inc"--> <% Dim RS set rs=server. createo. BJECT("adodb. recordset") rs. open "customer", "DSN=sales", ad. Open. Keyset, adlockoptimistic rs. addnew rs. fields("cid")=request. form("cid") rs. fields("cname")=request. form("cname") rs. fields("city")=request. form("city") rs. fields("rating")=request. form("rating") rs. update response. write "New customer: " & request. form("cname") & "added" %>
Update Example <% dim rs, cust. ID=request. form("cid") set rs=server. createobject("adodb. recordset") rs. open "customer", "dsn=sales", ad. Open. Keyset, adlockoptimistic rs. find "cid = ‘" & cust. ID & “’” if rs. eof then response. write "record not found" else rs. fields("city")=request. form("city") rs. fields("rating")=request. form("rating") rs. update response. write "customer: " & request. form("cname") & "updated" end if rs. close %>
Delete Example, p 320 Rs. find criteria If Rs. eof then response. write “record not found” Else rs. delete ad. Affect. Current rs. update End if
Processing Updates with SQL • INSERT INTO tablename (FIELDS) VALUES (values) • Use Connection object’s Execute method to run update commands • Demo: add. Cust. Conn. htm
Insert Into Example Dim conn set conn=server. createo. BJECT("adodb. connection") conn. open "DSN=sales" str. SQL="insert into customer (cid, cname, city, rating) values " str. SQL=str. SQL & "('" & request. form("cid") & "', '" & request. form("cname") str. SQL=str. SQL & "', '" & request. form("city") & "', '" & request. form("rating")& "')" response. write str. SQL conn. execute(str. SQL) response. write "New customer: " & request. form("cname") & "added" %>
Update Command UPDATE tablename SET field 1=value 1, field 2=value 2, … WHERE criteria
Delete Command DELETE * FROM tablename WHERE criteria
- Slides: 17