How To Call SQL Stored Procedures from Visual Basic
- Stored procedures that do not return records (or rows) can be executed from Visual Basic with the ExecuteSQL() method as follows: 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:
i% = MyDb.ExecuteSQL("sp_name")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.
Delete Authors where name like "fred%"
- 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: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.
Data1.Options = dbSQLPassThrough Data1.Recordsource = "sp_name" ' name of the stored procedure. Data1.Refresh ' Refresh the data control.
Alternative Example Using Object Variables:You can also use 'dbOpenSnapshot' in place of 'dbOpenDynaset' above.
Dim Ds as Recordset ' Open your desired database here. Set MyDB = DBEngine.Workspaces(0).OpenDatabase(... Set Ds = MyDB.OpenRecordset("sp_name", dbOpenDynaset, _ dbSQLPassThrough)
How to Pass Parameters to a Stored ProcedureTo pass parameters, include them after the name of the stored procedure in a string. For example:The object variable (Ds) contains the first set of results from the stored procedure (My_StorProc).
' 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)
Another ExampleThe 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")
- "Microsoft SQL Server Programmer's Reference for Visual Basic"
Article ID: 146651 - Last Review: 12/04/2015 14:10:26 - Revision: 2.1
- kbnosurvey kbarchive kbhowto KB146651