Procedure or function xyz expects parameter abc even if i dont want to enter anythin

Question

0.00/5 (No votes)

See more:

Hi,

A mere help needed, I am creating an mvc app wherein i take user input say CustId CustName and CustPhone.I have written a procedure for it which works fine when i enter all 3 values as input.

Now, sometimes need be,i may not enter CustName or CustPhone for an id.

if i do so i get an error "

Procedure or function 'NewCustomer' expects parameter '@ipv_CustomerName', which was not supplied.

how can i achieve this??

Please Help.

Thanks in advance.

[EDIT - moved from comment]

This is my Entity class:

C#

public class CustomerDetails
    {
        
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public string CustomerPhone { get; set; }  


        public CustomerDetails()
        {
           CustomerId = 123;
            CustomerName = "Customer";
            CustomerPhone = "9876543210";
        }  
                  
  }

----------------------------------------
Code in the data access layer:

C#

//Create parameters
SqlParameter ipv_CustomerId = cmd.CreateParameter();
SqlParameter ipv_CustomerName = cmd.CreateParameter();
SqlParameter ipv_CustomerPhone = cmd.CreateParameter();
                
//assign names
ipv_CustomerId.ParameterName = "@ipv_CustomerId";
ipv_CustomerName.ParameterName = "@ipv_CustomerName";
ipv_CustomerPhone.ParameterName = "@ipv_CustomerPhone";
                
//define type
ipv_CustomerId.SqlDbType = SqlDbType.Int;
ipv_CustomerName.SqlDbType = SqlDbType.VarChar;
ipv_CustomerPhone.SqlDbType = SqlDbType.VarChar;

//provide values (input variable is user input to the function)       
ipv_CustomerId.Value = input.CustomerId;
ipv_CustomerName.Value = input.CustomerName;
ipv_CustomerPhone.Value = input.CustomerPhone;
                
//add parameters
cmd.Parameters.Add(ipv_CustomerId);
cmd.Parameters.Add(ipv_CustomerName);
cmd.Parameters.Add(ipv_CustomerPhone);
                
rowsAffected = cmd.ExecuteNonQuery();
                if (rowsAffected < 1)
                {
                    added = false;
                }
                else
                {
                    added = true;
                }

----------------------------------------------
The Procedure:

SQL

Create PROCEDURE NewCustomer
(@ipv_CustomerId int,
@ipv_CustomerName varchar(20),
@ipv_CustomerPhone varchar(20))

AS
Insert into Customer_Table values(@ipv_CustomerId,
@ipv_CustomerName,
@ipv_CustomerPhone)

----------------------------------------
I want to be able to pass null values to the procedure for CustomerName and CustomerPhone.

[/EDIT]

Posted 9-May-13 8:12am

Member 9960197

Updated 9-May-13 8:46am

Maciej Los

v2

Add a Solution

Comments

joshrduncan2012 9-May-13 14:15pm

Can you post your code so we can see what you are talking about? How do you expect the function to proceed correctly if you don't supply any information?

Member 9960197 9-May-13 14:39pm

Hi Josh,

This is my Entity class:
public class CustomerDetails
{

public int CustomerId { get; set; }
public string CustomerName { get; set; }
public string CustomerPhone { get; set; }

public CustomerDetails()
{
CustomerId = 123;
CustomerName = "Customer";
CustomerPhone = "9876543210";
}

}

----------------------------------------
Code in the data access layer:
//Create parameters
SqlParameter ipv_CustomerId = cmd.CreateParameter();
SqlParameter ipv_CustomerName = cmd.CreateParameter();
SqlParameter ipv_CustomerPhone = cmd.CreateParameter();

//assign names
ipv_CustomerId.ParameterName = "@ipv_CustomerId";
ipv_CustomerName.ParameterName = "@ipv_CustomerName";
ipv_CustomerPhone.ParameterName = "@ipv_CustomerPhone";

//define type
ipv_CustomerId.SqlDbType = SqlDbType.Int;
ipv_CustomerName.SqlDbType = SqlDbType.VarChar;
ipv_CustomerPhone.SqlDbType = SqlDbType.VarChar;

//provide values (input variable is user input to the function)
ipv_CustomerId.Value = input.CustomerId;
ipv_CustomerName.Value = input.CustomerName;
ipv_CustomerPhone.Value = input.CustomerPhone;

//add parameters
cmd.Parameters.Add(ipv_CustomerId);
cmd.Parameters.Add(ipv_CustomerName);
cmd.Parameters.Add(ipv_CustomerPhone);

rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected < 1)
{
added = false;
}
else
{
added = true;
}
----------------------------------------------
The Procedure:
Create PROCEDURE NewCustomer
(@ipv_CustomerId int,
@ipv_CustomerName varchar(20),
@ipv_CustomerPhone varchar(20))

AS
Insert into Customer_Table values(@ipv_CustomerId,
@ipv_CustomerName,
@ipv_CustomerPhone)
----------------------------------------
I want to be able to pass null values to the procedure for CustomerName and CustomerPhone.

Tellme if you want me to elaborate it more.
Thanks.

[no name] 9-May-13 14:20pm

You *could* just pass a null for the parameters that you do not have and just ignore them in your function.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2013-05-09T08:42:00

Solution 1

You can use optional parameters in SQL. Just give it a default value. For example:

SQL

ALTER PROCEDURE [dbo].[TESTSP] 
(
   @param1   NVARCHAR (50),
   @param2   NVARCHAR (50) = NULL
)
AS
BEGIN

In this case, if I don't pass in @param2 then a null will be used.

Posted 9-May-13 8:42am

ZurdoDev

Comments

Maciej Los 9-May-13 14:59pm

+5!