Solved

RDO and Stored Procedures (SQL Server)

Posted on 1997-11-27
5
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses
Course of the Month3 days, 11 hours left to enroll

630 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