Visual Basic Q&A

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
Clicking the plus sign to expand a stored procedure in the Data View window in either Visual Basic, Visual InterDev, or Visual J++ generates the following error message if the stored procedure contains calls to extended stored procedures that return something other than 0 or 1, such as xp_cmdshell:

Devenv.exe – Application ErrorThe instruction at “0×412406f3″ referenced memory at “0×00000006″. The memory could not be “read”.
Resolution
Clicking the plus sign executes the stored procedure to enumerate the parameters and tables for the stored procedure. When the data environment tries to execute xp_cmdshell it generates an error.
NOTE: This problem does not occur with SQL Server 7.0 or later. It also does not occur under a Microsoft Windows 2000 environment.

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
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
Subclassing intercepts messages that the operating system sends to specific windows. Subclassing allows you to process messages that are not handled natively by a control or form.
Subclassing requires that you use the AddressOf operator, which provides callback functionality. For more information about how to use this operator, visit the following MSDN Web site:
AddressOf Operator
http://msdn.microsoft.com/en-us/library/aa242738.aspx(http://msdn.microsoft.com/en-us/library/aa242738.aspx)NOTE: The AddressOf operator provides callback functionality when it calls Windows APIs that require this feature. This functionality is also known as a function pointer. The AddressOf operator was not designed or tested for subclassing purposes. Subclassing can have disastrous results if you use it incorrectly.
Microsoft supports the demonstration of subclassing in Visual Basic. Some controls may experience problems if they are made into a subclass, because their intended behavior is modified beyond the original design. Therefore, subclassing in Visual Basic does not always work. Microsoft does not guarantee that subclassing in Visual Basic will produce the results that you want.
Resolution
The limitations and risks of function pointers in Visual Basic include the following:
Debugging: If your application fires a callback function while it is in break mode, the code is executed, but any breaks or steps are ignored. If the callback function generates an exception, you can catch it, and then return the current value. Resets are prohibited in break mode when a callback function is on the stack.
Thunks: Windows enables relocatable code by using thunking. If you delete a callback function in break mode, the thunk is modified to return a zero value. This value is typically correct, but not always. If you delete a callback function in break mode, and then you type the callback function again, it is possible that some call recipients will not know about the new address.
Thunks are not used in the compiled executable. The pointer is passed directly to the entry point.
Passing a function with the wrong signature: If you pass a callback function that takes a different number of arguments than the caller expects, or if you pass a callback function that mistakenly calls an argument by using ByRef or ByVal, your application may fail. You must pass a function with the correct signature.
Passing a function to a Windows procedure that no longer exists: When you make a window into a subclass, you pass a function pointer to Windows as the Windows procedure (WindowProc). However, when you run your application in the IDE, the WindowProc function may be called after the underlying function is destroyed. This may cause a general protection fault, and may cause the Visual Basic development environment to fail.
Visual Basic to Visual Basic function pointers are not supported: Pointers to Visual Basic functions cannot be passed in Visual Basic. Only pointers from Visual Basic to a DLL function are supported.
Containing errors in a callback procedure: Any errors in a callback procedure must not be propagated back to the external procedure that initially called. Therefore, put the On Error Resume Next statement at the beginning of the callback procedure.

Symptoms
In Windows programming terminology, subclassing is the process of creatinga message handling procedure to intercept messages for a given window,handling those messages, and passing any remaining messages to the window’soriginal message handler.
This article demonstrates how to subclass a UserControl in Visual Basicusing the AddressOf operator.
Resolution
WARNING: One or more of the following functions are discussed in this article; VarPtr, VarPtrArray, VarPtrStringArray, StrPtr, ObjPtr. These functions are not supported by Microsoft Technical Support. They are not documented in the Visual Basic documentation and are provided in this Knowledge Base article “as is.” Microsoft does not guarantee that they will be available in future releases of Visual Basic.
In Visual Basic, the AddressOf operator is used to specify whichfunction in a code module (.BAS file) will be the message handlingprocedure. The subclass procedure is basically a message filter thatperforms non-default processing for a few key messages, and passes othermessages to a default window procedure using CallWindowProc API. TheCallWindowProc API function passes a message to the Windows system, which,in turn, sends the message to the specified window procedure.
When subclassing a UserControl with the AddressOf operator, you must takeinto account that multiple instances of the control utilize the same codemodule and its functions. In order for the UserControl to maintain its”own” message handling function, the common procedure in the module needsto forward the messages to the proper instance of the UserControl.
To implement a function for each instance of a UserControl, you need tocreate a function in the UserControl to process the messages. The currentrequirement of Visual Basic is that the AddressOf operator can only beused for functions residing in a .BAS module. Therefore, the function inthe .BAS module must forward the message to the correct instance of theUserControl.

