Windows Software Development Lecture 2

1. Windows Software DevelopmentLecture 2 MIS288 Instructor – Larry Langellier

2. Where Are We? • Last Lecture • Orientation • Event Driven Programming Fundamentals • Tonight – ActiveX Data Object (ADO) • Introduction to ADO • ADO Data Control • Using ADO Programmatically • Rapid SQL Review • INSERT UPDATE DELETE SELECT • Using SQL with Visual Basic • Data bound ActiveX controls • DataList • DataCombo • DataGrid

3. Review Database Concepts • Data stored in a table • Tables consist of rows and columns • Dynamic views created using queries • Primarykey defines a unique record • Normalization eliminates data redundancy

4. Introducing ADO • New object model designed to replace DAO and RDO – neither will be supported by VB.NET • Three-tiered model • Data providers are the source of data • Spreadsheets • Text Files • Databases • Data services consume data from providers and present it to data consumers • Query and Cursor Engines • Data consumers are business applications that use data

5. ADO Conceptual View Data Consumers Microsoft Visual Basic program ADO OLE DB Data Services Cursor Engine Query Engine OLE DB Spreadsheet Jet Database Text file Others Data Providers

6. ADO Data Control • New (with VB 6.0) ActiveX control • Provides an Interface into the ADO object library • Key Concepts • ADO client establishes a connection with a provider • Commands are executed on that open connection

7. Establishing a Connection • A Connection is a link to a Data Provider • The ConnectionString property establishes a connection to a provider • Select the (Custom) property to access the Property Pages • Click the Build… Button

8. Building the Connection String • Two Part Process • Use Property Pages to: • Define the Provider • Define the database and user information needed by the provider

9. Defining the RecordSource • CommandType property defines what kind of command will be executed • CommandText property stores the command that will execute 4 Command Types: Unknown, Table, Text, and StoredProc

10. Hands-On Demo – Part 1 • Add the Component – Microsoft ADO Data Control 6.0 • Investigate the instance on frmADODC • Property Pages • General • Establishing the Provider and Connection • RecordSource • Set the Command Type and Table Name

11. Creating Bound Controls • Most controls can be bound • 2) Set the DataField to a field in the table or query referenced by the ADO Data control • 3) Set the DataFormat to automatically format field contents • 1) Set the DataSource to an instance of the ADO Data control Validate event occurs before object loses focus when CausesValidation property is True

12. Hands-On Demo – Part 2 • Creating Bound Controls • Look at DataSource and DataField property of each TextBox in frmADODC • Adding and Modifying records • Look at EOFAction property of the ADO Data control - adDoAddNew

13. ADO Data Control Events • WillChangeRecord / RecordChangeComplete • occurs just before/after the contents of a record change • Reasons: adRsnAddNew, adRsnDelete, adRsnUpdate • Status: adStatusOK, adStatusCancel • WillMoveRecord / MoveComplete • occurs just before/after the current record is repositioned • WillChangeField / FieldChangeComplete • occurs just before/after the the contents of a field change • EndOfRecordset • occurs when the user attempts to move past the last record

14. Hands-On Demo – Step 3 • Preventing run-time errors from occurring • WillChangeRecord event procedure • Update interface after moving to a new record • MoveComplete event procedure

15. The Recordset Object • ADOData control creates the Recordset object at run time • The ADO Recordset object is similar in concept to DAO Recordsets, but they have slightly different sets of properties and methods • Recordset objects can be created programmatically (as we’ll see shortly) • Recordset supports a bevy of properties and methods • Current record pointer identifies location (current record) in the recordset

16. Recordset Properties • BOF and EOF are True at beginning and end of file respectively • EditMode describes the edit status of the current record • RecordCount returns the number of records in the recordset • May be inaccurate if last record has not been located

17. Recordset Methods • AddNew adds a new record • Update updates new or edited record • Delete deletes the current record • MoveFirst, MoveNext, MovePrevious and MoveLast are navigational methods • CancelUpdate cancels add or edit • Find method locates the first record satisfying some criteria

18. Just Do It! • Work on Exercise 3.1 from the textbook (page 204) • There is an executable demo (jdi1.exe) located in the Classroom Lecture Demos – feel free to run that to get a better idea of what you’re being asked to do • We will discuss the solution after you’ve worked on problem for a while • Call me over if you have questions – don’t sit there stumped for long

19. Using ADO Programmatically • Establish a connection to a data source • Define a command • Execute the command • Manipulate the recordset

20. ADO Object Hierarchy Connection Errors Error Command Parameters Parameter Recordset Fields Field

21. DAO Object Hierarchy DBEngine object Workspacescollection DefaultWorkspaceobject Databasescollection Databaseobject TableDefscollection TableDefobject Fieldscollection Fieldobject Recordsetscollection Recordsetobject Fieldscollection Fieldobject

22. Declaring ADO objects • Unlike DAO, create an object instance • Requires ADO 2.0 Library (or greater) • Connection, Command, and Recordset are most commonly used objects Dim conCurrent As New ADODB.Connection Dim cmdCurrent As New ADODB.Command Dim rstCurrent As New ADODB.Recordset

23. Building the Connection • Connection object • Provider property contains the name of the database provider conCurrent.Provider = _ "Microsoft.Jet.OLEDB.3.51" • ConnectionString property contains the string sent to the provider to establish the connection conCurrent.ConnectionString = _ "Persist Security Info=False;" & _ "Data Source=filename” • Use the Property Pages of the ADO DataControl to determine this info Strings will vary based upon the provider

24. More Connection Properties • Mode property controls sharing and locking • CursorLocation sets the location of the cursor engine • adUseClient - Local cursor engine Supports more features than server cursors • adUseServer - Provider supplied cursor • CommandTimeout indicates the number of seconds to wait for a connection to open

25. Opening the Connection • Open method opens a connection Define Sharing • conCurrent.Mode = adShareDenyNone • conCurrennt.CursorLocation = adUseClient Use Local Cursor Open Connection after setting properties conCurrennt.Open

26. Hands-On Demo – Step 4 • Create a Connection programmatically • Go to Form_Load event procedure in frmADO • Create the instance of ADODB.Connection • Set Properties • Mode • CursorLocation • Provider • ConnectionString • Open

27. Building the Command • A Command obtains the rows to be stored in a Recordset • Command object properties • ActiveConnection - reference an Open connection • CommandType - the name of a command • CommandText - based upon the CommandType. I.e. table name or SQL statement • Examples Set cmdCurrent.ActiveConnection = pconCurrent cmdCurrent.CommandType = adCmdTable cmdCurrent.CommandText = StringValue

28. Hands-On Demo – Step 5 • Create a Command programmatically • In the Form_Load event procedure • Create the instance of ADODB.Command • Set properties • ActiveConnection • CommandType • CommandText

29. ADO Cursors • A Cursor: • Caches data on a client • Provides synchronization mechanisms • Provides tools to minimize network traffic • The ADO cursor is transparent and if defined by setting Recordset properties • CursorLocation determines whether cursor will be opened on the client (vbUseClient) or server (vbUseServer) • CursorType defines the type of cursor to open • LockType specifies optimistic or pessimistic locking • CursorLocation, CursorType, and LockType are inherited by Command or Connection • If explicitly specified for the recordset, the values are overridden

30. CursorType Property • CursorType defines the type of cursor to open • adOpenStatic - Data snapshot • adOpenForwardOnly - Forward only snapshot • adOpenDynamic - Add, Change, Delete, navigate • adOpenKeyset - records added by other users not seen in the keyset

31. LockType Property • LockType specifies optimistic or pessimistic locking • adLockReadOnly - default, read-only recordset • adLockPessimistic - record locked when editing begins and unlocked when editing ends • adLockOptimistic - locked during Update method • Example rstCurrent.LockType = adLockOptimistic rstCurrent.CursorLocation = adUseClient rstCurrent.CursorType = adKeyset mrstCurrent.Open cmdCurrent

32. The Recordset Object • A Recordset is a collection of records obtained from a Data Provider • Opening a Recordset aRecordset.Open [Source] [, ActiveConnection] where Source is Command object, SQL or table • Some Recordset properties: • RecordCount indicates number of records • AbsolutePosition indicates current record # • Referencing Fields • The following are equivalent • recordsetName(“fieldname”) • recordsetName![fieldname] • recordsetName.Fields.("fieldname").Value • recordsetName.Fields.Item("fieldname").Value • where fieldname is a string key

33. Hands-On Demo – Step 6 • Load info from the database into the interface • View the LoadCurrentRecord procedure in frmADO • This is called from numerous locations throughout the program to load records after moving to a new one – Form_Load

