Visual Basic Q&A

Symptoms
Pressing the ESC key while the cursor is in the SQL text box of theGeneral Tab in the property page of the Remote Data Control causes a blankGeneral tab. When you click the Colors tab and then click the General tabagain, an application error occurs and Visual Basic ends. The SQL text boxcan be empty or contain characters. Clicking the Cancel button on thewindow correctly closes the window. This behavior only occurs under theWindows 95 operating system.To work around this behavior, programmatically set the properties ofremote data control or use the Properties window of the Remote Data Objectto set the properties.
Resolution
Microsoft has confirmed this to be an issue in the Microsoft productslisted at the beginning of this article. Microsoft is researching thisissue and will post new information here in the Microsoft Knowledge Baseas it becomes available.

Symptoms
When you set the RecordSource property of an ADO Data Control to a different SQL SELECT statement and then try to execute the Refresh method, the following error is returned:

Syntax error in FROM clause.which is followed by:

Run-time error ‘-2147217900(80040e14)’:
Method ‘Refresh’ of object ‘IAdodc’ failedor:

Method ‘Refresh’ of object ‘IAdodc’ failed when attempting to refresh an ADODC after setting the recordsource property to another value.The errors occur if the CommandType property of the ADO Data Control is set to adCmdTable.
Resolution
When the CommandType of the ADO Data Control is set to adCmdTable, “SELECT * From” is automatically prepended to the RecordSource value.
Setting the RecordSource to a table name results in a valid SQL statement, such as SELECT * FROM Tablename.
Setting the RecordSource to a SQL SELECT statement, such as Select * From Tablename, results in a SQL statement ofSelect * From Select * From Tablename, which is an invalid SQL statement.

Symptoms
When you create a SQL Server local temporary table using the rdoConnection object by calling its .Execute method with default parameters, and then attempt to access the table after the Remote Data Objects (RDO) method has run, you may receive one of the following error messages:

Run-time error ‘40002′: 37000:[Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s)could not be prepared
-or-

Run-time error ‘40002′: S0002:[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid Object Name ‘#<Name of the temporary table>’
Resolution
The creation and use of temporary database tables to facilitate the storage and manipulation of volatile intermediate data is a common programming practice. The default behavior of the SQL Server Open Database Connectivity (ODBC) driver is to create and use temporary stored procedures to run prepared statements. The .Execute method of the rdoConnection object uses the SQLPrepare() and SQLExecute() ODBC application programming interface (API) calls by default to run a SQL statement as a prepared statement. Temporary tables that are created by a stored procedure are automatically dropped when the procedure completes execution. As a result, when you attempt to access a SQL Server temporary table that was created by calling the .Execute method of an rdoConnection object with default parameters, in subsequent statements you receive one of the error messages specified in the “Symptoms” section.

Symptoms
With SQL Server’s quoted_identifier option set to Off, you may receive the following error:

Run-time error ‘-2147217900 (80040e14)’:
Line 1: Syntax error near ‘tablename’ This error occurs when you are using client-side cursors with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). The error occurs on an ActiveX Data Objects (ADO) recordset’s Update method and may occur on an AddNew method.
Resolution
With ADO client-side cursors, when you invoke an ADO recordset’s AddNew or Update method, the OLE DB Provider prepares a SQL statement to send to SQL Server.
The Microsoft OLE DB Provider for SQL Server automatically quotes identifiers on an ADO recordset’s Update method and may quote identifiers on an AddNew method. Identifiers include table names and field names.
For example, updating the Titles table in the Pubs database with the following code:

MyADORecordet.Update The preceding would be prepared similar to the following:

UPDATE “titles” SET “title”=’Hello World’ WHERE “title_id”=’3′ Note that the table name is in quotes, “titles”, and that each field name is in quotes, “title”, “title_id”, and so on.
If SQL Server’s Quoted_Identifier option is set to Off, SQL Server will not recognize table names and field names enclosed in quotes.
The error “Syntax error near ‘tablename’” occurs.

Symptoms
When attempting to delete or update a record in a SQL Server cursor, the delete or update operation succeeds, but the following error returns:

Run-time error 3146 “ODBC call failed”.
Resolution
This behavior occurs when SQL Server does not return a message indicating the number of rows returned by a statement. SQL Server does not return a message indicating the number of rows affected by a statement after the following commands have been executed on SQL Server:

sp_configure “user options”, 512SET NOCOUNT ON

