Solved

Visual Basic 2003 get return value from sql server stored procedure

Posted on 2011-02-18
5
632 Views
Last Modified: 2012-05-11
I am trying to call a stored procedure from some Visual Basic 2003 code and I cant figure out what I am doing incorrectly in the VB Code. The stored procedure that I am calling is returning a value. I want to evaluate what the stored procedure has returned.  

*************************Here is my Visual Basic 2003 code*************

Private Function bolEligibility() As Boolean
        Dim strSql1 As New StringBuilder
        Dim strMessage as string
        strSql1.Append("exec uspCHK_Eligibility  '" & ApplNo & "'")
        Dim cnnConnection1 As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
        Dim objCommand1 As SqlCommand = New SqlCommand(strSql1.ToString(), cnnConnection1)
        Dim objDataAdapter1 As SqlDataAdapter = New SqlDataAdapter
        Dim objDataSet1 As DataSet = New DataSet
        objCommand1.CommandType = CommandType.Text
        objDataAdapter1.SelectCommand = objCommand1
        objDataAdapter1.Fill(objDataSet1)        
        If objDataSet1.Tables(0).Rows(0)("App_No").ToString() <> "-1" Then
            Return True
        Else
             strMessage = "not eligible."
            Return False
        End If
    End Function

*****Here is how the SQL SERVER stored procedure looks**********

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[uspCHK_Eligibility]    Script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspCHK_Eligibility]
(
      @ApplNo numeric(18,0)
)
AS
BEGIN
      SET NOCOUNT ON;
      IF EXISTS (SELECT * FROM tblLoan_Appl (NOLOCK) WHERE Appl_No = @ApplNo AND Status = 'N')
            Return @ApplNo
      ELSE
            Return -1
END



0
Comment
Question by:michiganblkman
5 Comments
 
LVL 7

Accepted Solution

by:
mkobrin earned 167 total points
ID: 34924881
change this
BEGIN
      SET NOCOUNT ON;
      IF EXISTS (SELECT * FROM tblLoan_Appl (NOLOCK) WHERE Appl_No = @ApplNo AND Status = 'N')
            Return @ApplNo as App_No
      ELSE
            Return -1 as App_No
END
 your app is looking for a name App_No and @ApplNo will not return a name
0
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 167 total points
ID: 34924933
Another thing:

objCommand1.CommandType = CommandType.Text
 
must be

objCommand1.CommandType = CommandType.StoredProcedure
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 166 total points
ID: 34924942
stored procedures can only return integers via the return code method....

you are trying to return a numeric...


you appear to be trying to access the value as it if had been returned as a result set...

in which case

IF EXISTS (SELECT * FROM tblLoan_Appl (NOLOCK) WHERE Appl_No = @ApplNo AND Status = 'N')
            Return @ApplNo
      ELSE
            Return -1


should be written as

    Select case when not exists ((SELECT * FROM tblLoan_Appl (NOLOCK) WHERE Appl_No = @ApplNo AND Status = 'N'))
                      then -1
                     else @applid end as applid
0
 

Author Comment

by:michiganblkman
ID: 34925146
Thanks for the rapid and insightful assistance so far!

How can I change my code to access the return value without makeing changes to the stored procedure?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34925174
Another remark: when you just return one value, you should use executeScalar
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now