Parameterized query error, parameter is supplied but says it wasn't

540 views Asked by At

I am passing a data object with jQuery.Ajax and I am getting this error when it gets to inserting a record.

Additional information: The parameterized query '(@CustomerID varchar(40),@CustomerName varchar(100),@Address1 va' expects the parameter '@CustomerID', which was not supplied.

That gets thrown when it hits this method..

public void AddCustomer(CustomerToAdd customer)
{
        AWEF = new TestDBEntities();
        //customer.customerID = "12345";
        AWEF.Database.SqlQuery<CustomerToAdd>("exec usp_AddCustomer",
            new object[]{
                new SqlParameter("@CustomerID", SqlDbType.VarChar,40, customer.customerID),
                new SqlParameter("@CustomerName", SqlDbType.VarChar, 100, customer.customerName),
                new SqlParameter("@Address1", SqlDbType.VarChar, 100, customer.address1),
                new SqlParameter("@Address2", SqlDbType.VarChar, 100, customer.address2),
                new SqlParameter("@City", SqlDbType.VarChar, 50, customer.city),
                new SqlParameter("@State", SqlDbType.VarChar, 50, customer.state),
                new SqlParameter("@Zip", SqlDbType.VarChar,10, customer.zip),
                new SqlParameter("@CountryID", SqlDbType.Int, 1),
                new SqlParameter("@ContactName", SqlDbType.VarChar, 100, customer.contactName),
                new SqlParameter("@Email", SqlDbType.VarChar, 100, customer.email),
                new SqlParameter("@Phone", SqlDbType.VarChar, 15, customer.phone),
                new SqlParameter("@Work", SqlDbType.VarChar, 15, customer.work),
                new SqlParameter("@Mobile", SqlDbType.VarChar, 15, customer.mobile),
                new SqlParameter("@Fax", SqlDbType.VarChar, 15, customer.fax),
                new SqlParameter("@Other", SqlDbType.VarChar, 15, customer.other),
                new SqlParameter("@Spouse", SqlDbType.VarChar, 15, customer.other2),
                new SqlParameter("@SpouseMobile", SqlDbType.VarChar, 15, customer.other3)
            }).ToList();
}

I have stepped through the code and the customer object has all the values, this is my stored procedure

 ALTER PROCEDURE [dbo].[usp_AddCustomer]
     (@CustomerID VARCHAR(40),
      @CustomerName VARCHAR(100),
      @Address1 VARCHAR(100),
      @Address2 VARCHAR(100),
      @City VARCHAR(50),
      @State VARCHAR(50),
      @Zip VARCHAR(10),
      @CountryID INT,
      @ContactName VARCHAR(100),
      @Email VARCHAR(100),
      @Phone VARCHAR(15),
      @Work VARCHAR(15),
      @Mobile VARCHAR(15),
      @Fax VARCHAR(15),
      @Other VARCHAR(15),
      @Spouse VARCHAR(15),
      @SpouseMobile VARCHAR(15)
    )
AS
    DECLARE @customerContactID INT

    INSERT INTO [Customer] (CustomerID, CustomerName) 
    VALUES (@CustomerID, @CustomerName)

    INSERT INTO [CustomerAddress] (Address1, Address2, City, State, Zip, CountryID, CustomerID)
    VALUES (@Address1, @Address2, @City, @State, @Zip, @CountryID, @CustomerID)

    INSERT INTO [CustomerContact] (ContactName, Email, CustomerID) 
    VALUES (@ContactName, @Email, @CustomerID)

    SET @customerContactID = @@IDENTITY

    INSERT INTO [CustomerPhone] (Phone, Work, Mobile, Fax, Other, CustomerContactID)
    VALUES (@Phone, @Work, @Mobile, @Fax, @Other, @customerContactID)

    INSERT INTO [CustomerSpouse] (Spouse, SpouseMobile, CustomerContactID)   
    VALUES (@Spouse, @SpouseMobile, @customerContactID)

If I run the stored procedure in SSMS and provide junk data then it executes fine.

Am I missing something or where am I going wrong?

Thanks

0

There are 0 answers