Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server VB.NET
Hi
 
I Have a strored procedure that requires one integer field as input eg.IDnumber
and it then returns two fields eg.Name and Position
 
how will I work towards executing the procedure in VB.net and then storing both these return values in variables sepratly?
 

STORED PROCEDURE :
_______________________
spGetDetails @intID
 
---------
--------
-------
 
return Name,Position
_________________________
 
something like that?
 
Thank you in advance!
Posted 23-Aug-12 4:23am
Edited 23-Aug-12 4:39am
v3
Comments
ryanb31 at 23-Aug-12 9:39am
   
You can use a DataReader and read in the values. This is pretty trivial. What have you done so far?
Member 9374423 at 23-Aug-12 9:50am
   
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Connect()
 
Dim IDnumber2 As Integer = Int32.Parse(IDnumber)
 
Dim cmdSP As New SqlCommand
cmdSP.CommandText = "spBatchRollBack2 @" & IDnumber2
cmdSP.CommandType = CommandType.StoredProcedure
 
End Sub
Member 9374423 at 23-Aug-12 9:50am
   
so its a button that needs to get the return values of that Stored procedure
Member 9374423 at 24-Aug-12 3:28am
   
excatly,two return values

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can specify out parameters to your proc, instead of using the returned table, or do select name, position and then read the table you get back.
 

Here[^] is how to write a proc with out params.
 
using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
                {
                    command1.CommandType = CommandType.Text;
                    command1.Connection = sc1;
                    // DIRECTION :: Input
                    command1.CommandText = "select @MyParameter = Count(*) FROM [Purchasing].[ShipMethod]";
                    System.Data.SqlClient.SqlParameter paramter1 = command1.Parameters.Add("@MyParameter", SqlDbType.SmallInt);
                    paramter1.Direction = ParameterDirection.Output;
                    command1.ExecuteNonQuery();
                    //The following value is now 6, the number of records inside the table
                    int newValue = (int)paramter1.Value;
                }
 
is an example of how to add output parameters to your call.
  Permalink  
v2
Comments
Member 9374423 at 23-Aug-12 9:50am
   
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Connect()
 
Dim IDnumber2 As Integer = Int32.Parse(IDnumber)
 
Dim cmdSP As New SqlCommand
cmdSP.CommandText = "spBatchRollBack2 @" & IDnumber2
cmdSP.CommandType = CommandType.StoredProcedure
 
End Sub
Member 9374423 at 23-Aug-12 9:51am
   
please refference an example
Christian Graus at 23-Aug-12 9:53am
   
What bit is confusing you ? You should really add your parameters as parameters, to the parameters collection. cmd.SP.Paramaters.Add. If you do this, you can specify an out direction, and get values back that way
Member 9374423 at 23-Aug-12 9:56am
   
well what can i add in the brackets?
Christian Graus at 23-Aug-12 10:04am
   
What brackets ? That's not the solution. Let me edit my response

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 575
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,476
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,917


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 23 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100