1 / 52

ActiveX Data Control

ActiveX Data Control. BICS 546. Intrinsic and ActiveX Controls. VB supports two types of controls: Intrinsic controls (command button, listbox, etc): When compile a program the code to call the intrinsic controls becomes embedded in the executable file.

berke
Download Presentation

ActiveX Data Control

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ActiveX Data Control BICS 546

  2. Intrinsic and ActiveX Controls • VB supports two types of controls: • Intrinsic controls (command button, listbox, etc): When compile a program the code to call the intrinsic controls becomes embedded in the executable file. • ActiveX controls: the code containing the methods and events for an ActiveX control is stored in a .OCX file and not stored in the executable.

  3. Intrinsic and ActiveX Controls VB Program Executable .EXE Intrinsic Controls Windows Library ActiveX Controls ActiveX .OCX

  4. ActiveX Controls ActiveX is build upon COM, a contract that defines a standard interface by which objects communicate. ActiveX controls support Property Pages. You set properties by using either property page or property window.

  5. ADO Data Control • An ActiveX control

  6. ADO Object Model • There are three main objects on which the ADO object models is built: • Connection object: make connection to the database. • Command object: run SQL against the database. • Recordset object: holds some or all of the records in database. • Note: Command and recordset object can make connections independently.

  7. Three Ways of Connecting to Databases Using ADO • Data Environment • ADO Data Control • Connecting in code using ADO Connection object.

  8. ADO Data Control • Project/Components/Microsoft ADO 6.0 • Double Click ADO to insert ADO • Right Click and select ADODC Properties • General : Source of Connection • For connection String, data providers’ property dialog are different • RecordSource: • 1 – adCmdText: SQL statement • 2 – adCmdTable: Table in the database • 4 - adCmdStoredProc

  9. ADO Properties • BOF Action, EOF Action • Command TimeOut: Amount of time in seconds the ADO control will allow for commands to be executed on the server. • ConnectionString • CursorLocation: server site, client site • CursorType • MaxRecords: 0 means no limit. • RecordSource

  10. Data-Bound Controls • Data Source • Data Field

  11. Navigating the Database • Use the navigation buttons on the data control. • Code the recordset object created by the data control. • To open the recordset: Use the Refresh method in the FormLoad event. • DataControlName.Refresh • To access the recordset’s properties and methods: • DataControlName.Recordset.Property • DataControlName.Recordset.Method

  12. BOF and EOF in a Record Set BOF Record # 1 Record # 2 Record #3 EOF

  13. Loop through a Recordset DO Until Recordset.EOF ‘Perform action on data Recordset.MoveNext Loop Do While Not Recordset.EOF ‘Perform action on data Recordset.MoveNext Loop

  14. Recordset Methods • AddNew, Update, Delete • After delete, MoveNext • MoveFirst, MoveLast, MoveNext, MovePrevious • Find: Search a recordset.

  15. Recordset Navigation Methods MoveNext, MoveLast, MovePrevious, MoveFirst ADODC1.Recordset.MoveNext IF ADODC1.Recordset.EOF THEN ADODC1.Recordset.MoveLast End IF ADODC1.Recordset.MovePrevious IF ADODC1.Recordset.BOF THEN ADODC1.Recordset.MoveFirst End IF

  16. Referring to Database Fields • DataControName.Recordset.Fields(“fieldName”) • DataControName.Recordset(“fieldName”) • DataControName.Recordset!fieldName

  17. ADO Control Recordset Update Methods Adding records: ADODC1.Recordset.AddNew (AddNew clears bound controls. The new record is added when move to the next record or added by: ADODC1.Recordset.Update (with a SaveNew button) Deleting records (after deletion, move to the next) ADODC1.Recordset.Delete ADODC1.Recordset.MoveNext IF ADODC1.Recordset.EOF THEN ADODC1.Recordset.MoveLast END IF Updating record: The database is automatically updated if the user moves to a new record. Otherwise it can be updated by: ADODC1.Recordset.Update (with a SaveChange button)

  18. Record set Find Method • Syntax: RecordsetName.Find criteria • Only accept simple comparison criteria. • Example: • Adodc1.recordset.Find “CID = ‘” & txtCID & “’” • Finding employees hired between 2/15/2000 and 5/15/2000: • Adodc1.recordset.Find “Hdate > #2/15/2000#” Then use IF statement in program to filter out those hired after 5/15/2000

  19. Find Code Example Private Sub Form_Load() Adodc1.Refresh List1.Clear Do Until Adodc1.Recordset.EOF List1.AddItem Adodc1.Recordset.Fields("cname") Adodc1.Recordset.MoveNext Loop End Sub Private Sub List1_Click() Adodc1.Recordset.MoveFirst Adodc1.Recordset.Find "CNAME = '" & List1.Text & "'" End Sub Demo

  20. Accessing Recordset in ADO Data Control and Data Environment • Data Control • Adodc1.Recordset.MoveNext • Data Environment’s command object • DataEnvironment1.rsCommand1.MoveNext

  21. Events of ADO Data Control • Error • WillChangeField, WillChangeRecord, WillChangeRecordSet, WillMove. • FieldChangeComplete, RecordChangeComplete, RecordsetChangeComplete • EndOfRecordSet • Other mouse events

  22. Parameters Passed to the Event Handlers • Error: ErrorNumber, Description, Scode, Source, etc. • WillChange: (Illustrate ADODB Enum) • adReason: adRsnAddNew, adRsnDelete, adRsnUpdate • adStatus: adStatusCancel, adStatusCantDeny,adStatusOK • etc. • ChangeComplete: adReason, pError, adStatus

  23. Data Validation with the ADO Data Control • WillChangeField, WillMove, WillChangeRecord, WillChangeRecordset • You can cancel the pending change if the adStatus is not set to adStatusCantDeny by changing the adStatus to adStatusCancel. • The ADO control must be refreshed after an event handler is fired using the Refresh method.

  24. WillChangeField Validation Example IF adStatus <> adStatusCantDeny THEN IF MsgBox(“DO you want to update” & Fields(0), vbYesNO) = vbNo THEN adStatus = adSTatusCancel END IF END IF ADODC1.Refresh

  25. WillChangeRecord Validation Example IF adStatus <> adStatusCantDeny THEN IF MsgBox(“DO you really want to update?”, vbYesNO) = vbNo THEN adStatus = adSTatusCancel END IF END IF ADODC1.Refresh

  26. Building ADO Control from Code Adodc1.ConnectionString = “provider=microsoft.jet.oledb.4.0;persist " & _ "security info=false;data source=c:\program files\microsoft visual studio\vb98\nwind.mdb" Adodc1.CommandType = adCmdText Adodc1.RecordSource = "select * from customers " Set Text1.DataSource = Adodc1 ‘Note: Use Set because DataSource is object Text1.DataField = "CustomerID“ Set Text2.DataSource = Adodc1 Text2.DataField = "CompanyName“

  27. ADO Programming Object Model Connection Errors Error Command Parameters Parameter RecordSet Fields Field

  28. Reference ADO Objects • Project/References • Microsoft Activex Data Objects 2.5 • Use the ADODB to define ADO objects • DIM myConn AS ADODB.Connection • Dim myCommand AS ADODB.Command • Dim myRecordset AS ADODB.Recordset • Use the Set and New to create objects: • Set myConn = New ADODB.Connection • Set myCommand = New ADODB.Command • Set myRecordset = New ADODB.Recordset • View/Object Browser

  29. Connection with a Connection Object • A connection object represents a unique session with a data source. • Connection string: database, OLE DB provider, password, if any, security, etc. • Use the Open method to open a connection. • Use the Execute method to execute SQL. • Manage transaction: BeginTrans, CommitTrans, rollBackTrans.

  30. Providers • MSDASQL • Microsoft OLE DB Provider for ODBC • The default provider for ADO • Microsoft.Jet.OLEDB.3.51 • Microsoft.Jet.OLEDB.4.0 • MSDAORA – For Oracle • SQLOLEDB – Microsoft SQL Server

  31. Connecting String • (ADO Data Control will build the connecting string automatically) • Containing information about database, OLE DB provider, password, if any, security, etc. • For Jet database: • Provider=Microsoft.Jet.OLEDB.4.0;Persist Security info = False;Data Source=c:\ …\Nwind.mdb • For SQL Server: • Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northeind

  32. ODBC DSN Connection • When using the ODBC provider, the connection string can be a DSN. For example: • Dim Cn as ADODB.Connection • Set Cn = New ADODB.Connection • Cn.Provider = “MSDASQL” • Cn.ConnectionString = “DSN=Sales” • Cn.Open • Or: • cn.Open ("provider=msdasql;dsn=Sales")

  33. Connection Object Methods • OPEN • cn.Open("provider=microsoft.jet.oledb.4.0;data source=c:\program files\microsoft visual studio\vb98\nwind.mdb;") • Connection string, UserID, Password, etc. • Close • Execute: • Return a recordset after executing a SQL Select statement. • Usually is used to execute SQL Insert and Delete that don’t return set of records. • Demo

  34. ADO Command Objects • Using Commands in ADO Data Controls • Using Commands in Data Environment. • Right Click Connections/Add Command • Reach the commands in a Data Environment in code. For example • DataEnvironment1.Commands(“Command1”).CommandText = “Select * from Customers” • DataEnvironment1.Commands(0).CommandText = “Select * from Customers” If Command1 is the first command in the Data Environment. • DataEnvironment1.Commands(0).Execute • The recordset created by the command has name: rs+Command name, for example, rsCommand1, and we can use recordset’s navigation methods, update methods.

  35. Command Objects Properties & Methods • Command object properties: • ActiveConnection: Current connection • CommandType: adCmdText, adCmdTable, adCmdStoredProc, etc. • CommandText: Table name if command type is adCmdTable, SQL statement if command type is adCmdText. • Command object methods: • Execute

  36. Command Object’s Parameter Object • Parameter properties • Name • “Select * From Customers Where CustomerID = ?” • For the above parameter query, the parameter name is CustomerID. • Type: Parameter data type • adDouble, adSingle, adChar, adVarChar, adVariant, etc. • Size: Paramter maximum size in bytes. • Value: Value passed to the parameter query. • Use the Append method to add the parameter to the Parameters collection.

  37. Option Explicit Private cn As Connection Private rs As Recordset Private cm As Command Private pm As Parameter Private Sub Form_Load() Set cn = New Connection cn.Open ("provider=microsoft.jet.oledb.4.0;data source=c:\program files\microsoft visual studio\vb98\nwind.mdb;") Set cm = New Command cm.CommandType = adCmdText cm.CommandText = "select * from customers where CustomerID = ?" Set pm = New Parameter pm.Name = "CustomerID" pm.Type = adVarChar pm.Value = "DRACD" pm.Size = 20 cm.Parameters.Append pm Set cm.ActiveConnection = cn Set rs = cm.Execute Text1.Text = rs.Fields(0) Text2.Text = rs.Fields(1) End Sub Parameter Query Code Example

  38. Creating ADO Recordset Objects • Connection object’s Execute method • Command object’s Execute method • ADO Data Control • Data Environment’s command object • Recordset’s Open method

  39. Creating Recordset Using Connection Option Explicit Private cn As Connection Private rs As Recordset Private Sub Form_Load() Set cn = New Connection cn.Open ("provider=msdasql;dsn=Sales") Set rs = cn.Execute("select * from customers", adCmdText) Set Text1.DataSource = rs Text1.DataField = "CustomerID" Set Text2.DataSource = rs Text2.DataField = "CompanyName“ Note: Text1 and Text2 are bound.

  40. Creating Recordset Using Command Option Explicit Private cn As Connection Private rs As Recordset Private cm As Command Private Sub Form_Load() Set cn = New Connection cn.Open ("provider=msdasql;dsn=Sales") Set cm = New Command cm.CommandType = adCmdText cm.CommandText = "select * from customers“ Set cm.ActiveConnection = cn Set rs = cm.Execute Text1.Text = rs.Fields(0) Text2.Text = rs.Fields(1) End Sub Note: Text1 and Text2 are not bound.

  41. Recordset Properties • BOF, EOF • BookMark • CursorLocation • CursorType • LockType • MarshalOption • MaxRecords • RecordCount • Sort

  42. Recordset Methods • AddNew, Update, Delete • After delete, MoveNext • Clone: Create a duplicate recordset. • Open, Close • Find: Search a recordset. • MoveFirst, MoveLast, MoveNext, MovePrevious • Requery: Re-Execute the query. • GetRows: Fetch records to an array. • GetString: Gets the recordset as a string.

  43. Recordset Open Method • RecordsetName.Open Source, ActiveConnection, CursorType, LockType, Options • Source: Command object name, an SQL statement, a table name, a stored procedure. • ActiveConnection: Connection string or DSN. Needed if no connection object defined.

  44. Recordset’s Open Method with a Connection Private Sub Form_Load() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Open "provider=msdasql;dsn=sales") rs.Open "customer", cn Text1.Text = rs.Fields!cid Text2.Text = rs.Fields!cname End Sub

  45. Recordset’s Open Method without a Connection Private Sub Form_Load() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "customer", "DSN=Sales" Text1.Text = rs.Fields!cid Text2.Text = rs.Fields!cname End Sub

  46. Cursor Type • adoOpenDynamic: Dynamic cursor lets you view changes made by other users, and allows all types of movement through the recordset. • adoOpenKeyset: Similar to Dynaset except it prevents you from seeing records that other users add. • adoOpenStatic: A static copy for genrating reports. Changes by other users will not be visible. • adoOpenForwardOnly: Same as Dynaset but only let you scroll forward.

  47. LockType • adLockReadOnly (Default): Read only • adLockPessimistic: The data provider does what is necessary to ensure successful editing of the records by locking records at the data source immediately when you start editing record’s fields. • adLockOptimistic: The data provider locks record only when you call the Update method. • adLockBatchOptimistic: Batch updates.

  48. Recordset Events • FetchProgress, FetchComplete • WillChangeField, FieldChangeComplete • WillMove, MoveComplete • WillChangeRecord, RecordChangeComplete • WillChangeRecordset, RecordsetChangeComplete • EndOfRecordSet

  49. Using SQL with ADO • SQL Select commands: • Connection object’s Execute method • Command object’s Execute method • Record object’s Open method • SQL Update commands: Insert, Delete, Update • Connection object’s Execute method • Cn.Execute “Update EmpFile Set Salary = Salary*1.05” • Command object’s Execute method sqlSTr = " Update EmpFile Set Salary = Salary*1.05” cm.CommandType = adCmdText cm.CommandText = sqlSTr cm.ActiveConnection = cn cm.Execute

  50. Recordset’s Open Method with SQL Private Sub Form_Load() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "select * from customer", "DSN=Sales" Text1.Text = rs.Fields!cid Text2.Text = rs.Fields!cname End Sub

More Related