troubleshooting Question

Store output parameter in a variable in VB code

Avatar of connelvalentine
connelvalentine asked on
Visual Basic.NET
2 Comments1 Solution1029 ViewsLast Modified:
Hi,

I'm using the following code to save my record in tbl_offices table. I'm inserting the record via a stroed procedure called stp_insertoffices
##THIS IS THE CODE FOR MY STORED PROCEDURE##
ALTER PROCEDURE dbo.stp_insertoffices
(
      @fld_officename varchar(20),
      @fld_legalentity char(3),
      @fld_description varchar(50),
      @fld_officecode int OUT
)
AS
      SET NOCOUNT OFF;
INSERT INTO tbl_offices
      (
      fld_officename,
      fld_legalentity,
      fld_description
      )
VALUES
      (
      @fld_officename,
      @fld_legalentity,
      @fld_description);
      SET @fld_officecode = scope_identity()

##THIS IS THE CODE FROM MY FORM##      
 'opening the connection
        Dim var_frmmain As New frm_main()
        Dim var_connectionString As String
        var_connectionString = var_frmmain.Connection
        Dim var_connection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(var_connectionString)
        var_connection.Open()

        'if a new office is being inserted then run the insert office stored procedure
        If var_isadd = True Then
            Try
                '##ADDING THE DETAILS IN THE OFFICES TABLE##
                'declaring the adapter
                Dim var_officesadapter As SqlDataAdapter = New SqlDataAdapter("SELECT fld_Officecode, fld_OfficeName, fld_Legalentity, fld_Description FROM tbl_offices", var_connection)

                'configuring the insert command to connect to the stored procedure
                var_officesadapter.InsertCommand = New SqlCommand("stp_insertoffices", var_connection)
                var_officesadapter.InsertCommand.CommandType = CommandType.StoredProcedure

                'setting the parameters
                With var_officesadapter.InsertCommand.Parameters
                    .Add("@fld_officename", SqlDbType.VarChar, 20, "fld_officename")
                    .Add("@fld_legalentity", SqlDbType.NChar, 3, "fld_legalentity")
                    .Add("@fld_description", SqlDbType.VarChar, 50, "fld_description")
                End With

                'When the InsertCommand is processed for an added row, the auto-incremented
                'Identity value is returned as this output parameter and is placed in the
                'fld_OfficeCode column of the current row.
                Dim var_parameter As SqlParameter = var_officesadapter.InsertCommand.Parameters.Add("@fld_officecode", SqlDbType.Int, 0, "fld_officecode")
                var_parameter.Direction = ParameterDirection.Output

                Dim var_officedataset As DataSet = New DataSet
                var_officesadapter.Fill(var_officedataset, "tbl_offices")

                Dim var_newRow As DataRow = var_officedataset.Tables("tbl_offices").NewRow()
                var_newRow("fld_OfficeName") = txt_officename.Text
                var_newRow("fld_Legalentity") = txt_legalentity.Text
                var_newRow("fld_Description") = txt_description.Text
                var_officedataset.Tables("tbl_offices").Rows.Add(var_newRow)

                var_officesadapter.Update(var_officedataset, "tbl_offices")
----------------------
As you can see, i'm inserting the output parameter of the field fld_officecode, which is set as an identity in my SQL database to new records entered.
I want to know how i can store this output value in a variable in my vb code, so that i can use it in other parts of my code in the same form.

I'm coding in VB.NET 2005.

Best Regards,
CV
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros