Visual Basic Q&A

Symptoms
When you attempt to use the events of an ADO Data Control or the DataEnvironment with a reference to a version of the Microsoft ActiveX Data Objects (ADO) later than version 2.0, you receive the following error message:

Compile error:
Procedure declaration does not match description of event or procedure having the same name.
Resolution
The ADO Data Control and the Data Environment were compiled using Microsoft Data Access Components version 2.0.

Symptoms
When you attempt to run simple ActiveX Data Objects (ADO) code in an application, the following error can occur:

“Method ‘~’ of Object ‘~’ Failed”
Resolution
The most common cause for this error is mismatched DLL files with Microsoft Data Access Components (MDAC).

Symptoms
Running under versions of Microsoft Data Access Components (MDAC) prior to 2.5, you get the following error when choosing a Recordset object:

Run-time error ‘-2147024882 (8007000e)’Too many columns defined in the rowset.Running under MDAC 2.5 and later, you get the following error:

Run-time error ‘-2147024882 (8007000e)’:Rowsets cannot contain more than 2048 columns.
Resolution
Prior to MDAC 2.5, the client-cursor engine supported a maximum of 255 fields.
Under MDAC versions 2.5 and later, the client-cursor engine supports a maximum of 2048 fields.

Symptoms
You may experience problems with Active Documents that will be used on a Web server. The Active Document works on the development computer but fails on computers that try to access it over the Internet. You may receive the following error message:

Internet Explorer is opening file of unknown type:
<Name of Document>.VBD from Internet Explorer reports this generic message whenever it encounters problems while processing a .vbd file.
Resolution
This problem can be caused by one of the following scenarios:The registry contains a .vbd extension entry.You are trying to download a Microsoft Data Access Components (MDAC) component with your .vbd file.The wrong version of Visual Basic run-time or OLE files are referenced.You are using the wrong .vbd file.You are using an out-dated .vbd file.The Actxprxy.dll file is missing or is not registered properly.The Active Document .exe or .dll file is not registered properly.The Active Document is not signed or safe for scripting.There is a run-time error in the initialization code of the Active Document.Dependent files are not being downloaded.Other problems that can occur.

Symptoms
With Visual Basic and ADO, you have the ability to connect to anOracle database through a DSN-Less connection, execute a stored procedureusing parameters, and get return values from that stored procedure. Theexample in this article illustrates all of this functionality.
Resolution
To run the sample code in this article, you may need to download andinstall the Microsoft Data Access Components if you are using Visual Basic 5.0. The MDAC Components are located at:http://msdn.microsoft.com/en-us/data/aa937729.aspx(http://msdn.microsoft.com/en-us/data/aa937729.aspx)The following example was created against an Oracle 7.3 database through aSQL*Net 2.3 connection. All of the following code (including the storedprocedure) should work fine with Oracle 7.2. However, the Microsoft ODBCDriver for Oracle Help file states that it only supports SQL*Net 2.3.
There are two objects that need to be created on the Oracle database; atable (adooracle) and a stored procedure (adoinsert).
NOTE: If you have worked through the following Microsoft Knowledge Base article then you can use the Oracle objects created in that article (rdooracle and rdoinsert). Just change the Visual Basic code below accordingly:

167225?(http://support.microsoft.com/kb/167225/EN-US/) HOWTO: Access an Oracle Database Using RDO
Here are the data definition language (DDL) scripts to create theseobjects:
ADOORACLE – This is just a two-column table with the first column set asthe primary key:

CREATE TABLE adooracle (item_numberNUMBER(3) PRIMARY KEY,depot_numberNUMBER(3));
ADOINSERT – This procedure accepts a single numeric input parameter andreturns a single numeric output parameter. The input parameter is firstused by an input statement, then it is divided by 2 and set as the outputparameter:

CREATE OR REPLACE PROCEDURE adoinsert (insnum IN NUMBER, outnum OUT NUMBER)ISBEGININSERT INTO adooracle(Item_Number, Depot_Number)VALUES(insnum, 16);outnum := insnum/2;END;/
In SQL 3.3, use a foward slash (/) to terminate and execute the script declaring the stored procedure.
NOTE: You must use Procedures that have output parameters and not Functions when working with Oracle and ADO parameters.
The preceding scripts can be run from SQL*Plus. Once these objects have been created, you can create the Visual Basic project that will use them.
This sample project uses a simple form to send a bind parameter to theADOINSERT stored procedure and then return the output parameter from thatprocedure. Here are the steps to create the project:
Open a new project in Visual Basic and add a Reference to the Microsoft ActiveX Data Objects library.Place the following controls on the form:

ControlNameText/CaptionButtoncmdCheckCheckButtoncmdSendSendText BoxtxtInputLabellblInputInput: From the Tools menu, choose Options, Click the “Default FullModule View” option, and then click OK. This allows you to view allof the code for this project.Paste the following code into your code window:

Option ExplicitDim Cn As ADODB.ConnectionDim CPw1 As ADODB.CommandDim CPw2 As ADODB.CommandDim Rs As ADODB.RecordsetDim Conn As StringDim QSQL As StringPrivate Sub cmdCheck_Click()CPw1(0) = Val(txtInput.Text)Set Rs = CPw1.ExecuteMsgBox “Item_Number = ” & Rs(0) & “.Depot_Number = ” & Rs(1) & “.”Rs.CloseEnd SubPrivate Sub cmdSend_Click()CPw2(0) = Val(txtInput.Text)CPw2.ExecuteMsgBox “Return value from stored procedure is ” & CPw2(1) & “.”End SubPrivate Sub Form_Load()’You will need to replace the “*” with the appropriate values.Conn = “UID=*****;PWD=****;DRIVER={Microsoft ODBC for Oracle};” _& “SERVER=*****;”Set Cn = New ADODB.ConnectionWith Cn.ConnectionString = Conn.CursorLocation = adUseClient.OpenEnd WithQSQL = “Select Item_Number, Depot_Number From adooracle Where ” _& “item_number = ?”Set CPw1 = New ADODB.CommandWith CPw1.ActiveConnection = Cn.CommandText = QSQL.CommandType = adCmdText.Parameters.Append .CreateParameter(, adInteger, adParamInput)End WithQSQL = “adoinsert”Set CPw2 = New ADODB.CommandWith CPw2.ActiveConnection = Cn.CommandText = QSQL.CommandType = adCmdStoredProc.Parameters.Append .CreateParameter(, adInteger, adParamInput).Parameters.Append .CreateParameter(, adDouble, adParamOutput)End WithEnd SubPrivate Sub Form_Unload(Cancel As Integer)Cn.CloseSet Cn = NothingSet CPw1 = NothingSet CPw2 = NothingEnd Sub Run the project.When you enter a number in the text box, txtInput, and click the Send button, the Oracle stored procedure, ADOINSERT, is called. The number you entered in the text box is used as the input parameter for the procedure. The output parameter is used in a message box that is called after the stored procedure has completed processing. With your original value still in the text box, click the “Check” button. This creates a simple read-only resultset that is displayed in another message box.
What follows is a detailed explanation of the code used in thisdemonstration project.
The Form_Load event contains the code that creates the DSN-Less connection:

Conn = “UID=<uid>;PWD=<pwd>;DRIVER={Microsoft ODBC for Oracle};” _& “SERVER=<MyServer>;”Set Cn = New ADODB.ConnectionWith Cn.ConnectionString = Conn.CursorLocation = adUseClient.OpenEnd With Once you create the ADO connection object (Cn), you set several of itsparameters using the WITH statement.
The connect string that is used to open a connection to an Oracle database(or any database for that matter) is very dependant on the underlying ODBCdriver. You can see in the connect string below that the Microsoft Oracledriver you are using is named specifically by DRIVER=:

Conn = “UID=<uid>;PWD=<pwd>;DRIVER={Microsoft ODBC for Oracle};” _& “SERVER==<MyServer>;” The most important part of this connect string is the “SERVER” keyword. Thestring assigned to SERVER is the Database Alias which you set up inSQL*Net. This is the only difference in the connect string when connectingto an Oracle database. For a DSN-Less connection, as is stated in the Helpfile, you do not specify a DSN in the connect string.
Also in the Form_Load event is the code that creates the two ADO Commandobjects used in the project:

QSQL = “Select Item_Number, Depot_Number From adooracle Where ” _& “item_number = ?”Set CPw1 = New ADODB.CommandWith CPw1.ActiveConnection = Cn.CommandText = QSQL.CommandType = adCmdText.Parameters.Append .CreateParameter(, adInteger, adParamInput)End WithQSQL = “adoinsert”Set CPw2 = New ADODB.CommandWith CPw2.ActiveConnection = Cn.CommandText = QSQL.CommandType = adCmdStoredProc.Parameters.Append .CreateParameter(, adInteger, adParamInput).Parameters.Append .CreateParameter(, adDouble, adParamOutput)End With The first Command object (CPw1) is a simple parameterized query. TheCommandText has one parameter that is the item_number for the where clause.Note that the CommandType is set to adCmdText. This is different than theadCmdStoredProc CommandType in the second Command object (CPw2). The following is from the ADO Help HTML file:
“Use the CommandType property to optimize evaluation of the CommandTextproperty. If the CommandType property value equals adCmdUnknown (thedefault value), you may experience diminished performance because ADO mustmake calls to the provider to determine if the CommandText property is anSQL statement, a stored procedure, or a table name. If you know what typeof command you’re using, setting the CommandType property instructs ADO togo directly to the relevant code. If the CommandType property does notmatch the type of command in the CommandText property, an error occurs whenyou call the Execute method.”Using the WITH command, you can create and append parameters to the commandobject easily. The first parameter of the CreateParameter function is forthe name of the parameter. This has been left blank because the sampleprogram uses the index of the parameters collection to identify theindividual parameters (such as CPw1(0) to identify the first parameter).The sample program uses adInteger and adDouble datatypes. If it had used avariable length datatype, then the size parameter of the CreateParameterfunction would need to be set. Again, from the ADO Help HTML:
“If you specify a variable-length data type in the Type argument, you musteither pass a Size argument or set the Size property of the Parameterobject before appending it to the Parameters collection; otherwise, anerror occurs.”The remainder of the project is fairly straightforward and well-documentedin both the Online Help file and Books Online which come with Visual Basic.The ADO issues that are critical to working with Oracle (the connectstring and the calling of stored procedures) have been detailed in thisproject.

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.