Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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

I have a VB.net 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
  • 2
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 400 total points
ID: 38338849
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

ID: 38340637
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

CodeCruiser earned 1600 total points
ID: 38340655
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

ID: 38341063
Excellent...that is what I was looking to do.  I have not had to do this so far in my VB.net 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

581 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