Visual Basic Q&A

Symptoms
SQL Server stored procedures are capable of returning more than one recordset and the Remote Data Object (RDO) has the ability to access these Multiple Resultsets.
When calling these stored procedures, the following error can be encountered:

Error 40002
“37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open a cursor on a stored procedure that has anything other than a single
select statement in it” The following code sample showing how to return the multiple Resultsets using the MoreResults Property of the RDO.
Resolution
There are two methods to avoid Error 40002.
Method 1 Use The ODBC Cursor library rather than Server Side Cursors. To do this, use the following code:

rdoEngine. rdoDefaultCursorDriver = rdUseODBC
- or -

rdoEnvironments(0).CursorDriver = rdUseOdbc This option gives better performance for small result sets, but may degrade quickly for larger result sets depending on the Server and workstation configuration.
NOTE: If you are using SQL text fields, you must use Server-side cursors to bypass Error 40002″

Method 2 Use Server Side Cursors, a Forward Only Cursor, and a rowset size of 1. Make the server create a cursor-less resultset on the server side by using a forward only cursor and a RowSetSize of 1.
The following code sample illustrates how to create a stored procedure that returns multiple result sets using method 2. Create the stored procedure on SQL Server:
Start the ISQL utility that shipped with SQL server. Connect to your SQL server. Select the pubs database from the combo box labeled DB. Enter the following lines into the Query Tab:

CREATE PROCEDURE TestMultiResults ASselect * from authorsselect * from discountsGO Choose the Query|Execute menu. The Results tab should display:
This command did not return data, and it did not return any rows
A stored procedure called TestMultiResults has now been created in the pubs database on SQL Server. Create the Visual Basic client to call the stored procedure:
Start Visual Basic. Form1 is created by default. Add a Command Button (Command1) and a List Box (List1) to Form1. Add the following code to Form1:

