Visual Basic Q&A

Symptoms
If you attempt to execute a SQL INSERT statement that contains a literalstring having over 16,379 characters with the Microsoft ODBC Driver forAccess, the following error message occurs:

[Microsoft][ODBC Microsoft Access 97 Driver]
Syntax error in INSERT INTO statement.SQL UPDATE SQL statements with literal strings over 16,379 bytes also failwith a similar error.
Resolution
The SQL parser for the Microsoft ODBC Driver for Access prior to 4.0.4202 does not accept string literals larger than 16,379 bytes. This is the defined limit for literal strings in a SQL statement for the Microsoft ODBC Driver for Access.
With the Microsoft ODBC Driver for Access versions 4.0.4202 and later, you can execute a SQL INSERT containing a string literal having over 16,379 characters. However, you will get the following error message with a SQL INSERT that contains a literal string having over 516,076 characters:

[Microsoft][ODBC Microsoft Access Driver]
Query is too complexThis error occurs with both Access 97 and Access 2000 databases.

Symptoms
The following error occurs if a variant set equal to an Array() function isredimensioned using the Preserve keyword:

Run-time error ‘5′ “Invalid Procedure Call or Argument”NOTE: This behavior occurs only under the following two conditions:If Microsoft Data Access Components (MDAC) 2.0 or Visual Studio 6.0is installed on the computer.
-and-If the array has not been initialized.
Resolution
This error occurs with the newer version of the OLE automation core file,(version 2.304261). The Oleaut32.dll file is installed in your systemdirectory when you install MDAC 2.0 or Visual Studio 6.0 products.

Symptoms
When you install Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET on a computer that uses third-party firewall software, such as Zone Labs ZoneAlarm Pro, the Microsoft Windows Component Update (WCU) does not install all the Microsoft .NET Framework components, and the installation may stop responding (hang).
Resolution
When you install Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET programs, the WCU does not install all the Microsoft .NET Framework components. The WCU installation may stop responding while it is installing the Microsoft .NET Framework.

