Microsoft KB Archive/154758

= How To Call a Stored Procedure Using the Data Control =

Article ID: 154758

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q154758



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:

 Start a new program in Visual Basic. Form1 is created by default. Add a Data Control and a Text Box to Form1. Set the DataSource property of Text1 to Data1.  Add the following code to Form1.

Note You must change UID = and PWD = to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. Private Sub Form_Load Data1.Connect = "ODBC;DSN=DSNToPubs;" & _ ";Database=pubs;" & _ "Uid= ;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

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

<div class="references_section">