Solved

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

Posted on 2014-10-27
3
474 Views
Last Modified: 2014-10-27
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
Comment
Question by:Mike Eghtebas
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40407026
.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
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 40407031
Thank you.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40407035
re> Return value would be "select 1"

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now