KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q154758: HOWTO: Call a Stored Procedure Using the Data Control

Article: Q154758
Product(s): Microsoft Visual Basic for Windows
Version(s): 
Operating System(s): 
Keyword(s): kbGrpDSVBDB
Last Modified: 11-JAN-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Learning Edition for Windows, versions 5.0, 6.0 
- Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0 
- Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 
- Microsoft Visual Basic Professional Edition, 16-bit, for Windows, version 4.0 
- Microsoft Visual Basic Professional Edition, 32-bit, for Windows, version 4.0 
- Microsoft Visual Basic Enterprise Edition, 16-bit, for Windows, version 4.0 
- Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows, version 4.0 
-------------------------------------------------------------------------------

SUMMARY
=======

When writing prototype or demo applications, it may sometimes be necessary to
call a stored procedure on a SQL Server database from the Data Control. Because
the Data Control behaves similarly to a Recordset in DAO code, it is possible to
achieve this behavior.

MORE INFORMATION
================

In order to pass SQL Server syntax through the JET engine (the engine that
allows Visual Basic to connect to an Access databases), the dbSQLPassThrough
option must be used. This prevents the parser built into JET from attempting to
parse the SQL string.

This ability to pass through the JET engine allows the Visual Basic programmer to
call stored procedures on the SQL Server database. When using the Data Control,
you can set the SQLPassThrough option to use the Options property of the data
control.

The Options property Online Documentation describes dbSQLPassThrough as follows:

Constant          Value  Description
---------------------------------------------------------------------------
dbSQLPassThrough  64     When using Data controls with an SQL statement
                        in the RecordSource property, sends the SQL
                        statement to an ODBC database, such as a SQL
                        Server or Oracle database, for processing.

The complete list of valid settings for the Options property is documented in the
Online Help. Search there for Options Property.

Step-by-Step Example
--------------------

The following sample uses the pubs database in SQL Server to demonstrate the
pass-through behavior. This contains a stored procedure called "byroyalty" that
takes an integer and returns all authors that match the percentage royalty:

1. Start a new program in Visual Basic. Form1 is created by default.

2. Add a Data Control and a Text Box to Form1.

3. Set the DataSource property of Text1 to Data1.

4. Add the following code to Form1:

        Private Sub Form_Load()
           Data1.Connect = "ODBC;DSN=DSNToPubs;" & _
                           ";Database=pubs;" & _
                           "Uid=sa;Pwd=;"
           Data1.RecordSource = "Exec byroyalty 40"
           Data1.Caption = "Stored Procedure Test"
           Data1.Options = Data1.Options Or dbSQLPassThrough
           Data1.Refresh

           Text1.DataField = "Au_ID"
        End Sub

5. Press the F5 key to run the program. You should see the first author ID in
  the Text Box.

REFERENCES
==========

In Visual Basic Online Help, see Options property, OpenRecordset method.

Additional query words: kbVBp400 kbVBp500 kbVBp600 kbdse kbDSupport kbVBp

======================================================================
Keywords          : kbGrpDSVBDB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVBA500 kbVBA600 kbVB500 kbVB600 kbVB400Search kbVB400 kbVB16bitSearch
Issue type        : kbhowto

=============================================================================

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.