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
If you run commands or call methods of the SqlCommand or OleDbCommand object, you receive the following error message if a connection is not open:

An unhandled exception of type ‘System.InvalidOperationException’ occurred in system.data.dll
Additional information: ExecuteReader requires an open and available Connection (state=Closed).
Resolution
The DataAdapter object does not require that you explicitly open a connection to run some of its methods. Therefore, you can call the Update or Fill method of the DataAdapter object when the connection is closed. The Connection object that is associated with the SELECT statement must be valid, but it does not need to be open. If you close the connection before you call Fill, the connection is opened to retrieve the data and then closed. If the connection is open before you call Fill, it remains open.
Steps to Reproduce the BehaviorStart Microsoft Visual Studio .NET.Create a new Windows Application project in Visual Basic .NET. Form1 is added to the project by default.Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.Place two Button controls and one DataGrid control on Form1. Button1, Button2, and DataGrid1 are created by default.Change the Name property of Button1 to btnDataAdapter and the Text property to DataAdapter.
Change the Name property of Button2 to btnCommand and the Text property to Command.Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the “General Declarations” section of Form1:

Imports SystemImports System.Data.OleDbImports System.Data.SqlClient In the Code window, copy and paste the following code after the “Windows Form Designer generated code” region:

Private Sub btnDataAdapter(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles btnDataAdapter.ClickDim myConnString As String = _”User ID=sa;password=sa;Initial Catalog=Northwind;Data Source=myServer”Dim mySelectQuery As String = _”Select * From Customers Where CustomerID Like ‘A%’”Dim con As New SqlConnection(myConnString)’The code works fine even if you comment out the next line (to open the connection).con.Open()Dim daCust As New SqlDataAdapter(mySelectQuery, con)Dim ds As New DataSet()daCust.Fill(ds, “Cust”)DataGrid1.DataSource = dsDataGrid1.DataMember = “Cust”End SubPrivate Sub btnCommand(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles btnCommand.ClickDim myConnString As String = _”User ID=sa;password=sa;Initial Catalog=Northwind;Data Source=myServer”Dim mySelectQuery As String = _”Select * From Customers Where CustomerID Like ‘A%’”Dim con As New SqlConnection(myConnString)Dim myCommand As New SqlCommand(mySelectQuery, con)’An exception is thrown if you comment out the next line (to open the connection).con.Open()Dim myReader As SqlDataReader = myCommand.ExecuteReader()While myReader.Read()’Process data.End WhilemyReader.Close()con.Close()End Sub Modify the connection string (myConnString) as appropriate for your environment.Save your project. On the Debug menu, click Start to run your project.Comment out the line of code that opens the connection. Notice that DataAdapter.Fill works as expected, but Command.ExecuteReader fails with the above-mentioned exception.

Symptoms
You can use Microsoft SQL Server’s PRINT statement in stored procedures to return messages as informational errors in ADO, OLE DB, and ODBC applications. However, a Visual Basic client application may not capture such informational messages when it sets up a DataEnvironment command and uses the DataEnvironment.CommandName syntax to run the stored procedure.
Resolution
This problem has its roots in ADO. The InfoMessage event of an ADO Connection object does not fire when its CursorLocation property is set to adUseClient. By default, the CursorLocation property of Visual Basic 6.0 DataEnvironment Connection objects is set to adUseClient. As a result, the InfoMessage event procedure of DataEnvironment Connection objects does not fire when informational messages are returned to the client application.

Symptoms
When you create a command object for Oracle to run a stored procedure by using the OracleCommand object from the Toolbox, you may receive the following error message after you specify the stored procedure name in the CommandText property:

Invalid Property Data
The stored procedure “OraclePackageName.OracleProcedureName” could not be found in the database.
Resolution
To work around this problem, use one of the following methods: In the CommandText property of OracleCommand1, type the name of the stored procedure exactly as it appears (this property is case-sensitive) in the stored procedure list in Server Explorer.Click OK to ignore the error message, and then manually add the code to call the Oracle stored procedure in your class. You can view the name of the stored procedure in Server Explorer. To do this, follow these steps: On the View menu, click Server Explorer.Right-click Data Connection, and then click Add Connection.On the Provider tab, click to select the Microsoft OLE DB Provider for Oracle check box. Click the Connection tab. Type the server name, the user name and the password, and then click Test Connection. Click OK to close the Test connection succeeded dialog box. Click OK to close the Data Link Properties dialog box. Expand Oracle database. Expand Stored Procedure to view the list of existing stored procedures.

Symptoms
If more than one connection exists in Microsoft Visual Studio .NET Server Explorer, and if you cannot see Server Explorer in Visual Studio .NET, you may receive the following error message when you click Fill Dataset to preview the data in the Data Adapter Preview window:

SqlDataAdapterName. The data adapter could not return the data from the data source.
The connection string is invalid.Alternatively, when you right-click DataAdapter and then click Generate Dataset, you may receive the following error message:

Retrieving the schema for SqlDataAdapter1 failed.
The connection string is invalid.
Resolution
Connection Manager manages the Microsoft SQL Server connections in Visual Studio .NET. When you click Fill Dataset or Generate Dataset, a Connection object is built and then added to the Connection Manager list of Connection objects. Connection Manager finds the Connection in its list and then prepares to return the Connection with the display name of the Connection. To return the display name, Connection Manager incorrectly opens Server Explorer and then looks for a connection in Server Explorer. Connection Manager generates an error because the connection does not exist in Server Explorer.

Symptoms
This article demonstrates how to programmatically use Data Links feature of the Microsoft Data Access Components in order to generate a connection string at run-time.
Resolution
In version 2.0 of the Microsoft Data Access Components, Data Links were introduced. Data Link files are similar to ODBC DSN files, but allow you to select an OLE DB provider to connect to your database. With the OLE DB Provider for ODBC drivers, you can also connect to an ODBC data source.
Double-clicking on a Data Link file displays a set of property pages that allow you to build a connection string to connect to your database.
You can use this same functionality in your Visual Basic applications by following the steps listed below: Launch Visual Basic and open a new Standard Exe project. Form1 is created by default.Select References from the Project menu, and then select Microsoft OLE DB Service Component 1.0 Type Library from the list of available references.Add a CommandButton to your form.Add the following code to the Click event of your CommandButton:

Private Sub Command1_Click()Dim objDataLink As New DataLinksDim strConn As StringstrConn = objDataLink.PromptNewMsgBox “The connection string you created is:” & _vbCrLf & strConnEnd Sub Run the project. When you click the CommandButton, you will see the Data Links property pages. Once you have specified how you want to connect to your database and click the OK button, you’ll see the connection string in a dialog box.