Symptoms
AdoGUIDz.exe is a self-extracting executable that contains a sample project that demonstrates using the globally unique identifier datatype (GUID) with Microsoft Access, SQL 6.5 and SQL 7.0. The sample code may be particularly helpful if you are attempting to manipulate GUIDs with ODBC versions 3.51 and below because those versions of ODBC do not support a native GUID datatype. ODBC versions 3.6 and above include the GUID datatype. Consequently, the methods for manipulating GUIDs with ODBC 3.6 are simpler.
NOTE: SQL 6.5 does not support a native GUID datatype so in order to store/retrieve GUIDs in SQL 6.5 you must use the VarBinary datatype and Byte Arrays.
Resolution
The following files are available for download from the Microsoft Download Center:
Adoguidz.exe(http://download.microsoft.com/download/vb60pro/sample/1/win98/en-us/adoguidz.exe) Release Date: DEC-29-1998
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 Services Microsoft 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.

FileNameSize———————————————————AdoGUID.bas3KBAdoGUID.exe60KBAdoGUID.frm25KBAdoGUID.frx1KBAdoGUID.mdb80KBAdoGUID.vbp2KBReadme.txt4KB Microsoft Access has a ReplicationID AutoNumber field that is a 16-byte (128 bit) Globally Unique Identifier (GUID) that uniquely identifies each record in the database. Please reference the sample project for the code that demonstrates how to SELECT specific GUIDs and Insert GUIDs using the AutoNumber field with Microsoft Access. The following function is a code snippet from the sample that demonstrates how to SELECT a specific GUID from an Access table using Microsoft ActiveX Data Objects (ADO):
Sample Code

Sub AccessReQueryADO()On Error GoTo ErrorMessageDim adoCn As adoDb.ConnectionDim adoRs As adoDb.RecordsetDim strCn As StringDim strSQL As StringstrCn = App.Path & “\adoGUID.mdb”Set adoCn = New adoDb.ConnectionWith adoCn.Provider = “Microsoft.JET.OLEDB.3.51″.CommandTimeout = 500.ConnectionTimeout = 500.Open strCn, “admin”, “”End WithIf Option7.Value = True ThenstrSQL = “SELECT * FROM GUIDtable WHERE ” & _”Instr(1,[colGUID],’” & strGUID & “‘)”ElsestrSQL = “SELECT * FROM GUIDtable”End IfSet adoRs = New adoDb.RecordsetWith adoRsSet .ActiveConnection = adoCn.LockType = adLockOptimistic.CursorLocation = adUseServer.CursorType = adOpenForwardOnlyEnd WithadoRs.Open strSQLtxtMessage.Text = “”While Not adoRs.EOFtxtMessage.Text = txtMessage.Text & _adoRs.Fields(“colGUID”).Value & “|”txtMessage.Text = txtMessage.Text & _adoRs.Fields(“colDescription”).Value & vbCrLfadoRs.MoveNextWendGoTo ExitSubErrorMessage:MsgBox Err.Number & ” : ” & vbCrLf & Err.DescriptionExitSub:Label6.Caption = “- ReQueried AccessADO GUID Table…”Set adoCn = NothingSet adoRs = NothingEnd Sub
Unlike SQL 6.5, SQL 7.0 supports a GUID datatype known as UNIQUEIDENTIFIER. This datatype is a 16-byte GUID stored in the same format as the Microsoft Access AutoNumber (GUID) datatype. There are subtle differences concerning how to Insert and Retrieve the GUIDs among the different database engines. Since SQL 6.5 does not support a native GUID datatype the methods used for storing/retrieving GUIDs are much different than SQL 7.0 or Microsoft Access. SQL 7.0 with the OLEDB provider is almost the same as Microsoft Access with or without the OLEDB provider (SQLOLEDB), as you will see in the sample code. With SQL 6.5 you must store the GUID as a VarBinary(16) datatype. Consequently, to retrieve the GUID with SQL65 you must use a stored procedure and build a Command object with the appropriate ByteArray parameter to pass to the stored procedure SELECT statement.
NOTE: You must use the same code techniques with SQL 7.0 as with SQL 6.5 if you are using the ODBC provider (MSDASQL) since in ODBC 3.51 and below the GUID datatype is not recognized.
The 16-byte (128 bit) data structure of a GUID:

typedef struct _GUID{unsigned longData1;unsigned shortData2;unsigned shortData3;unsigned charData4[8];} GUID;

* Data1An unsigned long integer data value.* Data2An unsigned short integer data value.* Data3An unsigned short integer data value.* Data4An array of unsigned characters. To demonstrate GUIDs with SQL 7.0 or SQL 6.5 in the sample project you must specify a valid (test) SQL 7.0/SQL 6.5 server and database. To do so, navigate to the Connection Info tab and change the Server and Database reference. The defaults are (local) Server and the Pubs database. Also, to use the native GUID datatype for SQL 7.0, you must change to the OLEDB provider (SQLOLEDB) by clicking the appropriate option button in the Provider frame at the top of the Form. If you select ODBC as the provider for SQL 7.0 then the application uses the same code as with SQL 6.5.
NOTE: The Connection Info tab has no bearing on Microsoft Access. The default database for Microsoft Access is included with the sample project AadoGUID.mdb as should reside in the Application path.
For each database you select in the sample project you must run Create Table to create the GUID test table and then Insert to automatically generate some test GUID data before running a ReQuery.
NOTE: CoCreateGUID() is called in the Insert sample code to generate the test GUID values.
In the sample application there is a ByteArray2GUID() function that is used to convert the VarBinary(16) byte array to a GUID string for display. Also, the function is used to convert the GUID string to a byte array for storage in the SQLServer VarBinary(16) datatype column. Note that the function is needed when using the GUIDs interchangeably between Microsoft Access and SQL 6.5. If you Export the Microsoft Access table to SQL 6.5 you will see that the bytes are not stored in the same order in which they display in the Microsoft Access table view. For example:

Reversed…Not Reversed…>—————-<|>—————<20C68F83-9593-0011-BFBB-00C04F8F8347 ‘SQLServer view after table Export.838FC620-9395-1100-BFBB-00C04F8F8347 ‘Microsoft Access view. NOTE: The bytes are in (DWord and Word) reverse order after Exporting the Microsoft Access table.
Because the Microsoft Access Upsizing Wizard results in the same storage of the bytes in SQL 6.5, you must use the ByteArray2GUID() and GUID2ByteArray() functions to remain compatible with the storage of the GUIDs in Microsoft Access. If you do not need to Export the Microsoft Access table to SQL 6.5 or upsize the Microsoft Access database to SQL 6.5 then you need only store the bytes in a straightforward fashion.
The following is a code snippet from the code sample that demonstrates the storage of the GUID in the byte format of Microsoft Access.
Sample Code

Sub SQL65InsertGUID()’Insert GUID record.On Error GoTo ErrorMessageDim adoCn As adoDb.ConnectionDim adoRs As adoDb.RecordsetDim strGUIDtmp As StringDim bytGUID() As ByteDim strCn As StringDim strSQL As StringstrCn = “Provider=” & strProvider & _”;Driver={SQL Server}” & _”;Server=” & txtServer & _”;Database=” & txtDatabase & _”;Uid=” & txtUserID & _”;Pwd=” & txtPasswordSet adoCn = New adoDb.ConnectionWith adoCn.ConnectionString = strCn.CommandTimeout = 500.ConnectionTimeout = 500.OpenEnd WithstrGUIDtmp = strGUIDbytGUID = GUID2ByteArray(FilterGUID(strGUIDtmp))strSQL = “SELECT * FROM GUIDtable WHERE 1=0″Set adoRs = New adoDb.RecordsetWith adoRsSet .ActiveConnection = adoCn.LockType = adLockOptimistic.CursorLocation = adUseServer.CursorType = adOpenForwardOnlyEnd WithadoRs.Open strSQLadoRs.AddNewadoRs.Fields(“colGUID”).Value = bytGUIDadoRs.Fields(“colDescription”).Value = “This is a test GUID”adoRs.UpdateGoTo ExitSubErrorMessage:MsgBox Err.Number & ” : ” & vbCrLf & Err.DescriptionExitSub:Label6.Caption = “[ASCII 176] Inserted SQL65 GUID Record…”Set adoCn = NothingSet adoRs = NothingEnd Sub’======================Function GUID2ByteArray(ByVal strGUID As String) As Byte()Dim i As IntegerDim j As IntegerDim sPos As IntegerDim OffSet As IntegerDim sGUID(0 To 2) As ByteDim bytArray() As ByteReDim bytArray(0 To 15) As BytesGUID(0) = 7sGUID(1) = 11sGUID(2) = 15OffSet = 0sPos = 0′AABBCCDD-AABB-CCDD-XXXX-XXXXXXXXXXXX ‘Microsoft Access view.’DDCCBBAA-BBAA-DDCC-XXXX-XXXXXXXXXXXX ‘SQLServer view.’Need to loop through to build the GUID byte array in the Microsoft’Access storage format since the first eight bytes are reversed.For i = 0 To UBound(sGUID)For j = sGUID(i) To (OffSet + 1) Step -2bytArray(sPos) = “&H” & Mid$(strGUID, j, 2)sPos = sPos + 1Next jOffSet = sGUID(i)Next iFor i = 17 To 31 Step 2bytArray(sPos) = “&H” & Mid$(strGUID, i, 2)sPos = sPos + 1Next iGUID2ByteArray = bytArray()End Function