Advanced data handling techniques: updating records ADO object model: appendix D page 303

1
Advanced data handling techniques updating
records ADO object model appendix D page 303

• Knud S. Vemming

2
Modifying data
3
Modifying data

• 3 types of action commands
• UPDATE, INSERT and DELETE
• Use the Command object or the Recordset object
  or the Connection object

4
Modifying data

• Updating existing data (where the cursor is)
• objRS(City).Value Vejle
• objRS.Update
• Adding data

objRS.Open listing", strConnect, adOpenStatic,
adLockOptimistic, adCmdTable p.
213,214 objRS.MoveLast intIDForNewRecord
objRS(Propid") 1 make a note of its MovieID
value 1 objRS.AddNew ' add a new
empty record objRS("Propid") intIDForNewRecord o
bjRS(City") Aalborg objRS(Bedrooms")
4" objRS(Sqfoot") 1200" objRS(Price")
100000" objRS.Update
5
Modifying data

• Changes are only made to the recordset which
  means that the datastore isnt updated before
  Update or Movefirst are used

6
Modifying data Deleting records

• Delete method from Recordset object
• objRS.Delete
• Deletes the record that the cursor points to.
• The record is deleted immidiately except if in
  batch update mode.
• objRS.find Propid10
• if not objRS.EOF then objRS.delete

7
Structured Query Language - SQL

• Universal language for programming databases
• Declarative language
• We tell the computer what we want and the
  computer decides the best way to do it.
• Java , C are procedural languages the the
  computer exactly what to do.
• 4 main types of commands
• SELECT, INSERT, UPDATE and DELETE

8
Modifying data SQL

• INSERT and DELETE
• Set objConn Server.CreateObject("ADODB.Connectio
  n")
• Set objRS Server.CreateObject("ADODB.Recordset")
  
• Man kunne skrive
• strSQLINSERT INTO listing (Propid, City,
  Bedroom, Sqfoot, Price ) VALUES (11, Odense,
  3, 1234, 120000)
• Eller man kunne skrive
• strSQLDELETE FROM listing WHERE City LIKE
  Daytona Beach
• objConn.execute(strSQL,Rowsaffected)
• response.write kommandoen har berørt
  Rowsaffected antal records

9
Modifying dataSQL

• SELECT
• formdatarequest.form(T1)
• Set objConn Server.CreateObject("ADODB.Connectio
  n")
• Set objRS Server.CreateObject("ADODB.Recordset")
  
• strSQLSELECT FROM listing WHERE formdata
  LIKE City
• objRSobjConn.execute(strSQL,Rowsaffected)
• response.write kommandoen har hentet
  Rowsaffected antal records
• ... udskriv objRS med RecToTable()
• The connection object returns a recordset object

10
Modifying data SQL

• UPDATE
• formdatarequest.form(T1)
• Set objConn Server.CreateObject("ADODB.Connectio
  n")
• SQLstrUPDATE listing set CityAalborg WHERE
  City formdata
• objRSobjConn.execute(strSQL,Rowsaffected)
• response.write kommandoen har berørt
  Rowsaffected antal records
• Remember WHERE!
• ( if not -gt all records will be affected)

11
The command object

• Executing commands against the data store (here
  selecting)
• objRS.open listing, strConnect,
  adOpenForwardOnly, adLockReadOnly, adCmdTable
• objRS.open SELECT FROM listing
• Other commands like insert, update and delete a
  record.
• Command object allows us to run more complex
  queries, from i.e stored procedures

12
The command object

• Specifying
• objCommand.ActiveConnection strConnect A
  string or connection object
• objCommand.CommandText SELECT FROM listing
• objCommand.CommandType adCmdTable command
  type
• And executing
• Set objRS objCommand.Execute
• Or
• objRS.Open objCommand