Private Sub Form_Load()Command1.Caption = “Run Stored Procedure”End SubPrivate Sub Command1_Click()Dim cn As rdoConnectionDim ps As rdoPreparedStatementDim rs As rdoResultsetDim strConnect As String’set cursor driver to use server-side cursorsrdoDefaultCursorDriver = rdUseServer’open a connection to the pubs database using DSNless connections’Remember to change the following connection string parameters to reflect the correct valuesstrConnect = “Driver={SQL Server}; Server=myServer; ” & _”Database=pubs; Uid=<username>; Pwd=<strong password>”Set cn = rdoEnvironments(0).OpenConnection(dsName:=”", _Prompt:=rdDriverNoPrompt, _ReadOnly:=False, _Connect:=strConnect)’create a prep stmt for the stored proc callSet ps = cn.CreatePreparedStatement(“MyPs”, _”{call TestMultiResults}”)’set the RowSet size to 1ps.RowsetSize = 1′open the resultset with forward-only cursorSet rs = ps.OpenResultset(rdOpenForwardOnly)’add the first resultset to a list boxWhile Not rs.EOFlist1.AddItem rs(“au_fname”) & ” ” & rs(“au_lname”)rs.MoveNextWend’move to the second resultsetrs.MoreResultslist1.AddItem “Second Resultset Below”‘add the second resultset to the same list boxWhile Not rs.EOFlist1.AddItem rs(“discounttype”) & ” = ” & rs(“discount”)rs.MoveNextWend’Close the resultset and the connection and set both to nothingrs.CloseSet rs = Nothingcn.CloseSet cn = NothingEnd Sub Run the project and click the “Run Stored Procedure” button. You should see a list of Authors and then Discounts in the list box.

Symptoms
The UserConnection Designer is one of the new ActiveX Designers includedwith Visual Basic 5.0 and higher. It allows you to wrap SQL or a StoredProcedure with code at design time so you can call it as a method at runtime. This way, Visual Basic will list the parameters of your storedprocedure in the object browser just as it does with any other VBA method.This new functionality can replace the rdoPreparedStatement and rdoQueryobjects, although these are still available.
The following is an excerpt on the UserConnection from Books Online:
The UserConnection designer uses Visual Basic’s ActiveX designerarchitecture to provide design-time support for programmatic dataaccess. It allows you to create connection and query objects atdesign time. These connections and queries are persisted as project-levelobjects. You can pre-set properties, define new properties and methods,and write code behind the objects to catch events.
Resolution
This sample uses SQL Server and the Pubs database, but you can change theDSN, UID, PWD, and SQL to match any ODBC database you may be using.
Task One: Create the Example Stored ProcedureThis sample SQL Server stored procedure accepts an input parameter ofau_id and returns a resultset and a test output variable. The testoutput variable is hard-coded and has no meaning except to demonstratehow to return an output using a UserConnection. If you are not usingSQL Server, you might have to modify the SQL into an acceptable formatfor your database server.To create this stored procedure you will need a tool that allows you toexecute SQL such as ISQL/W or MSQuery included with SQL Server. Selectthe Pubs database, then place the following Create Procedure T-SQL inthe SQL window of your selected tool and execute it:

Create procedure GetAuthorInfo@au_id Varchar(11), @testOut Varchar(10) OUTPUT AsSelect * from authors Where au_id = @au_idSelect @testOut = ‘Hello’
Task Two: Create the UserconnectionStart a new project in Visual Basic and choose “Standard EXE.” Form1 iscreated by default.From the Project menu, select Components, select the Designer tab, andthen place a check next to Microsoft UserConnection.From the Project menu, select Add ActiveX Designer, then selectMicrosoft UserConnection. This will bring up a dialog titledUserConnection1 Properties.On the Connection tab, select either a DSN or a DSN-Less connection andfill in the appropriate information. If you select DSN-less, make sureyou specify a database in the Other ODBC Attributes area withdatabase=pubs. On the Authentication tab, fill in your username andpassword, and place a check next to Save Connection Information forDesign time. On the Miscellaneous tab in the Other section, choose UseODBC Cursor Library because you have more than one Select statement inyour stored procedure.Click OK to save this information, and return to the Designer window.Press the F4 key to display the Properties window and change the Nameproperty from UserConnection1 to StoredProcs.Insert a new Query by right-clicking on StoredProcs and choosingInsert Query or by clicking on the Insert Query toolbar icon. Change thename of the Query from Query1 to GetAuthorInfo. From the Source ofQuery, choose Based on Stored Procedure, then select the storedprocedure you created earlier, GetAuthorInfo.If you select the Parameters tab, you can review the input and outputparameters. Your GetAuthorInfo Query setup is now finished. ClickOK to close the Dialog.To place code in the connection event of your UserConnection, selectView Code from the UserConnection toolbar. Choose UserConnection infrom the upper-left combo box, then choose the Connect Event from theupper-right combo box. Place the following code in the UserConnectionConnect event:

Debug.Print “Connect”
Task Three: The Userconnection CodeAdd a CommandButton, Command1, to Form1 of Project1.Paste the following code in the General Declarations section ofForm1:

Private Sub Command1_Click()Dim objSP As New StoredProcs’creatable UC objectDim objRs As rdoResultset’pointer to rdoResultsetDim lngRet As Long, strOut As String’vars to hold outputobjSP.EstablishConnection rdDriverNoPrompt’establish connectionlngRet = objSP.GetAuthorInfo(“648-92-1872″, strOut) ‘SP methodSet objRs = objSP.LastQueryResults’get results off objRsDebug.Print lngRet, strOut, objRs(0)’output example resultsEnd Sub Start the program or press the F5 key.Click the Command1 button to execute the UserConnection code. TheStored Procedure return value, output value, and the first column ofthe returned resultset will print in the Debug window.

Symptoms
This article describes how to use Data Access Objects (DAO) to callMicrosoft SQL stored procedures from Microsoft Visual Basic for Windows. Astored procedure is a precompiled collection of SQL statements, oftenincluding control-of-flow language.
NOTE: This article assumes you already know how to open an ODBC database.
Resolution
The method of calling depends on whether the SQL stored procedure returnsrecords or not. For example:
Stored procedures that do not return records (or rows) can be executedfrom Visual Basic with the Execute method in Visual Basic as follows:

MyDb.Execute “sp_name”, dbSQLPassThroughi = MyDb.RowsAffectedYou can also use ExecuteSQL:

i = MyDb.ExecuteSQL(“sp_name”)However, this syntax is obsolete, and you should replace it with theExecute method and RowsAffected property syntax given at the beginningof this section.
The Execute (and ExecuteSQL) method runs the stored procedure sp_name.The RowsAffected property returns the number of rows the storedprocedure affected. This method is strictly for action queries such as:

Delete Authors where name like “fred%”Using Execute with an SQL statement that uses “SELECT…” returnsrecords that causes a run-time error.Stored procedures that return records (or rows) require a Snapshot-typeRecordset to capture the values. Listed below are two examples:
Example 1The following example using a Data Control on a Visual Basic Form:

Data1.Options = dbSQLPassThroughData1.Recordsource = “sp_name”‘ Name of the stored procedure.Data1.Refresh’ Refresh the data control.When you use the SQLPassThrough bit, the Microsoft Jet database engineignores the syntax used and passes the command through to the SQLserver.
Example 2Using Data Access Objects:

Dim Rs as Recordset’ Open your desired database here.Set MyDB = DBEngine.Workspaces(0).OpenDatabase(…Set Rs = MyDB.OpenRecordset(“sp_name”, dbOpenSnapshot, _dbSQLPassThrough) You must use dbOpenSnapshot. dbOpenDynaset and dbOpenTable do notapply to pass-through queries.
How to Pass Parameters to a Stored ProcedureTo pass parameters, include them after the name of the stored procedurein a string. For example:

‘ String specifying SQL.SQL = “My_StorProc parm1, parm2, parm3″…’ For a stored procedure that doesn’t return records.MyDb.Execute SQL, dbSQLPassThroughi = MyDb.RowsAffected…’For a stored procedure that returns records.set Rs = MyDB.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough) The object variable (Rs) contains the first set of results from thestored procedure (My_StorProc).
Another ExampleThe following contains more example code showing both methods:

Dim db as DatabaseDim l as LongDim Rs as RecordsetSet Db = DBEngine.Workspaces(0).OpenDatabase _(“”, False, False, “ODBC;dsn=yourdsn;uid=youruid;pwd=yourpwd:”)’ For SPs that don’t return rows.Db.Execute “YourSP_Name”, dbSQLPassThroughl = Db.RowsAffected’ For SPs that return rows.Set Rs = Db.OpenRecordset(“YourSP_Name”, dbOpenSnapshot, _dbSQLPassThrough)Col1.text = Rs(0) ‘ Column one.Col2.text = Rs!ColumnNameCol3.Text = Rs(“ColumnName”)

Symptoms
This article describes how to call system-stored procedures on SQL Serverfrom RDO.
SQL Server has a number of prewritten, stored procedures that are used toconfigure and administer the SQL server. They are all located in the Masterdatabase and have the prefix [ASCII 147]sp_,[ASCII 148] which distinguishes them fromuser- or application-written stored procedures.
In order to call these stored procedures from RDO, you must specify thecorrect database in which they reside, the master database. There areseveral ways to do this, but the most effective way is to explicitlyreference the stored procedure in your call syntax. For example:

{ ? = call master.dbo.sp_addlogin(?,?) }
Another method to accomplish this behavior is to set the default databasebefore creating and executing your stored procedure. The following exampleuses a prepared statement and assumes that “Pubs” is your default database,cn is an active rdoConnection object, and qr is an rdoQuery object:

cn .Execute “Use Master”qr.SQL= “some sql to execute…”Set qr.ActiveConnection = cnqr .Executecn .Execute “Use Pubs”
Resolution
Start a new Standard EXE project. Form1 is created by default.Add a CommandButton (Command1) to Form1.From the Project menu, select References, and place a check next toMicrosoft Remote Data Object 2.0.Paste the following code into the General Declarations section of Form1:

Private Sub Command1_Click()Dim cn As New rdoConnectionDim qr As New rdoqueryWith cn.Connect = “Driver={SQL Server};Server=MyServer;” & _”Database=pubs;Uid=<username>;Pwd=<strong password>”.EstablishConnection Prompt = rdDriverNoPromptEnd WithWith qrSet .ActiveConnection = cn’Note: If you don’t specify master, you will get the following’error: “An invalid parameter was passed.”.SQL = “{ ? = call master.dbo.sp_addlogin(?,?) }”.Prepared = True.BindThreshold = 1024 ‘largest column that will be bound under’ODBC.Debug.Print .rdoParameters.Count.rdoParameters(0).Direction = rdParamOutput.rdoParameters(1).Direction = rdParamInput.rdoParameters(2).Direction = rdParamInput.rdoParameters(1) = “Edward”.rdoParameters(2) = “Scissorhands”.ExecuteDebug.Print .rdoParameters(0).Value’Const SQL_SUCCESS As Long = 0′Const SQL_ERROR As Long = -1End WithEnd Sub Note that you need to change your DATABASE, UID, and PWD parameters inthe Connect Property.Start the program or press the F5 key.Click the Command1 button to execute the stored procedure and displaythe parameter count and the output parameter in the debug window.

Symptoms
If multiple stored procedures are executed when using ADO 2.0, the T-SQL Debugger automaticallystarts for the first stored procedure, but does not automatically startupon executing the second or subsequent stored procedures. The followingerror message displays:

The query could not be debugged due to a problem coordinatingevents with the server. Check the server and client log to find theexact cause, fix the problem and try again.This problem no longer occurs in ADO 2.1 and later when calling simple stored procedures like the pubs..reptq1 procedure that is called in the example code below.However, if you are calling multiple stored procedures that accept parameters, using a single ADO Connection, the T-SQL Debugger does not start automatically when executing the second or subsequent stored procedures.You will either receive the error described above, or it will fail silently.
Resolution
In ADO 2.1 and later, to temporarily work around this while debugging, execute each store procedure on a separate connection. This workaround is illustrated in “Steps to Reproduce Behavior Using ADO 2.1″ that follows.