Visual Basic Q&A

Symptoms
ActiveX Data Objects (ADO) offers several ways to open both the Connection and Recordset objects. This article presents sample code for several common techniques for each object.
Resolution
There are several ways to open a Connection Object within ADO:
By Setting the ConnectionString property to a valid Connect string and then calling the Open() method. This connection string is provider- dependent.By passing a valid Connect string to the first argument of the Open() method.By passing a valid Command object into the first argument of a Recordset’s Open method.By passing the ODBC Data source name and optionally user-id and password to the Connection Object’s Open() method. There are three ways to open a Recordset Object within ADO:
By opening the Recordset off the Connection.Execute() method.By opening the Recordset off the Command.Execute() method.By opening the Recordset object without a Connection or Command object, and passing an valid Connect string to the second argument of the Recordset.Open() method. This code assumes that Nwind.mdb is installed with Visual Basic, and is located in the C:\Program Files\DevStudio\VB directory:

Option ExplicitPrivate Sub cmdOpen_Click()Dim Conn1 As New adodb.ConnectionDim Cmd1 As New adodb.CommandDim Errs1 As ErrorsDim Rs1 As New adodb.RecordsetDim i As IntegerDim AccessConnect As String’ Error Handling VariablesDim errLoop As ErrorDim strTmp As StringAccessConnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=nwind.mdb;” & _”DefaultDir=C:\program files\devstudio\vb;” & _”Uid=Admin;Pwd=;”‘—————————’ Connection Object Methods’—————————On Error GoTo AdoError’ Full Error Handling which traverses’ Connection object’ Connection Open method #1:Open via ConnectionString PropertyConn1.ConnectionString = AccessConnectConn1.OpenConn1.CloseConn1.ConnectionString = “”‘ Connection Open method #2:Open(“[ODBC Connect String]“,”",”")Conn1.Open AccessConnectConn1.Close’ Connection Open method #3:Open(“DSN”,”Uid”,”Pwd”)Conn1.Open “Driver={Microsoft Access Driver (*.mdb)};” & _”DBQ=nwind.mdb;” & _”DefaultDir=C:\program files\devstudio\vb;” & _”Uid=Admin;Pwd=;”Conn1.Close’————————–’ Recordset Object Methods’————————–’ Don‘t assume that we have a connection object.On Error GoTo AdoErrorLite’ Recordset Open Method #1:Open via Connection.Execute(…)Conn1.Open AccessConnectSet Rs1 = Conn1.Execute(“SELECT * FROM Employees”)Rs1.CloseConn1.Close’ Recordset Open Method #2:Open via Command.Execute(…)Conn1.ConnectionString = AccessConnectConn1.OpenCmd1.ActiveConnection = Conn1Cmd1.CommandText = “SELECT * FROM Employees”Set Rs1 = Cmd1.ExecuteRs1.CloseConn1.CloseConn1.ConnectionString = “”‘ Recordset Open Method #3:Open via Command.Execute(…)Conn1.ConnectionString = AccessConnectConn1.OpenCmd1.ActiveConnection = Conn1Cmd1.CommandText = “SELECT * FROM Employees”Rs1.Open Cmd1Rs1.CloseConn1.CloseConn1.ConnectionString = “”‘ Recordset Open Method #4:Open w/o Connection & w/Connect StringRs1.Open “SELECT * FROM Employees”, AccessConnect, adOpenForwardOnlyRs1.CloseDone:Set Rs1 = NothingSet Cmd1 = NothingSet Conn1 = NothingExit SubAdoError:i = 1On Error Resume Next’ Enumerate Errors collection and display properties of’ each Error object (if Errors Collection is filled out)Set Errs1 = Conn1.ErrorsFor Each errLoop In Errs1With errLoopstrTmp = strTmp & vbCrLf & “ADO Error # ” & i & “:”strTmp = strTmp & vbCrLf & “ADO Error# ” & .NumberstrTmp = strTmp & vbCrLf & “Description” & .DescriptionstrTmp = strTmp & vbCrLf & “Source” & .Sourcei = i + 1End WithNextAdoErrorLite:’ Get VB Error Object’s informationstrTmp = strTmp & vbCrLf & “VB Error # ” & Str(Err.Number)strTmp = strTmp & vbCrLf & “Generated by ” & Err.SourcestrTmp = strTmp & vbCrLf & “Description” & Err.DescriptionMsgBox strTmp’ Clean up gracefully without risking infinite loop in error handlerOn Error GoTo 0GoTo DoneEnd Sub
ERROR NOTES Only the ADO Connection object has an errors collection. The observant reader will notice that a lightweight error handler is in effect for the RecordSet.Open examples. In the event of an error opening a RecordSet object, ADO should return the most explicit error from the OLEDB provider. Some common errors that can be encountered with the preceding code follow.
If you omit (or there is an error in) the DefaultDir parameter in the connect string, you may receive the following error:

