Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Return value from stored procedure (VB/DAO/SQL)

Posted on 1999-07-01
12
Medium Priority
?
707 Views
Last Modified: 2013-12-25
/* This VB routine errors out when it tries to do the OpenRecordset statement.*/
Option Explicit

Private Sub button1_click()
Dim strSQL, strResult As String
Dim qd As QueryDef
Dim rs As Recordset
Dim db As Database
On Error GoTo blah
Set db = OpenDatabase("TEST", dbDriverNoPrompt, False,"ODBC;DATABASE=IDB;DSN=TEST")

strSQL = "SET NOCOUNT ON"
strSQL = strSQL & vbCrLf & "DECLARE @intRet      int"
strSQL = strSQL & vbCrLf & "EXECUTE @intRet = idb_addnumbers 3, 5"
strSQL = strSQL & vbCrLf & "SELECT @intRet AS param1"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)

strResult = "intReturn = " & rs.Fields("param1")
MsgBox strResult

Exit Sub

blah:
Dim errLoop As Error
Dim strError As String
For Each errLoop In Errors
    With errLoop
        strError = "Error #" & .Number & vbCr
        strError = strError & "    " & .Description & vbCr
        strError = strError & "    (Source: " & .Source & ")" & vbCr
    End With
    MsgBox strError
Next
Resume Next

End Sub

/* This is the interactive SQL used to create the stored procedure. */
CREATE PROCEDURE dbo.sp_addnumbers
@intA int = 0,
@intB int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @intReturn int
SELECT  @intReturn = @intA + @intB
RETURN  @intReturn
END

/* This interactive SQL query works fine. */
SET NOCOUNT ON
DECLARE @intRet      int
EXECUTE @intRet = sp_addnumbers 3, 5
SELECT @intRet AS param1



0
Comment
Question by:jordanb
[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
12 Comments
 
LVL 3

Expert Comment

by:gmoriak
ID: 1506077
Your VB says

strSQL = strSQL & vbCrLf & "EXECUTE @intRet =      
    idb_addnumbers 3, 5"

shouldn't it be sp_addnumbers not idb_addnumbers?

0
 

Author Comment

by:jordanb
ID: 1506078
Oops, yes, it does say sp_addnumbers.  Sorry for the typo.

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1506079
What kind of error are you getting?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 5

Expert Comment

by:mayhew
ID: 1506080
Or did changing the typo fix it?
0
 

Author Comment

by:jordanb
ID: 1506081
Typo was not in original code.  Sorry.

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1506082
Error message?
0
 

Author Comment

by:jordanb
ID: 1506083
Run-time error 13  Type mismatch

0
 
LVL 15

Expert Comment

by:ameba
ID: 1506084
>Dim strSQL, strResult As String
is the same as:
Dim strSQL As Variant, strResult As String

0
 
LVL 3

Accepted Solution

by:
BGillham earned 800 total points
ID: 1506085
SQL is very specific about Data Typing:
Use the following to correct the error.
Var = CInt(IntVar)
or
Var = CStr(StrVar)
or whatever.


0
 
LVL 5

Expert Comment

by:mayhew
ID: 1506086
I think I see the problem.  I don't think you can execute multiple statements through an openrecordset method.

Have you tried putting everything in your SQL string into its own stored proc and just calling that from your program?
0
 

Author Comment

by:jordanb
ID: 1506087
Changing the code to:
Dim strSQL As String
Dim strResult As String
did not change anything, although I was glad for the tip!

Executing multiple SQL statements through an OpenRecordset
statement works fine for me usually.

0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6822598
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.

This is the Community Support link, if help is needed, along with the link to All Topics since many new ones were recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thanks,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Suggested Courses

688 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