VB.Net - Call S/P with Input and multiple Output Parameters

Posted on 2012-08-27
Last Modified: 2012-08-28
Good Day Experts!

I have a project that I need to have the queries  converted over to reside in StoredProcedures.  There will be two Input paramters and two Ouput parameters.

I have looked over the net and could not find a "good" atricle on how to achieve this.  Can you suggest a good article or reference to help me achieve this?

Question by:Jimbo99999
    LVL 13

    Assisted Solution

    by:Jesus Rodriguez
    You can do this

    Con = New SqlConnection("Server=(local); Initial Catalog=onlinejob; User ID=sa; Password=sa")
    Cmd = New SqlCommand
    Cmd.CommandText = "InsertUser"
    Cmd.CommandType = CommandType.StoredProcedure

    Cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50) 'First PArameter
    Cmd.Parameters("@UserName").Value = "username"

    Cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)  'Second PArameter
    Cmd.Parameters("@Password").Value = password

    Cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)  'Third Parameter
    Cmd.Parameters("@FirstName").Value = firstname

    Cmd.Parameters.Add("@Opp1", SqlDbType.Int)   'First Output Parameter
    Cmd.Parameters("@Opp").Direction = ParameterDirection.Output

    Cmd.Parameters.Add("@Opp2", SqlDbType.Int)   'Second Output Parameter
    Cmd.Parameters("@Opp2").Direction = ParameterDirection.Output

    Cmd.Connection = Con
    Dim Result1 as Integer= Cmd.Parameters("@Opp2").Value
    Dim Result2 as Integer= Cmd.Parameters("@Opp1").Value

    Result 1 and Result 2 will have the result values returned from the stored procedure. verify the Datatype of the output value for the store procedure

    Author Comment

    Good Day Experts...Thanks for your response.  

    Unfortunately, I did not formulate my question properly. I will have the 2 input parameters and a resultset as the return output.  

    Does returning a resultset make the task more difficult?

    LVL 83

    Accepted Solution

    In that case, try

    Dim dbadp as new sqldataadapter("spname", "connection string")
    Dbadp.selectcommand.commandtype = commandtype.storedprocedure
    Dbadp.selectcommand.parameters.addwithvalue("paramname", value)
    Dim dTable as new datatable

    Author Comment

    Excellent...that is what I was looking to do.  I have not had to do this so far in my career.  Also, I have not had to utilize what K-designers sent over but I will put it in my back pocket for future use.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    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…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 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

    18 Experts available now in Live!

    Get 1:1 Help Now