Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

expects parameter '@message', which was not supplied ... vb.net

I am running:

SQL.RecordCount = cmdSP.ExecuteNonQuery

where it complains about @message output parameter:Procedure or function 'spAddOrder' expects parameter '@message', which was not supplied., but as shown on line 13 below, this parameter is included.

Question: Is my syntax at line 13 correct?

              With cmdSP
                    ' CUSTOMER PARAMETERS AND VALUES
                    .Parameters.Add("@CustomerID", SqlDbType.Int, 4).Value = intCustID

                    ' ORDER PARAMETERS
                    Dim x As String = cboOrder.SelectedValue.ToString
                    Dim SoftID As Integer = x
                    .Parameters.Add("@SoftwareID", SqlDbType.Int, 4).Value = SoftID

                    Dim Qty As Integer = txtQuantity.Text
                    .Parameters.Add("@Quantity", SqlDbType.Int, 4).Value = Qty

                    .Parameters.Add("@message", SqlDbType.VarChar, 100).Direction = ParameterDirection.ReturnValue

                End With

Open in new window

I have tested the following proc in SSMS and it works
CREATE PROCEDURE [dbo].[spAddOrder]
     @CustomerID int
	, @SoftwareID int 
	, @Quantity int 
	, @message VARCHAR(100) OUTPUT
As
BEGIN

SET NOCOUNT ON;

Declare @OrderID int
Declare @AddedOrderCount int
Declare @msg varchar(100)=''

   Insert Into tblOrders (CustomerID, OrderDate)
				       Values(@CustomerID,GETDATE())

  Set @OrderID = SCOPE_IDENTITY()

  Insert Into tblOrderDetails(OrderID, SoftwareID, Quantity)
			         Values(@OrderID, @SoftwareID, @Quantity) 

Set @AddedOrderCount = @@ROWCOUNT

Set @msg ='Orders added: ' + CAST(@AddedOrderCount AS NVARCHAR(10));

if @@Error>0 
     Set @msg = @msg + '; SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))

Set @message = @msg

Return @@Error

END 

/*
Declare @msg varchar(100)
Declare @message varchar(100)
Exec @msg = spAddOrder 1,2,14, @message OUTPUT
Select @msg 
Select @message 
*/

Open in new window

And here is a look at vs2012 Server Explorer:
ProcOutput
0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
.Parameters.Add("@message", SqlDbType.VarChar, 100).Direction = ParameterDirection.ReturnValue
should be
.Parameters.Add("@message", SqlDbType.VarChar, 100).Direction = ParameterDirection.Output


Return value would be "select 1"
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
re> Return value would be "select 1"

Where this should go? Not in vs, right?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now