Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to call a stored procedure and capture the values it returned using vb.net in a console application

Posted on 2007-10-11
13
Medium Priority
?
263 Views
Last Modified: 2010-04-23
Hi I Have a stored procedure SP_abc and when this procedure is executed it returns me integer value.
This stored procedure take two parameters of type varchars.
Can I get vb.net code to call that storedprocedure and capture that returned value in a variable.Assume my connection string is Strconn

Thank you
0
Comment
Question by:Anushart
  • 6
  • 6
13 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20059628
SqlConnection Strconn = new SqlConnection(
               "server=<servername>;Integrated Security=SSPI;database=<databasename>");

SqlDataAdapter da = new SqlDataAdapter("EXEC SP_abc @param1, @param2",Strconn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet("<datasetname>");

Strconn.Open();
da.Fill(ds);

Strconn.Close();  
Grid1.DataSource = ds.Tables[0];
0
 
LVL 16

Expert Comment

by:TSmooth
ID: 20059634
Since you didn't specify, I'm going to assume you're using SQL Server/ SQL Server Express

At the top of your file:
Imports System.Data
Imports System.Data.SqlClient

In your code:
Dim conn As New SqlConnection(Strconn)
Dim cmd As New SqlCommand("SP_abc", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@param1", "Parameter1")
cmd.Parameters.AddWithValue("@param2", "Parameter2")

conn.Open()

Dim iReturn = cmd.ExecuteScalar()

conn.Close()

Note: This also assumes that based on what you said, you are only returning 1 integer with a select statement in the stored procedure. If you are using SQL Server's specialized return value then you need to do things a little bit differently.
0
 

Author Comment

by:Anushart
ID: 20060038
Hi Tsmooth

I have used your code but its giving me error as parameter required.Plugres and carrierid are my two parameters from my application.below is the code
         plugres = ds.Tables(1).Rows(0).Item("Plug_Resistor").ToString
        carrierid = ds.Tables(1).Rows(0).Item("CarrierNumber").ToString()
        Dim SQLCon As New SqlClient.SqlConnection
        SQLCon.ConnectionString = strConnection
         Dim cmd As New SqlCommand("SP_DATAEVAL1", SQLCon)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@param1", carrierid)
        cmd.Parameters.AddWithValue("@param2", plugres)

        SQLCon.Open()

        Dim iReturn = cmd.ExecuteScalar()

        SQLCon.Close()
0
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!

 
LVL 16

Expert Comment

by:TSmooth
ID: 20060103
You need to replace "@param1" and "@param2" with the names of the parameters as they are in the stored procedure. So it might be "@carrierid" and "@plugres".

Also note that I forgot to add an "As Integer" in my code. Should be:
 Dim iReturn As Integer  = cmd.ExecuteScalar()
0
 

Author Comment

by:Anushart
ID: 20060247
TSmooth  everything went well but i'm not getting expected result, My SP when executed with inputs as 10 &10000 returns 2 but when I run my application its returning 0 which v are storing in iReturn..Could you tell me where might have gone wrong
0
 

Author Comment

by:Anushart
ID: 20060499
hello SQL_SERVER_DBA I tried your code too but its giving me an error saying "Could not find stored procedure 'EXEC SP_DATAEVAL1 @carrierid, @plugres'." blow is the way I wote the code
plugres = ds.Tables(1).Rows(0).Item("Plug_Resistor").ToString
        carrierid = ds.Tables(1).Rows(0).Item("CarrierNumber").ToString()
        Dim SQLCon As New SqlClient.SqlConnection
        SQLCon.ConnectionString = strConnection
        da = New SqlDataAdapter("EXEC SP_DATAEVAL1 @carrierid, @plugres", SQLCon)
        da.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds1 As New DataSet
        SQLCon.Open()
        da.Fill(ds1)

        SQLCon.Close()
0
 
LVL 16

Expert Comment

by:TSmooth
ID: 20060966
Can you paste the code of your stored procedure please so I can see how the data is being returned?
0
 

Author Comment

by:Anushart
ID: 20061204
ALTER PROCEDURE dbo.DATAEVAL1_YESP
/*below are three inputs for my SP*/
      @carrierid as varchar(10),
      @plugresistor as Varchar(15)
         
       
AS

if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(2,3)
BEGIN
     If(@plugresistor =0)
     BEGIN
    return (-201)
    end
    Else
    declare @paravalue as varchar(10)
    set @paravalue = (SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM                 TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =2))
     If @paravalue >= @plugresistor * 0.98 And @paravalue <= @paravalue * 1.02
      BEGIN
/* should call a sub procedure
Me assume it as EXEC SP_DATAEVAL2 */
declare @recipetype as int

set @recipetype = (SELECT DISTINCT RECIPETYPE FROM TBLRECIPE WHERE (RECIPEID IN(SELECT RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid)AND CARRIERRECIPEISACTIVE = 'TRUE')))

      /* SET NOCOUNT ON */
      RETURN @recipetype

      end
