Solved

RDO and Stored Procedures (SQL Server)

Posted on 1997-11-27
5
312 Views
Last Modified: 2011-04-14
<NT4, VB5, SQL-Server 6.5>
Hi everybody!
I have a problem using RDO and stored procedures.
From VB5 I execute this piece of code to retrieve a resultset. I have one in-parameter.

------------------------------------------------------------
Set rsRes = rdCon.OpenResultset("exec spTest 'DEM',
                     rdOpenDynamic,rdConcurReadOnly)
------------------------------------------------------------

The stored procedure "spTest" looks like this:
------------------------------------------------------------
CREATE PROCEDURE spTest
@isokod char (3)
AS
IF @isokod='ALL'
  BEGIN
    select isokod, afnr, sub
    from tblSebtrade
    order by isokod
  END
ELSE
  BEGIN
    select isokod, afnr, sub
    from tblSebtrade
    where isokod=@isokod
    order by isokod
  END
GO
------------------------------------------------------------

And the errormessage :) is:
------------------------------------------------------------
Run-time 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 statment in it.
------------------------------------------------------------

I understand the errormessage allright but I have done things like this before (not using RDO). What am I doing wrong this time?
I suspect that I can write the SP in a different way in
order to achieve what I want, but I don't know how...
I would really appreciate help!

                     Best regards // Anders Karlsson
0
Comment
Question by:Kalle 2
  • 3
  • 2
5 Comments
 

Author Comment

by:Kalle 2
ID: 1442690
Edited text of question
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1442691
I might be way of base here, but couldn't you just provide a SQL-statement instead of running it as a stored procedure ?

Something like this

Myisokod="DEM" ' or whatever

SqlStatement="select isokod, afnr, sub from tblSebtrade where '" & MyIsokod & "' = 'ALL' OR isokod='" & MyIsokod & "' order by isokod "

Set rsRes = rdCon.OpenResultset(SqlStatement, rdOpenDynamic,rdConcurReadOnly)


Trygve
0
 

Author Comment

by:Kalle 2
ID: 1442692
Yes I could do that but I dont want to! :)
I have other, more complex, procedures I want to execute
and they all use this technique. I should work and I
cant seem to find the problem.

0
 
LVL 12

Accepted Solution

by:
Trygve earned 150 total points
ID: 1442693
It seems to me that you are using a cursor on your connection.

Try using a cursorless result set;   rdCon.CursorDriver = rdUseNone


Another solution could be to let the stored procedure produce a temporary table on the server use another openresultset to get the data and then delete the temp table.
0
 

Author Comment

by:Kalle 2
ID: 1442694
I changed the cursortype as you mentioned and it worked
just fine.
This solved my problem and got me going again, thanks!

                                        // Anders Karlsson
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Determine Range to Select 5 52
Exit a vb6 apps when a calling it apps closes 15 52
VBA Shell can't Find Word document 11 112
Create Files based on Cell Values in a Range in Excel 12 43
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question