The following steps show how to create a UserControl that detects mouseactivation at design-time and run-time.
WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWNRISK. Microsoft provides this code “as is” without warranty of any kind, either express or implied, including but not limited to the impliedwarranties of merchantability and/or fitness for a particular purpose.
WARNING: Failure to unhook a window before its imminent destruction willresult in application errors, Invalid Page Faults, and data loss. This isdue the fact that the new WindowProc function being pointed to no longerexists, but the window has not been notified of the change. Always unhookthe sub-classed window upon unloading the sub-classed UserControl orexiting the application. This is especially important while debugging anapplication that uses this technique within the Microsoft Visual BasicDevelopment Environment. Pressing the End button or selecting End from theRun menu without unhooking will cause an Invalid Page Fault and closeMicrosoft Visual Basic.
Step-by-Step ExampleStart Visual Basic and create a new ActiveX Control Project.”UserControl1″ is created by default.Add the following code to the UserControl1 code module:

Option Explicit’mWndProcOrg holds the original address of the’Window Procedure for this window. This is used to’route messages to the original procedure after you’process them.Private mWndProcOrg As Long’Handle (hWnd) of the subclassed window.Private mHWndSubClassed As Long’Constant for Windows Message used in sample.Private Const WM_MOUSEACTIVATE = &H21Private Sub SubClass()’————————————————————-’Initiates the subclassing of this UserControl’s window (hwnd).’Records the original WinProc of the window in mWndProcOrg.’Places a pointer to the object in the window’s UserData area.’————————————————————-’Exit if the window is already subclassed.If mWndProcOrg Then Exit Sub’Redirect the window’s messages from this control’s default’Window Procedure to the SubWndProc function in your .BAS’module and record the address of the previous Window’Procedure for this window in mWndProcOrg.mWndProcOrg = SetWindowLong(hWnd, GWL_WNDPROC, _AddressOf SubWndProc)’Record your window handle in case SetWindowLong gave you a’new one. You will need this handle so that you can unsubclass.mHWndSubClassed = hWnd’Store a pointer to this object in the UserData section of’this window that will be used later to get the pointer to’the control based on the handle (hwnd) of the window getting’the message.Call SetWindowLong(hWnd, GWL_USERDATA, ObjPtr(Me))End SubPrivate Sub UnSubClass()’———————————————————–’Unsubclasses this UserControl’s window (hwnd), setting the’address of the Windows Procedure back to the address it was’at before it was subclassed.’———————————————————–’Ensures that you don’t try to unsubclass the window when’it is not subclassed.If mWndProcOrg = 0 Then Exit Sub’Reset the window’s function back to the original address.SetWindowLong mHWndSubClassed, GWL_WNDPROC, mWndProcOrg’0 Indicates that you are no longer subclassed.mWndProcOrg = 0End SubFriend Function WindowProc(ByVal hWnd As Long, _ByVal uMsg As Long, ByVal wParam As Long, _ByVal lParam As Long) As Long’————————————————————–’Process the window’s messages that are sent to your UserControl.’The WindowProc function is declared as a “Friend” function so’that the .BAS module can call the function but the function’cannot be seen from outside the UserControl project.’————————————————————–’Start Demo Code: Changes the color of the UserControl each’time the control is clicked in design-time from red to blue’or from blue to red.If uMsg = WM_MOUSEACTIVATE ThenIf UserControl.BackColor = vbRed ThenUserControl.BackColor = vbBlueElseUserControl.BackColor = vbRedEnd IfEnd If’End Demo Code.’Forwards the window’s messages that came in to the original’Window Procedure that handles the messages and returns’the result back to the SubWndProc function.WindowProc = CallWindowProc(mWndProcOrg, hWnd, _uMsg, wParam, ByVal lParam)End FunctionPrivate Sub UserControl_Initialize()’Occurs the first time a UserControl is placed on a container.UserControl.BackColor = vbRedSubClassEnd SubPrivate Sub UserControl_Terminate()UnSubClassEnd Sub Add new Standard Module (.BAS) to the project and add the followingcode:

Option Explicit’API Declarations used for subclassing.Public Declare Sub CopyMemory _Lib “kernel32″ Alias “RtlMoveMemory” _(pDest As Any, _pSrc As Any, _ByVal ByteLen As Long)Public Declare Function SetWindowLong _Lib “user32″ Alias “SetWindowLongA” _(ByVal hWnd As Long, _ByVal nIndex As Long, _ByVal dwNewLong As Long) As LongPublic Declare Function GetWindowLong _Lib “user32″ Alias “GetWindowLongA” _(ByVal hWnd As Long, _ByVal nIndex As Long) As LongPublic Declare Function CallWindowProc _Lib “user32″ Alias “CallWindowProcA” _(ByVal lpPrevWndFunc As Long, _ByVal hWnd As Long, _ByVal Msg As Long, _ByVal wParam As Long, _ByVal lParam As Long) As Long’Constants for GetWindowLong() and SetWindowLong() APIs.Public Const GWL_WNDPROC = (-4)Public Const GWL_USERDATA = (-21)’Used to hold a reference to the control to call its procedure.’NOTE: “UserControl1″ is the UserControl.Name Property at’design-time of the .CTL file.’(‘As Object’ or ‘As Control’ does not work)Dim ctlShadowControl As UserControl1′Used as a pointer to the UserData section of a window.Dim ptrObject As Long’The address of this function is used for subclassing.’Messages will be sent here and then forwarded to the’UserControl’s WindowProc function. The HWND determines’to which control the message is sent.Public Function SubWndProc( _ByVal hWnd As Long, _ByVal Msg As Long, _ByVal wParam As Long, _ByVal lParam As Long) As LongOn Error Resume Next’Get pointer to the control’s VTable from the’window’s UserData section. The VTable is an internal’structure that contains pointers to the methods and’properties of the control.ptrObject = GetWindowLong(hWnd, GWL_USERDATA)’Copy the memory that points to the VTable of our original’control to the shadow copy of the control you use to’call the original control’s WindowProc Function.’This way, when you call the method of the shadow control,’you are actually calling the original controls‘ method.CopyMemory ctlShadowControl, ptrObject, 4′Call the WindowProc function in the instance of the UserControl.SubWndProc = ctlShadowControl.WindowProc(hWnd, Msg, _wParam, lParam)’Destroy the Shadow Control CopyCopyMemory ctlShadowControl, 0&, 4Set ctlShadowControl = NothingEnd Function NOTE: If your UserControl is not named UserControl1, you need to changethe “Dim ctlControl As UserControl1″ line of code to indicate thecorrect name of your UserControl as specified by its Name property.
Close all the project windows that may be open and save the project.NOTE: During subclassing, you do not want to stop the executing code orit will cause an exception. Always save your project before testing anysubclassing code.
From the File menu, select “Add Project…” and add a Standard EXEproject. This will be your test project. It is named Project2 bydefault.Open the default form (Form1) of Project2 and place an instance of theUserControl on the form. It should appear as a red rectangle.Each time the mouse is clicked on the UserControl, it should toggle itscolor between red and blue.Place a second instance of the UserControl on the form. Note that thesubclassing works independently on each control, sending the messages tothe appropriate control.