Else
return (-201)
end
Else
BEGIN
set @recipetype = (SELECT DISTINCT RECIPETYPE FROM TBLRECIPE WHERE (RECIPEID IN(SELECT RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid)AND CARRIERRECIPEISACTIVE = 'TRUE')))

      /* SET NOCOUNT ON */
      RETURN @recipetype

 
end
..........................................................................................
Thats my SP code and I am trying to capture value in @recipetype
0
 

Author Comment

by:Anushart
ID: 20061221
ALTER PROCEDURE dbo.DATAEVAL1_YESP
/*below are two inputs for my SP*/
      @carrierid as varchar(10),
      @plugresistor as Varchar(15)
         
       
AS

if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(2,3)
BEGIN
     If(@plugresistor =0)
     BEGIN
    return (-201)
    end
    Else
    declare @paravalue as varchar(10)
    set @paravalue = (SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM                 TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =2))
     If @paravalue >= @plugresistor * 0.98 And @paravalue <= @paravalue * 1.02
      BEGIN
declare @recipetype as int

set @recipetype = (SELECT DISTINCT RECIPETYPE FROM TBLRECIPE WHERE (RECIPEID IN(SELECT RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid)AND CARRIERRECIPEISACTIVE = 'TRUE')))

      /* SET NOCOUNT ON */
      RETURN @recipetype

      end
Else
return (-201)
end
Else
BEGIN
set @recipetype = (SELECT DISTINCT RECIPETYPE FROM TBLRECIPE WHERE (RECIPEID IN(SELECT RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid)AND CARRIERRECIPEISACTIVE = 'TRUE')))

      /* SET NOCOUNT ON */
      RETURN @recipetype

 
end
..........................................
Check this i have removed unwanted comments
Thats my SP code and I am trying to capture value in @recipetypeback to top
0
 
LVL 16

Expert Comment

by:TSmooth
ID: 20061378
Ok, my code as is should work if you changed RETURN @recipetype to "SELECT @recipetype" but to make my code work with your sproc as is, change it to the following:

plugres = ds.Tables(1).Rows(0).Item("Plug_Resistor").ToString
        carrierid = ds.Tables(1).Rows(0).Item("CarrierNumber").ToString()
        Dim SQLCon As New SqlClient.SqlConnection
        SQLCon.ConnectionString = strConnection
        Dim cmd As New SqlCommand("SP_DATAEVAL1", SQLCon)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@carrierid", carrierid)
        cmd.Parameters.AddWithValue("@plugresistor", plugres)
        cmd.Parameters.Add("@recipetype", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

        SQLCon.Open()

        Dim iReturn As Integer

        ' Try this code first
        cmd.ExecuteNonQuery()
        iReturn = CInt(cmd.Parameters.Item("@recipetype").Value)


       ' Using dr As SqlDataReader = cmd.ExecuteReader()
        '    If dr.Read() Then
         '       iReturn = CInt(cmd.Parameters.Item("@recipetype").Value)
         '   End If
        'End Using

        SQLCon.Close()

0
 
LVL 16

Expert Comment

by:TSmooth
ID: 20061387
Ok, my code as is should work if you changed RETURN @recipetype to "SELECT @recipetype" but to make my code work with your sproc as is, change it to the following:

plugres = ds.Tables(1).Rows(0).Item("Plug_Resistor").ToString
        carrierid = ds.Tables(1).Rows(0).Item("CarrierNumber").ToString()
        Dim SQLCon As New SqlClient.SqlConnection
        SQLCon.ConnectionString = strConnection
        Dim cmd As New SqlCommand("SP_DATAEVAL1", SQLCon)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@carrierid", carrierid)
        cmd.Parameters.AddWithValue("@plugresistor", plugres)
        cmd.Parameters.Add("@recipetype", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

        SQLCon.Open()

        Dim iReturn As Integer

        ' Try this code first
        cmd.ExecuteNonQuery()
        iReturn = CInt(cmd.Parameters.Item("@recipetype").Value)

       ' Comment the above 2 lines and uncomment the following if the above does not return a value.
       ' Using dr As SqlDataReader = cmd.ExecuteReader()
        '    If dr.Read() Then
         '       iReturn = CInt(cmd.Parameters.Item("@recipetype").Value)
         '   End If
        'End Using

        SQLCon.Close()

NOTE: There are two methods in this code because I can't remember if "ExecuteNonQuery" grabs a return value or not. If it doesn't, follow the comments in the code and use the other method.
0
 
LVL 16

Accepted Solution

by:
TSmooth earned 2000 total points
ID: 20061392
Sorry for the double post, hit the wrong key. Ignore the first one as it is incomplete and use the one directly above this.
0
 

Author Comment

by:Anushart
ID: 20065029
Thanks alot Tsmooth ..It worked ...actually I changed my SP to Select @recipetype which was easy and it worked :) thanks once again for your support
0

Featured Post

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.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

810 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