Microsoft KB Archive/106492: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 55: | Line 55: | ||
<ol> | <ol> | ||
<li><p>Stored procedures that don't return records (or rows) can be executed from Visual Basic with the ExecuteSQL method as follows:</p> | <li><p>Stored procedures that don't return records (or rows) can be executed from Visual Basic with the ExecuteSQL method as follows:</p> | ||
<pre class="codesample"> i% = MyDb.ExecuteSQL( | <pre class="codesample"> i% = MyDb.ExecuteSQL("sp_name") | ||
</pre> | </pre> | ||
<p>This executes the stored procedure sp_name and returns the affected number of rows in i%. The ExecuteSQL method is strictly for action queries such as:</p> | <p>This executes the stored procedure sp_name and returns the affected number of rows in i%. The ExecuteSQL method is strictly for action queries such as:</p> | ||
<pre class="codesample"> Delete Authors where name like | <pre class="codesample"> Delete Authors where name like "fred%" | ||
</pre> | </pre> | ||
<p>The ExecuteSQL method is valid only for SQL statements that do not return records (or rows). An SQL statement that uses | <p>The ExecuteSQL method is valid only for SQL statements that do not return records (or rows). An SQL statement that uses "SELECT..." returns records, while an SQL statement that uses "DELETE..." does not. Neither Execute nor ExecuteSQL return a recordset, so using ExecuteSQL on a query that selects records produces an error.</p></li> | ||
<li><p>Stored procedures that return records (or rows) require a Dynaset or Snapshot to capture the values. Here are two examples:<br /> | <li><p>Stored procedures that return records (or rows) require a Dynaset or Snapshot to capture the values. Here are two examples:<br /> | ||
<br /> | <br /> | ||
Line 68: | Line 68: | ||
<pre class="codesample"> DB_SQLPassThrough = 64 | <pre class="codesample"> DB_SQLPassThrough = 64 | ||
Data1.Options = DB_SQLPassThrough | Data1.Options = DB_SQLPassThrough | ||
Data1.Recordsource = | Data1.Recordsource = "sp_name" ' name of the stored procedure | ||
Data1.Refresh ' Refresh the data control | Data1.Refresh ' Refresh the data control | ||
Line 77: | Line 77: | ||
<pre class="codesample"> Dim Ds as Dynaset | <pre class="codesample"> Dim Ds as Dynaset | ||
Set MyDB = OpenDatabase(... ' Open your desired database here. | Set MyDB = OpenDatabase(... ' Open your desired database here. | ||
Set Ds = MyDB.CreateDynaset( | Set Ds = MyDB.CreateDynaset("sp_name",Db_SQLPassThrough) | ||
' You can also Dim as Snapshot and use MyDb.CreateSnapshot above. | ' You can also Dim as Snapshot and use MyDb.CreateSnapshot above. | ||
Line 86: | Line 86: | ||
To pass parameters, include them after the name of the stored procedure in a string, for example:<br /> | To pass parameters, include them after the name of the stored procedure in a string, for example:<br /> | ||
<pre class="codesample"> SQLx = | <pre class="codesample"> SQLx = "My_StorProc parm1, parm2, parm3" ' String specifying SQL | ||
' command. | ' command. | ||
... | ... | ||
Line 105: | Line 105: | ||
' Enter the following two lines as one, single line: | ' Enter the following two lines as one, single line: | ||
Set Db = OpenDatabase | Set Db = OpenDatabase | ||
( | ("",false,false, "ODBC;dsn=yourdsn;uid=youruid;pwd=yourpwd") | ||
l=ExecuteSQL( | l=ExecuteSQL("YourSP_Name") ' for SPs that don't return rows | ||
Set Ss = Db.CreateSnapshot( | Set Ss = Db.CreateSnapshot("YourSP_Name", 64) ' for SPs that return rows | ||
Col1.text = Ss(0) ' Column one | Col1.text = Ss(0) ' Column one | ||
Col2.text = Ss!ColumnName | Col2.text = Ss!ColumnName | ||
Col3.Text=Ss( | Col3.Text=Ss("ColumnName") | ||
</pre> | </pre> | ||
Latest revision as of 10:21, 20 July 2020
Article ID: 106492
Article Last Modified on 10/20/2003
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q106492
SUMMARY
This article describes how to call Microsoft SQL stored procedures from Visual Basic. A stored procedure is a precompiled collection of SQL statements, often including control-of-flow language.
MORE INFORMATION
The method of calling depends on whether the SQL stored procedure returns records or not:
Stored procedures that don't return records (or rows) can be executed from Visual Basic with the ExecuteSQL method as follows:
i% = MyDb.ExecuteSQL("sp_name")
This executes the stored procedure sp_name and returns the affected number of rows in i%. The ExecuteSQL method is strictly for action queries such as:
Delete Authors where name like "fred%"
The ExecuteSQL method is valid only for SQL statements that do not return records (or rows). An SQL statement that uses "SELECT..." returns records, while an SQL statement that uses "DELETE..." does not. Neither Execute nor ExecuteSQL return a recordset, so using ExecuteSQL on a query that selects records produces an error.
Stored procedures that return records (or rows) require a Dynaset or Snapshot to capture the values. Here are two examples:
Example Using a Data Control on a Visual Basic Form:DB_SQLPassThrough = 64 Data1.Options = DB_SQLPassThrough Data1.Recordsource = "sp_name" ' name of the stored procedure Data1.Refresh ' Refresh the data control
When you use the SqlPassThrough bit, Visual Basic's Microsoft Access database engine will ignore the syntax used and will pass the command through to the SQL server.
Alternative Example Using Object Variables:Dim Ds as Dynaset Set MyDB = OpenDatabase(... ' Open your desired database here. Set Ds = MyDB.CreateDynaset("sp_name",Db_SQLPassThrough) ' You can also Dim as Snapshot and use MyDb.CreateSnapshot above.
How to Pass Parameters to a Stored Procedure
To pass parameters, include them after the name of the stored procedure in a string, for example:
SQLx = "My_StorProc parm1, parm2, parm3" ' String specifying SQL ' command. ... i = MyDB.ExecuteSQL(SQLx) ' For stored procedure that ' doesn't return records. ... set Ds = MyDB.CreateDynaset(SQLx,64) ' For stored procedure that ' returns records.
The object variable (Ds) will contain the first set of results from the stored procedure (My_StorProc).
Another Example
Here's more example code showing both methods:
Dim db as Database; l as long; Ss as Snapshot ' Enter the following two lines as one, single line: Set Db = OpenDatabase ("",false,false, "ODBC;dsn=yourdsn;uid=youruid;pwd=yourpwd") l=ExecuteSQL("YourSP_Name") ' for SPs that don't return rows Set Ss = Db.CreateSnapshot("YourSP_Name", 64) ' for SPs that return rows Col1.text = Ss(0) ' Column one Col2.text = Ss!ColumnName Col3.Text=Ss("ColumnName")
REFERENCES
More information about calling stored procedures is documented in the following Microsoft SQL manual which covers the Visual Basic Library for SQL Server:
- Microsoft SQL Server Programmer's Reference for Visual Basic
See the functions SqlRpcInit% (pages 200-201), SqlRpcParam%, and SqlRpcSend%. These functions call stored procedures more quickly than do the methods described above.
Additional query words: 3.00
Keywords: KB106492