Share out a Stored Procedure output parameter with other subs

[WayneGretzky]

how can I make the value for the output parameter below available to all my subs in my code behind class?

This is in one of my Subs but I want to share it out:

Dim NewCateringOrderID As New SqlParameter("@NewCateringOrderID", SqlDbType.BigInt)
NewCateringOrderID.Direction = ParameterDirection.Output
cmd.Parameters.Add(NewCateringOrderID)

 

[ca8msm]

Declare the parameter globally (i.e. at the top of your page instead of within the sub) then modify your code to create a new instance of the parameter e.g.

Dim NewCateringOrderID As SqlParameter

Private Sub mySub() 
        NewCateringOrderID = New sqlParameter("@NewCateringOrderID", SqlDbType.BigInt)
        NewCateringOrderID.Direction = ParameterDirection.Output
        cmd.Parameters.Add(NewCateringOrderID)
End Sub

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[WayneGretzky]

ok, but now I get this error:

Exception Details: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Source Error:


Line 292: Dim cmd As SqlCommand = New SqlCommand("sp_Get_OrderDetails", Conn)
Line 293: cmd.CommandType = CommandType.StoredProcedure
Line 294: cmd.Parameters.Add(New SqlParameter("@CateringOrderID", NewCateringOrderID))
Line 295: cmd.Connection.Open()
Line 296: Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

 

[WayneGretzky]

Here's my latest code from my code-behind page:


Public Class catering_form
Inherits System.Web.UI.Page

some code...

Dim NewCateringOrderID As SqlParameter

...some more code


Public Sub InsertOrder()

Conn.Open()

cmd = New SqlCommand("Add_NewOrder", Conn)
cmd.CommandType = CommandType.StoredProcedure
NewCateringOrderID = New SqlParameter("@NewCateringOrderID", SqlDbType.BigInt)
NewCateringOrderID.Direction = ParameterDirection.Output
cmd.Parameters.Add(NewCateringOrderID)

....more code
End Sub


End Class

I now get this error: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

 

[WayneGretzky]

latest code is here:

http://www.photopizzaz.biz/allcode.txt

remember, I can't pull out NewCateringOrderID = New SqlParameter("@NewCateringOrderID", SqlDbType.BigInt) from my InsertOrder() or it throws an error saying I haven't declared NewCateringOrderID