Visual Basic Q&A

Symptoms
The Stream object introduced in ActiveX Data Objects (ADO) 2.5 can be used to greatly simplify the code that needs to be written to access and modify Binary Large Object (BLOB) data in a SQL Server Database. The previous versions of ADO [ 2.0, 2.1, and 2.1 SP2 ] required careful usage of the GetChunk and AppendChunk methods of the Field Object to read and write BLOB data in fixed-size chunks from and to a BLOB column. An alternative to this method now exists with the advent of ADO 2.5. This article includes code samples that demonstrate how the Stream object can be used to program the following common tasks: Save the data stored in a SQL Server Image column to a file on the hard disk.Move the contents of a .gif file to an Image column in a SQL Server table.
Resolution
The following code samples are based on the data stored in the pub_info table in the SQL Server 7.0 pubs sample database. You need to modify the ADO connection string to point to your SQL Server installation. Example 1 : Saving the Data in a SQL Server Image Column to a File on the Hard Disk The code in this example opens a recordset on the pub_info table in the pubs database and saves the binary image data stored in the logo column of the first record to a file on the hard disk, as follows: Open a new Standard EXE Visual Basic project.On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.Place a CommandButton control on Form1.Make the following declarations in the form’s General declarations section:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream Cut and paste the following code into the Click event of the CommandButton that you added to the form:

Set cn = New ADODB.Connectioncn.Open “Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>”Set rs = New ADODB.Recordsetrs.Open “Select * from pub_info”, cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.Write rs.Fields(“logo”).Valuemstream.SaveToFile “c:\publogo.gif”, adSaveCreateOverWriters.Closecn.Close Save and run the Visual Basic project.Click the CommandButton to save the binary data in the logo column of the first record to the file c:\publogo.gid. Look for this file in Windows Explorer and open it to view the saved image.
The code in this example declares an ADODB Stream object and sets its Type property to adTypeBinary to reflect that this object will be used to work with Binary data. Following this, the binary data stored in the logo column of the first record in the pub_info table is written out to the Stream object by calling its Write method. The Stream object now contains the binary data that is saved to the file by calling its SaveToFile method and passing in the path to the file. The adSaveCreateOverWrite constant passed in as the second parameter causes the SaveToFile method to overwrite the specified file if it already exists.Example 2 : Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table The code in this example saves an image stored in a .gif file to the logo column in the first record of the pub_info table by overwriting its current contents, as follows: Open a new Standard EXE Visual Basic project.On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.Place a CommandButton on Form1. Make the following declarations in the form’s General declarations section:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream Cut and paste the following code in the Click event of the CommandButton that you added to the form:

Set cn = New ADODB.Connectioncn.Open “Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>”Set rs = New ADODB.Recordsetrs.Open “Select * from pub_info”, cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.LoadFromFile “<path to .gif file>”rs.Fields(“logo”).Value = mstream.Readrs.Updaters.Closecn.Close Save and run the Visual Basic project.Click on the CommandButton to run the code to stream the contents of the .gif file to the ADO Stream object, and save the data in the Stream to the logo column in the first record of the recordset.Verify that the image in the logo column has been modified by using the code in Example 1.

Symptoms
When you try to use the Query Designer against the MS Shape Provider inthe Data Environment, the following error message is returned:

Query Designer encountered a ADODB.Properties error:
Query Designer encountered a ADODB.Properties error:
Query Designer encountered a ADODB.Properties error:
ADO could not find the object in the collection corresponding to the
name or ordinal reference requested by the application.(11)Upon clicking OK, a second error message is returned:

Object Variable or With block variable not set.
Resolution
This behavior is by design.

Symptoms
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection. Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.
Resolution
The following code demonstrates using the SELECT @@Identity to retrieve the value of the newly inserted auto-increment field. The code snippet also includes code to create the table for the query.