Symptoms
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
Resolution
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe(http://download.microsoft.com/download/excel2000/sample/12_27_2000/w98nt42kme/en-us/excelado.exe)Release Date: December 12, 2000
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591?(http://support.microsoft.com/kb/119591/EN-US/)How to Obtain Microsoft Support Files from Online ServicesMicrosoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.Why Use ADO?The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel: Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format ismaintained. If you require “conditional” formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.Jet OLE DB Provider Specifics for Excel WorkbooksThe Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks: Excel 3.0Excel 4.0Excel 5.0Excel 8.0NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:

Dim oConn As New ADODB.ConnectionWith oConn.Provider = “Microsoft.Jet.OLEDB.4.0″.Properties(“Extended Properties”).Value = “Excel 8.0″.Open “C:\Book1.xls”‘…..CloseEnd With ?????-or-

Dim oConn As New ADODB.ConnectionoConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=C:\Book1.xls;” & _”Extended Properties=”"Excel 8.0;”"”oConn.Close Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.

oRS.Open “Select * from [Sheet1$]“, oConn, adOpenStatic Use a range with a defined name (for example, [Table1]).

oRS.Open “Select * from Table1″, oConn, adOpenStatic Use a range with a specific address (for example, [Sheet1$A1:B10]).

oRS.Open “Select * from [Sheet1$A1:B10]“, oConn, adOpenStatic Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to “guess” the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.Files Included with the SampleThe ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project FilesExcelADO.vbpForm1.frmForm1.frxActive Server PagesEmpData.aspOrders.aspMicrosoft Excel WorkbooksOrdersTemplate.xlsEmpDataTemplate.xlsProductsTemplate.xlsSourceData.xlsMicrosoft Access DatabaseData.mdbHow to Use the SampleExtract the contents of the .exe file to a folder.
To use the Visual Basic project: In Visual Basic, open the ExcelADO.vbp file. On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.Press the F5 key to run the program. A form for the demonstration appears.Click Sample 1.This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:On the Insert menu in Excel, select Names, and then select Define. In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel’s OFFSET function, to compute a total on the data added to the worksheet. Quit Microsoft Excel and return to the Visual Basic application.Click Sample 2.This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3.This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new “Products” worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any “conditional” formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4.This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet (“Sheet1$” or “Sheet2$”) for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.To use the Active Server Pages (ASP):Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.Copy the following files to the folder you created in the previous step:EmpData.aspOrders.aspData.mdbEmpDataTemplate.xlsOrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application. (c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

Symptoms
When you use DataReader to read a row, if you try to access columns in that row, you receive an error message similar to the following:

System.InvalidOperationException: Invalid attempt to read from column ordinal ‘0′.With CommandBehavior.SequentialAccess, you may only read from column ordinal ‘2′ or greater.
Resolution
This problem occurs because you executed OleDbCommand or SqlCommand with the System.Data.CommandBehavior.SequentialAccess flag set but did not access the columns sequentially.

Symptoms
Visual Basic .NET or Visual Basic 2005 offers the ability to use threads in Visual Basic applications for the first time. Threads introduce debugging issues such asrace conditions and deadlocks. This article explores these two issues.
Resolution
Race ConditionsA race condition occurs when two threads access a shared variable at the same time. The first thread reads the variable, and the second thread reads the same value from the variable. Then the first thread and second thread perform their operations on the value, and they race to see which thread can write the value last to the shared variable. The value of the thread that writes its value last is preserved, because the thread is writing over the value that the previous thread wrote.Details and ExampleEach thread is allocated a predefined period of time to execute on a processor. When the time that is allocated for the thread expires, the thread’s context is saved until its next turn on the processor, and the processor begins the execution of the next thread.
How can a one-line command cause a race condition? Examine the following example to see how a race condition occurs. There are two threads, and both are updating a shared variable called total (which is represented as dword ptr ds:[031B49DCh] in the assembly code).
Visual Basic code:

‘Thread 1Total = Total + val1

‘Thread 2Total = Total – val2 Assembly code (with line numbers) from the compilation of the preceding Visual Basic code:

‘Thread 1 1.moveax,dword ptr ds:[031B49DCh]2.addeax,edi3.jno000000334.xorecx,ecx5.call7611097F6.movdword ptr ds:[031B49DCh],eax

‘Thread 2 1.moveax,dword ptr ds:[031B49DCh]2.subeax,edi3.jno000000334.xorecx,ecx5.call76110BE76.movdword ptr ds:[031B49DCh],eax By looking at the assembly code, you can see how many operations the processor is performing at the lower level to execute a simple addition calculation. A thread may be able to execute all or part of its assembly code during its time on the processor. Now look at how a race condition occurs from this code.
Total is 100, val1 is 50, and val2 is 15. Thread 1 gets an opportunity to execute but only completes steps 1 through 3. This means that Thread 1 read the variable and completed the addition. Thread 1 is now just waiting to write out its new value of 150. After Thread 1 is stopped, Thread 2 gets to execute completely. This means that it has written the value that it calculated (85) out to the variable Total. Finally, Thread 1 regains control and finishes execution. It writes out its value (150). Therefore, when Thread 1 is finished, the value of Total is now 150 instead of 85.
You can see how this might be a major problem. If this were a banking program, the customer would have money in their account that should not be present.
This error is random, because it is possible for Thread 1 to complete its execution before its time on the processor expires, and then Thread 2 canbegin its execution. If these events occur, the problem does not occur.Thread execution is nondeterministic, therefore you cannot control the time or order of execution. Also note that the threads may execute differently in runtime versus debug mode. Also, you can see that if youexecute each thread in series, the error does not occur. This randomness makes these errors much harder to track down and debug.
To prevent the race conditions from occurring, you can lock shared variables, so that only one thread at a time has access to the shared variable. Do this sparingly, because if a variable is locked in Thread 1 and Thread 2 also needs the variable, Thread 2’s execution stops while Thread 2 waits for Thread 1 to release the variable. (For more information, see “SyncLock” in the “References” section of this article.)SymptomsThe most common symptom of a race condition is unpredictable values of variables that are shared between multiple threads. This results from the unpredictability of the order in which the threads execute. Sometime one thread wins, and sometime the other thread wins. At other times, execution works correctly. Also, if each thread is executed separately, the variable value behaves correctly.
DeadlocksA deadlock occurs when two threads each lock a different variable at the same time and then try to lock the variable that the other thread already locked. As a result, each thread stops executing and waits for the other thread to release the variable. Because each thread is holding the variable that the other thread wants, nothing occurs, and the threads remain deadlocked.Details and ExampleThe following code has two objects, LeftVal and RightVal:

‘Thread 1SyncLock LeftVal SyncLock RightVal’Perform operations on LeftVal and RightVal that require read and write. End SyncLockEnd SyncLock

‘Thread 2SyncLock RightVal SyncLock LeftVal’Perform operations on RightVal and LeftVal that require read and write. End SyncLockEnd SyncLock A deadlock occurs when Thread 1 is permitted to lock LeftVal. The processor stops Thread 1’s execution and begins the execution of Thread 2. Thread 2 locks RightVal and then tries to lock LeftVal. Because LeftVal is locked, Thread 2 stops and waits for LeftVal to be released. Because Thread 2 is stopped, Thread 1 is permitted to continue executing. Thread 1 tries to lock RightVal but cannot, because Thread 2 has locked it. As a result, Thread 1 starts to wait until RightVal becomes available. Each threadwaits for the other thread, because each thread has locked the variable that the other thread is waiting on, and neither thread is unlocking the variable that it is holding.
A deadlock does not always occur. If Thread 1 executes both locks before the processor stops it, Thread 1 can perform its operations and then unlock the shared variable. After Thread 1 unlocks the variable, Thread 2 can proceed with its execution, as expected.
This error seems obvious when these snippets of code are placed side by side, but in practice, the code may appear in separate modules or areas of your code. This a very hard error to track down because, from this same code, both correct execution and incorrect execution can occur.SymptomsA common symptom of deadlock is that the program or group of threads stops responding. This is also known as a hang. At least two threads are each waiting for a variable that the other thread locked. The threads do not proceed, because neither thread will release its variable until it gets the other variable. The whole program can hang if the program is waiting on one or both of those threads to complete execution.
What Is a Thread?Processes are used to separate the different applications that are executing at a specified time on a single computer. The operating system does not execute processes, but threads do. A thread is a unit of execution. The operating system allocates processor time to a thread for the execution of the thread’s tasks. A single process can contain multiple threads of execution. Each thread maintains its own exception handlers, scheduling priorities, and a set of structures that the operating system uses to save the thread’s context if the thread cannot complete its execution during the time that it was assigned to the processor. The context is held until the next time that the thread receives processor time. The context includes all the information that the thread requires to seamlessly continue its execution. This information includes the thread’s set of processor registers and the call stack inside the address space of the host process.