How To Call SQL Stored Procedures from Visual Basic

Article translations Article translations
Article ID: 146651 - View products that this article applies to.
This article was previously published under Q146651
Expand all | Collapse all

SUMMARY

This article describes how to call Microsoft SQL stored procedures from Microsoft Visual Basic for Windows. 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. For example:
  1. Stored procedures that do not 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.
  2. Stored procedures that return records (or rows) require a Dynaset or Snapshot to capture the values. Listed below are two examples:

    Example Using a Data Control on a Visual Basic Form:
          Data1.Options = dbSQLPassThrough
          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 ignores the syntax used and passes the command through to the SQL server.

    Alternative Example Using Object Variables:
       Dim Ds as Recordset
       ' Open your desired database here.
       Set MyDB = DBEngine.Workspaces(0).OpenDatabase(...
       Set Ds = MyDB.OpenRecordset("sp_name", dbOpenDynaset, _
       dbSQLPassThrough)
    
    						
    You can also use 'dbOpenSnapshot' in place of 'dbOpenDynaset' 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:
          ' String specifying SQL.
          command.SQLx = "My_StorProc parm1, parm2, parm3"
          ...
          ' For stored procedure that doesn't return records.
          i = MyDB.ExecuteSQL(SQLx)
          ...
          'For stored procedure that returns records.
          set Ds = MyDB.OpenRecordset(SQLx, dbOpenDynaset, dbSQLPassThrough)
    
    						
    The object variable (Ds) contains the first set of results from the stored procedure (My_StorProc).

    Another Example

    The following contains more example code showing both methods:
          Dim db as Database
          Dim l as Long
          Dim Ss as Recordset
          Set Db = DBEngine.Workspaces(0).OpenDatabase _
          ("", False, False, "ODBC;dsn=yourdsn;uid=youruid;pwd=yourpwd:")
          ' For SPs that don't return rows.
          l=Db.ExecuteSQL("YourSP_Name")
          ' For SPs that return rows.
          Set Ss = Db.OpenRecordset("YourSP_Name", dbOpenSnapshot, _
           dbSQLPassThrough)
          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.

Properties

Article ID: 146651 - Last Review: June 29, 2004 - Revision: 2.1
APPLIES TO
  • 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
Keywords: 
kbhowto KB146651

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com