SQLException was unhandled, Dataset -Table Adapter

Question

I have the following stored procedure and I'm calling it from my Windows Forms application DataSet like this:

  Me.TransactionTableAdapter.spPaymentApply(130, iAmount, Now)

Although I provide the CustomerID, (and stepping in the code to see if it's actually there - and it is) I get the following error during execution: Procedure or function 'PaymentApply' expects parameter '@CustomerID', which was not supplied.

Here is my SP:

USE [dbPB]
GO
/****** Object:  StoredProcedure [dbo].[PaymentApply]    Script Date: 05/30/2013 18:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PaymentApply]
   @CustomerID int,
   @Amount int,
   @AsOfDate datetime
AS
WITH Totals AS (
   SELECT
      T.*,
      RunningTotal =
         Coalesce (
            (SELECT Sum(S.Buyin - Coalesce(S.CreditPaid, 0))
            FROM [Transaction] S
            WHERE
               T.CustomerID = S.CustomerID
               AND S.Type = 'Credit'
               AND S.Buyin > Coalesce(T.CreditPaid, 0)
               AND (
                  T.Starttime > S.Starttime
                  OR (
                     T.Starttime = S.Starttime
                     AND T.TransactionID > S.TransactionID
                  )
               )
            ),
        0)
   FROM
      [Transaction] T
   WHERE
      CustomerID = @CustomerID
      AND T.Type = 'Credit'
      AND T.Buyin > Coalesce(T.CreditPaid, 0)
)
UPDATE T
SET
   T.EndTime = P.EndTime,
   T.CreditPaid = Coalesce(T.CreditPaid, 0) + P.CreditPaid
FROM
   Totals T
   CROSS APPLY (
      SELECT TOP 1
         V.*
      FROM
         (VALUES
            (T.Buyin - Coalesce(T.CreditPaid, 0), @AsOfDate),
            (@Amount - RunningTotal, NULL)
         ) V (CreditPaid, EndTime)
      ORDER BY
         V.CreditPaid,
         V.EndTime DESC
   ) P
WHERE
   T.RunningTotal <= @Amount
   AND @Amount > 0;
;

VB Code:

Private Sub btnTransProcess_Click(sender As Object, e As EventArgs) Handles btnTransProcess.Click
        Dim iAmount As Integer
        Dim drv As DataRowView = CType(Me.cboCustomerName.SelectedItem, DataRowView)
        Dim SelCustId As Integer
        SelCustId = drv.Item("CustomerID")
        Try
            iAmount = CType(txtCreditPayment.Text, Integer)
        Catch ex As Exception
            MessageBox.Show("Enter a valid Credit Payment Amount!", "Invalid Amount", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End Try
        MsgBox(SelCustId)
        Me.TransactionTableAdapter.spPaymentApply(130, iAmount, Now)
        '
        'Dim command As New SqlCommand(queryString, connection)
        'command.Parameters.AddWithValue("@CustomerID", 123)
    End Sub

Answer 1

It sounds like you're using a Table Adapter instead of a Command Object.

see: Table Adapter and TableAdapter Query Configuration Wizard

Your call to Me.TransactionTableAdapter.spPaymentApply(130, iAmount, Now) looks like it should provide 130 as the CustomerID- but if the code does not compile/work properly, perhaps you should use the configuration wizard.

Or, consider the use of a command object instead. The use of both is outlined here:

How to: Create and Execute an SQL Statement that Returns No Value

Answer 2

What happened ?

You didn't provide the parameter.

But.. I double-checked...

Maybe you're not calling it the right way ?
Have you tried something like this :

  Dim queryString As String = "exec PaymentApply" 

    Using connection As New SqlConnection(ConnStrg)
        connection.Open()
        Dim command As New SqlCommand(queryString, connection)
        command.Parameters.AddWithValue("@CustomerID", 123)
        // ... same for other non-nullable parameters
        Dim reader As SqlDataReader = command.ExecuteReader()
    End Using

End Try

Why should I do this ?

When you call your Stored Procedure from you code, you don't provide the @CustomerID int parameter.

Since the parameter is not nullable it expects a value and will not go any further until you give it one.

If you want it to be nullable you can set a default value to it. This way he will take this value if you don't provide a new one. Exemple :

From your code, you pass a parameter like this :

someCommand.Parameters.AddWithValue("@CustomerID", 123)

If you want your parameter to be nullable, do something like this in SQL

@CustomerID int = 123