ADO Error # -2147467259
Description [Microsoft][ODBC Microsoft Access 97 Driver] ‘(unknown)’
isn’t a valid path. Make sure that the path name is
spelled correctly and that you are connected to the server
on which the file resides.
Source Microsoft OLE DB Provider for ODBC Drivers
If there is an error in the Dbq parameter in the connect string, you may receive the following error:

ADO Error # -2147467259 Description [Microsoft][ODBC Microsoft Access 97 Driver] Couldn’t find
file ‘(unknown)’.
Source Microsoft OLE DB Provider for ODBC Drivers
The preceding errors also populate the Connection.Errors collection with the following errors:

ADO Error # -2147467259
Description [Microsoft][ODBC Driver Manager] Driver’s
SQLSetConnectAttr failed
Source Microsoft OLE DB Provider for ODBC Drivers

ADO Error # -2147467259
Description Login Failed
Source Microsoft OLE DB Provider for ODBC Drivers Note that for each error, the ADO Error number is the same, in this case translating to 0×80004005, which is the generic E_FAIL error message. The underlying Component did not have a specific error number for the condition encountered, but useful information was never-the-less raised to ADO.

Symptoms
When you attempt to disconnect an ADO Recordset by setting its ActiveConnection Object property to Nothing, the following error message might appear:

Run-time error ‘3705′: Operation is not allowed when the object is open.
Resolution
Only client-side ADO recordsets can be disconnected. The specified error occurs only when you attempt to disconnect a server-side ADO recordset.

Symptoms
When using some controls bound to an ADO Recordset object, the EditMode is not set properly when you modify the data through the bound controls. These controls include the Microsoft DataCombo control.
With other controls bound to an ADO Recordset object, the EditMode is set properly when data is changed through the bound controls. These controls include the TextBox.
The expected behavior is that the EditMode is set properly for each type of bound control.
Resolution
The affected controls do not notify the Binding Collection that they are dirty.

Symptoms
If an ActiveX Data Objects data control (ADODC) is bound to a Microsoft Access table that specifies a default value for the numeric field, when you take the following actions:Add a new record to the ADO recordset, which the ADODC exposes, without entering a value for that numeric field.Update the recordset.Type a value into the numeric field (either directly into the Recordset field or into the corresponding DataGrid cell) in that same newly-added record.Update the recordset again.the following run-time error is raised with error number -2147217864 (80040e38) or 6153:

Row cannot be located for updating. Some values may have changed since it was last read.If a DataGrid control is bound to the ADODC, the same error message usually appears a second time without an error number in a dialog box that is entitled “Microsoft DataGrid Control.”
Resolution
To resolve this problem, remove the default value that is specified for the numeric field in the Access database table.
Alternately, you can run an UPDATE statement on a separate ADO Connection object to update the numeric field in the newly-added record directly in the database and then refresh the ADODC.

Symptoms
When the filter of an ADO Recordset object is set to one of the special constants, such as adFilterPendingRecords, the bound DataGrid control still displays all of the rows in the Recordset. If the filter is set to specify a particularfield, such as “State = ‘CA’,” the DataGrid displays only the rows that are specific to that filter.
Resolution
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Symptoms
DataCombo/DataList controls bound to an ADO.Recordset are not correctly reflecting data when Recordset.Sort or Recordset.Filter is applied.
Resolution
The DataCombo/DataList controls don’t use chapter handles when fetching rows from OLEDB rowsets. They do call IRowPosition::GetRowPosition and pass in a non-NULL chapter handle parameter, but apparently they don’t use it. As a result, these controls behave incorrectly when bound to child commands from the DE (or manually to child recordsets) or when the Sort and Filter properties on the recordset are modified after binding to these controls. In certain cases, this results in run-time errors and, in other cases, the controls are populated with the entire rowset rather than the restricted set of rows. The Hierarchical Flex grid control appears to work properly. The DataList and DataCombo controls are OLEDB bindable controls, but they do not utilize chapter handles.