34. Locating Specific Records • The same methods are available as discussed earlier for the ADO DC • MoveFirst, MoveNext, MovePrevious, MoveLast, Find • Common task is to examine each record Do Until mrstCurrent.EOF ' The following two statements are ' equivalent. Debug.Print mrstCurrent![fldOrderID] Debug.Print mrstCurrent("fldOrderID] mrstCurrent.MoveNext Loop

35. Modifying a Recordset • Data Modification • AddNew adds a new blank record • Update changes a record • Delete removes a record • CancelUpdate cancels a pending add or edit

36. Hands-On Demo – Part 7 • Moving to a record programmatically • mnuFindFirst_Click • mnuFindNext_Click • mnuFindPrevious_Click • mnuFindLast_Click • Locating specific records • mnuFindByOrderID_Click • Adding Data • mnuRecordsAdd_Click • mnuRecordsUpdate_Click

37. Just Do It! • Rework the prior Just Do It exercise – this time utilizing ADO programmatically, rather than using an ADO Data Control • Don’t worry about editing records, simply provide four buttons – First, Prior, Next, Last • There is an executable demo (jdi2.exe) located in the Classroom Lecture Demos – feel free to run that to get a better idea of what you’re being asked to do • We will discuss the solution after you’ve worked on problem for a while • Call me over if you have questions – don’t sit there stumped for long

38. SQL From Visual Basic • Visual Basic / ADO supports SQL • Set the CommandType property of the Command object to adCmdText • Store the SQL statement in the CommandText property • Difficulty arises because of complex string concatenation

39. Types of SQL Statements • INSERT, UPDATE , and DELETE, are action queries and do not return records • SELECT statement returns records in a recordset • Same as the ADO recordset already discussed

40. Simple SQL syntax • SELECT { * | table.* | [table.]field1}FROMtableexpression [WHERE... ][GROUPBY... ][ORDERBY... ] • INSERTINTOtarget [(field1[, field2[, ...]])]VALUES (value1[, value2[, ...]) • Number of fields and values must match • Enclose strings in single quotes • 'string' • Enclose dates in pound signs • #date# • UPDATEtableSETnewvalueWHEREcriteria; • DELETE [table.*] FROMtableWHEREcriteria • WHERE clause syntax is the same as used with SELECT and INSERT statements

41. INSERT example Dim pstrSQL As String pstrSQL = "INSERT INTO tblDemo (fldString, fldDate," & _ "fldNumber) VALUES ('Marty Smith', #3/22/2001#," & _ "3884.33)" mcmdCurrent.CommandText = pstrSQL mcmdCurrent.Execute Enclose string in ' ' Enclose date in # #

42. UPDATE Example ' Build the SQL string. pstrSQL = "UPDATE tblPayrollDetail " & _ "SET fldGrossPay = fldHourlyWage *fldHoursWorked” ' Assume that mcmdCurrent is an instance of the ' ADO Command object. mcmdCurrent.CommandText = pstrSQL mcmdCurrent.Execute

43. DELETE Example ' Delete only those records from the table ' named tblPayroll where the field named ' fldPayDate is less than 3/10/1995 Dim pstrSQL As String pstrSQL = "DELETE * FROM tblPayroll " & _ "WHERE fldPayDate < #3/10/1995#" cmdCurrent.CommandText = pstrSQL cmdCurrent.Execute

44. Handling Database Errors • ADO supports the Errors collection and Error object • Different from the Visual Basic Err object • The Connection Object holds the Errors collection • One SQL statement can generate multiple errors (multiple Error objects in the Errors collection) • Error Object properties • Number property contains the error number • Description property contains the error description

45. Handling Database Errors (example) On Error Goto cmdSelect_Error Dim errSelect As ADODB.Error Dim pstrMessage As String ' Statements to select records Exit Sub cmdSelect_Error: For Each errSelect In pconCurrent.Errors Debug.Print errSelect.Description Next

46. Action Queries • Action queries do not return records • Insert, Update, Delete are action queries • Execute method pertaining to Command object executes an SQL statement • Example (assume mcmdCurrent is a Command object) pstrText = 'DELETE * FROM tblDemo' mcmdCurrent.CommandType = adCmdText mcmdCurrent.CommandText = pstrText mcmdCurrent.Execute

47. Transactions • Pertains to the Connection object • BeginTrans starts a transaction • CommitTrans saves a transaction • RollbackTrans performs undo on the transaction • Transactions can be nested

48. Data bound controls • DataList control is a superset of the intrinsic list box • DataCombo control is a superset of the intrinsic combo box • DataGrid is a two dimensional array (displays rows and columns in a table or query) • Controls replace DBList, DBCombo and DBGrid • Example – Ch4_c.vbp • Category on the Main Form • View -> Payroll Detail • View -> Linked Data Grids

49. DataList and DataCombo • Typical use is to display a value in the list based upon a lookup value in another table • Process is automatic. No code needs to be written • Properties and Methods • Both controls support same methods and properties • RowSource contains the name of the recordset that will be visible • ListField specifies the field in the RowSource • BoundColumn identifies second field in the ADO data control indicated by RowSource • When an item in the list is selected, BoundText becomes updated to the value in BoundColumn • DataSource contains the name of the recordset to be updated • DataField specifies a field in the DataSource

50. DataCombo/List logic flow User selects the category description 'Manager'. RowSource = adCategory ListField = fldCategoryDescription BoundColumn used to set BoundText property. BoundText = 3 DataSource = adPayrollMaster DataField in second ADO Data control (adCategoryID) is updated to the value 3. DataField = adCategoryID