HOWTO: Call a Stored Procedure Using the Data Control

• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 5.0
• Microsoft Visual Basic Professional and Enterprise Editions, 16-bit and 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.

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.

Keywords          : APrgDataODBC VB4ALL VB4WIN vb5all VBKBDB VBKBODBC kbhowto
Version           : 4.0 5.0
Platform          : WINDOWS