DOCUMENT:Q176561 11-NOV-1997 [vbwin] TITLE :PRB: Passing Timestamp Parameter Using User Connection Designer PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:5.0 OPER/SYS:WINDOWS KEYWORDS:vb5all kberrmsg ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0 --------------------------------------------------------------------- SYMPTOMS ======== When executing a query defined from the User Connection Object with a Timestamp data type as the input parameter of a Stored Procedure, the following error occurs: Run-time error '13' Type mismatch CAUSE ===== The User Connection Designer automatically recognizes the parameters of Stored Procedures by name, direction, ODBC binding data type, and Visual Basic's data type. However, the Designer thinks the Timestamp parameter is a Byte data type in Visual Basic, which is incorrect. In SQL Server, Timestamp is binary array; there is no corresponding counterpart in Visual Basic. RESOLUTION ========== In User Connection Designer, go to the Parameters tab of Query object properties page and change the Visual Basic Data Type of Timestamp input parameter from Byte to Variant. MORE INFORMATION ================ Steps to Reproduce Behavior --------------------------- Task One : Create the Test Table and Stored Procedure ----------------------------------------------------- 1. For testing purpose, table TimeStampTbl is created in Pubs database with one row inserted. In SQL Server, to create a table and add new row, select Pubs database, then place the following Create Table T-SQL in the SQL window of ISQL/W and execute it: CREATE TABLE TimestampTbl (ID int identity, name char(30) NULL, stamp Timestamp) INSERT INTO TimeStampTbl (name) Values("RoadRunner") 2. To create stored procedure, spID_Timestamp, place the following Create Procedure T-SQL in the SQL window of ISQL/W and execute it: CREATE PROCEDURE spID_Timestamp @theID int, @theTS TimeStamp As SELECT * FROM TimestampTbl WHERE ID=@theID AND stamp=@theTS Task Two: Create the UserConnection ----------------------------------- 1. Start a new project in Visual Basic and choose "Standard EXE". Form1 is created by default. 2. From the Project menu, select Components, select the Designer tab, and then place a check next to Microsoft UserConnection. 3. From the Project menu, select Add ActiveX Designer, and then select Microsoft UserConnection. This will bring up a dialog titled UserConnection1 Properties. (A reference to Remote Data Objects 2.0 will be automatically added to your project.) 4. On the Connection tab, select either a DSN or DSN-less connection and fill in the appropriate information. If you select DSN-less, make sure you specify a database in the Other ODBC Attributes area with database=Pubs. On the Authentication tab, fill in your username and password, and place a check next to Save Connection Information for Design Time. 5. Click OK to save this information and return to the Designer window. 6. Insert a new Query by right-clicking on UserConnection1 and choosing Insert Query or by clicking on Insert Query toolbar icon. 7. From the Source of Query, choose Based on Stored Procedure. From the pull-down list of the available stored procedures, select the one you just created, spID_Timestamp. 8. On the Parameters tab, note that the Visual Basic Data Type of input parameter, theTS, is default to Byte, which will cause the run-time error "Type mismatch." 9. Click OK to save this information. Task Three: Build the Visual Basic Code --------------------------------------- 1. Paste the following code into the General Declarations section of Form1: Private Sub Form_Load() Dim cn As New UserConnection1 Dim rs1 As rdoResultset Dim rs2 As rdoResultset Dim Timestamp As Variant cn.EstablishConnection Set rs1 = cn.OpenResultSet("Select ID, Stamp From TimeStampTbl") Timestamp = rs1(1) cn.Query1 rs1(0), Timestamp Set rs2 = cn.LastQueryResults Debug.Print "rs2(0): " & rs2(0) End Sub 2. Start the program or press the F5 key. REFERENCES ========== For additional information about User Connection Designer and TimeStamp datatype, please see the following articles in the Microsoft Knowledge Base: ARTICLE-ID: Q166281 TITLE : HOWTO: Create and Implement a UserConnection ARTICLE-ID: Q170380 TITLE : HOWTO: Display/Pass Timestamp Value from/to SQL Server (c) Microsoft Corporation 1997, All Rights Reserved. Contributions by Adrian Chiang, Microsoft Corporation ====================================================================== Keywords : vb5all kberrmsg Version : WINDOWS:5.0 Platform : WINDOWS Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 1997.