Dim cnDatabase As ADODB.ConnectionDim rsNewAutoIncrement As ADODB.RecordsetDim strConn As StringDim strSQL As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnstrSQL = “CREATE TABLE AutoIncrementTest ” & _”(ID int identity, Description varchar(40), ” & _”CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “INSERT INTO AutoIncrementTest ” & _”(Description) VALUES (‘AutoIncrement Test’)”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “SELECT @@Identity”Set rsNewAutoIncrement = New ADODB.RecordsetrsNewAutoIncrement.Open strSQL, cnDatabase, adOpenForwardOnly, _adLockReadOnly, adCmdTextMsgBox “New Auto-increment value is: ” & rsNewAutoIncrement(0).ValuersNewAutoIncrement.CloseSet rsNewAutoIncrement = NothingstrSQL = “DROP TABLE AutoIncrementTest”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordscnDatabase.CloseSet cnDatabase = Nothing Thanks to this newly added functionality, you can see the newly added auto-increment values in your client-side ActiveX Data Objects (ADO) recordsets in ADO 2.1 and later. When you submit the new row to the Jet provider by calling Update or UpdateBatch (depending on your choice of LockType), the ADO cursor engine generates an INSERT INTO query to create the new row in the table. If the recordset contains an auto-increment field, ADO will also generate a SELECT @@Identity query to retrieve the value generated for that auto-increment field. The following code demonstrates this feature:

Dim cnDatabase As ADODB.ConnectionDim rsNewAutoIncrement As ADODB.RecordsetDim strConn As StringDim strSQL As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnstrSQL = “CREATE TABLE AutoIncrementTest ” & _”(ID int identity, Description varchar(40), ” & _”CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “SELECT ID, Description FROM AutoIncrementTest”Set rsNewAutoIncrement = New ADODB.RecordsetrsNewAutoIncrement.CursorLocation = adUseClientrsNewAutoIncrement.Open strSQL, cnDatabase, adOpenStatic, _adLockOptimistic, adCmdTextrsNewAutoIncrement.AddNewrsNewAutoIncrement(“Description”).Value = “AutoIncrement Test”rsNewAutoIncrement.UpdateMsgBox “New Auto-increment value is: ” & rsNewAutoIncrement(0).ValuersNewAutoIncrement.CloseSet rsNewAutoIncrement = NothingstrSQL = “DROP TABLE AutoIncrementTest”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordscnDatabase.CloseSet cnDatabase = Nothing You can create a new Jet 4.0 database using Microsoft Access 2000 or using the ADOX library that is included with MDAC 2.1. To use this library in your Visual Basic project, create a reference to Microsoft ADO Ext. 2.1 for DDL and Security. You can then use code like the following to create a new Jet 4.0 database:

Dim strPathToMDB As StringDim catNewDatabase As ADOX.CatalogstrPathToMDB = “C:\NewJet4.MDB”If Dir(strPathToMDB) <> “” ThenKill strPathToMDBEnd IfstrConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set catNewDatabase = New ADOX.CatalogcatNewDatabase.Create strConnSet catNewDatabase = Nothing To determine the format of your Microsoft Access database, check the dynamic “Jet OLEDB:Engine Type” property in the Connection object’s Properties collection. The property will return a value of 5 for Jet 4.x databases. The following code snippet demonstrates using the property:

Dim cnDatabase As ADODB.ConnectionDim strConn As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnIf cnDatabase.Properties(“Jet OLEDB:Engine Type”).Value = 5 ThenMsgBox “Jet 4.0 database”ElseMsgBox “Not a Jet 4.0 database”End IfcnDatabase.CloseSet cnDatabase = Nothing

Symptoms
When you use a single ADODB Connection object multiple times within an MTS transaction, other ADO objects must be cleaned up after use. Also, client cursors should be used when possible. If ADO objects are not cleaned up properly, ADO may raise an “Unspecified Error” [-2147467259 / 80004005] when it runs other operations against the database.
Resolution
When you use an open ADO Connection object on multiple operations within an MTS transaction, Microsoft recommends that you follow these steps:Either disconnect all open recordsets, or close and set all recordsets to nothing before executing other operations.Set all Command objects that are not used to nothing.If these steps are not followed, ADO might display an “Unspecified Error” or open secondary connections to complete the operations.
This situation arises more frequently when within an MTS transaction.
A simple example of code that FAILS when it is run within an MTS transaction as follows:
This is FAILING code:

Public Sub DoStuff()On Error Goto ErrHandlerDim oConn As New ADODB.ConnectionDim oCmd As New ADODB.CommandDim oRS As ADODB.RecordsetoConn.Open sConnectionStringSet oCmd.ActiveConnection = oConnoCmd.CommandText = “SELECT * FROM Authors”oCmd.CommandType = adCmdTextSet oRS= oCmd.Execute’…Operate on Recordset…’This FAILS if executed within an MTS transaction with ADO ‘UnspecifiedError’ message:oConn.Execute “INSERT INTO Jobs (job_desc, min_lvl, max_lvl) values (‘edjez’,25,100 )”GetObjectContext.SetCompleteExit SubErrHandler:GetObjectContext.SetAbortErr.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,Err.HelpContextEnd Sub The preceding code should be optimized and organized as follows:

Public Sub DoStuff()On Error Goto ErrHandlerDim oConn As ADODB.ConnectionDim oCmd As ADODB.CommandDim oRS As ADODB.RecordsetSet oConn = New ADODB.ConnectionoConn.Open sConnectionString’Here we specify cursor locations to adUseClient (3)’because we will be reusing the connection afterwardsSet oCmd = New ADODB.CommandSet oCmd.ActiveConnection = oConnoCmd.CommandText = “SELECT * FROM Authors”oCmd.CommandType = adCmdTextSet oRS= oCmd.Execute’…Operate on Recordset…’…and we won’t be needing it anymore so let’s clean upoRS.CloseSet oRS = NothingSet oCmd = NothingoConn.Execute “INSERT INTO Jobs (job_desc, min_lvl, max_lvl) values (‘edjez’,25,100 )”oConn.CloseSet oConn = NothingGetObjectContext.SetCompleteExit SubErrHandler:GetObjectContext.SetAbortErr.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,Err.HelpContextEnd Sub Please note that the error is raised to the caller within the error handler, and that the ADO objects are not created using ObjectContext.CreateInstance but rather the New operators – this is because ADO objects do not need to be created using ObjectContext.CreateInstance to participate in the current transaction (if any). If you are using a custom wrapper for database access (which in turn could be using ADO) you would need to create this wrapper using ObjectContext.CreateInstance for it to participate in the MTS transactions.

Symptoms
If you run an ADODB command stream and specify SQL SELECT with the FOR XML AUTO clause, an XML document stream is fetched from SQL Server and displayed in the Visual Basic Intermediate window.
You must set the following properties for the Properties collection of the ADODB.Command object: Output Stream. This property designates where the resulting XML data stream will be piped.Dialect. The dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. By setting the command language dialect, you specify how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ActiveX Data Objects (ADO). The dialect is specified by a globally unique identifier (GUID) and is set by using the Dialect property of the Command object.
Resolution
Create a new Visual Basic Standard EXE. Form1 is created by default. On the Project menu, select References, and then set a reference to Microsoft ActiveX Data Objects 2.6.Place a CommandButton on Form1, and then place the following code in its click event:
Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

Private Sub Command1_Click()Dim sConn As StringDim sQuery As StringDim outStrmsConn = “Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID=<username>;Password=<strong password>;”Dim adoConn As ADODB.ConnectionSet adoConn = New ADODB.ConnectionadoConn.ConnectionString = sConnadoConn.CursorLocation = adUseClientadoConn.OpenDim adoCmd As ADODB.CommandSet adoCmd = New ADODB.CommandSet adoCmd.ActiveConnection = adoConnsQuery = “<ROOT xmlns:sql=’urn:schemas-microsoft-com:xml-sql’>”sQuery = sQuery & “<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>”’sQuery = sQuery & “</ROOT>”Dim adoStreamQuery As ADODB.StreamSet adoStreamQuery = New ADODB.Stream’Open the command stream so it may be written toadoStreamQuery.Open’Set the input command stream’s text with the query stringadoStreamQuery.WriteText sQuery, adWriteChar’Reset the position in the stream, otherwise it will be at EOS.adoStreamQuery.Position = 0′Set the command object’s command to the input stream set above.Set adoCmd.CommandStream = adoStreamQuery’Set the dialect for the command stream to be a SQL query.adoCmd.Dialect = “{5D531CB2-E6Ed-11D2-B252-00C04F681B71}”‘Create the output stream to stream the results into.Set outStrm = CreateObject(“ADODB.Stream”)outStrm.Open’Set command’s output stream to the output stream just opened.adoCmd.Properties(“Output Stream”) = outStrm’Execute the command, thus filling the output stream.adoCmd.Execute , , adExecuteStream’Position the output stream back to the beginning of the stream.outStrm.Position = 0′Create temporary string.Dim str As String’Assign the stream’s output to the temp string to format.str = outStrm.ReadText(-1)’Add a cr/lf pair for each row in the result stream.str = Replace(str, “><”, “>” & vbCrLf & “<”)Debug.Print strGoTo ByeRecError:Debug.Print Err.Number & “: ” & Err.DescriptionBye:Set adoCmd = NothingIf adoConn.State = adStateOpen ThenadoConn.CloseEnd IfSet adoConn = NothingEnd Sub Specify either the SQL 2000 Server or, if the server is on your local machine, use the period symbol (.) or (local). Note that the Immediate window of Visual Basic displays the results.

Symptoms
This article describes, by example, how to implement a Visual Basic Component Object Model (COM) Object that returns a recordset to Active Server Pages (ASP).
Implementing this incorrectly can result in memory leaks or one of the following errors:

The operation requested by the application is not allowed if the object is closed. -or-

Type Mismatch -or-

error ‘ASP 0115′ – A trappable error occured in an external object
Resolution
Use the following steps to implement a method that returns a recordset from a Visual Basic COM Object to Active Server Pages: Create a Visual Basic ActiveX DLL project called PassRsPrj.Add a class module and change its name to PassRsObj.Implement the following method:
Note You must change the UID and the PWD values to the ones that are used on your system.

Public Function TestRs() as ADODB.RecordsetDim rsObj As ADODB.RecordsetDim cnObj As ADODB.Connectionset rsObj = New ADODB.Recordsetset cnObj = New ADODB.Connection’Open connection to databasecnObj.Open(“DSN=pubs;uid=<username>;pwd=<strong password>”)’To use disconnected Recordset you must use client side cursorsrsObj.CursorLocation = adUseClientrsObj.Open “select * from authors”, cnObj, adOpenKeyset, _adLockOptimistic, adCmdText’Disconnected recordsetSet rsObj.ActiveConnection = Nothing’Set return valueSet Testrs = rsObj’Clean up resourcescnObj.CloseSet cnObj = NothingEnd Function Create an ASP page with the following code:

<%Dim rsTest, oTestPassRsSet oTestPassRs = Server.CreateObject(“PassRsPrj.PassRsObj”)Set rsTest = oTestPassRs.TestRs()DoResponse.Write ( “Value in Record = ” & rsTest(1) & “<BR>” )rsTest.MoveNextLoop until rsTest.EOFrsTest.CloseSet rsTest = NothingSet oTestPassRs = Nothing%>