Stored procedure doesn't allow null parameters even the parameter represent accept nulls?

Question

I saw many questions on stack overflow, non of them touched my own problem

procedure or function expects parameter which was not supplied

I created this SQL Server stored procedure:

CREATE proc [dbo].[spAddCustomer]
    @cuName varchar(50),
    @cuAddress varchar(50),
    @cuMobile varchar(50),
    @cuImage image,
    @cityId int,
    @exist int output
AS
BEGIN
    IF NOT EXISTS(SELECT Cu_Mobile FROM tblCustomers WHERE Cu_Mobile = @cuMobile)
    BEGIN
        INSERT INTO tblCustomers (Cu_Name, Cu_Address, Cu_Mobile, Cu_Image, City_ID)
        VALUES (@cuName, @cuAddress, @cuMobile, @cuImage, @cityId)

        SET @exist = 1
    END
    ELSE
        SET @exist = 0
END

In my Data Access Layer I have this method that is responsible for non-query commands:

public static int ExecuteNonQuery(string query, CommandType type, params SqlParameter[] arr)
{
    int outParam;

    SqlCommand cmd = new SqlCommand(query, cn);
    cmd.Parameters.AddRange(arr);
    cmd.CommandType = type;

    int i = cmd.ExecuteNonQuery();

    foreach (SqlParameter param in arr)
    {
        if (param.Direction == ParameterDirection.Output)
        {
            outParam = (int)cmd.Parameters[param.ToString()].Value;
            return outParam;
        }
    }

    return i;
}

The method responsible for creating parameters:

public static SqlParameter CreateParameter(string name, SqlDbType type, object value, ParameterDirection pd = ParameterDirection.Input)
{
    SqlParameter pr = new SqlParameter();
    pr.ParameterName = name;
    pr.Direction = pd;
    pr.SqlDbType = type;
    pr.SqlValue = value;

    return pr;
}

And this method in the Business Layer, pass the arguments from the Presentation Layer

public static int spAddCustomer(string cusName, string cusAddress, string cusMobile, byte[] arrImg, int cityId)
{
    DataAccessLayer.Open();

    int i = DataAccessLayer.ExecuteNonQuery("spAddCustomer", CommandType.StoredProcedure,
        DataAccessLayer.CreateParameter("@cuName", SqlDbType.VarChar, cusName),
        DataAccessLayer.CreateParameter("@cuAddress", SqlDbType.VarChar, cusAddress),
        DataAccessLayer.CreateParameter("@cuMobile", SqlDbType.VarChar, cusMobile),
        DataAccessLayer.CreateParameter("@cuImage", SqlDbType.Image, arrImg),
        DataAccessLayer.CreateParameter("@cityId", SqlDbType.Int, cityId),
        DataAccessLayer.CreateParameter("@exist", SqlDbType.Int, null, ParameterDirection.Output));

    DataAccessLayer.Close();

    return i;
}

When the user Click add a new record is inserted into the table (tblCustomers)

private void btnAU_Click(object sender, EventArgs e)
{
    byte[] imgArr;

    if (PbCustomer.Image == null)
           imgArr = null;
    else
    {
        MemoryStream ms = new MemoryStream();
        PbCustomer.Image.Save(ms, PbCustomer.Image.RawFormat);
        imgArr = ms.ToArray();
    }

    int cityId = int.Parse(cmbCities.SelectedValue.ToString());

    try
    {
        int exist = CustomerClass.spAddCustomer(txtName.Text, txtAddress.Text, txtMobile.Text, imgArr, cityId);

        if (exist == 1)
        {
            MessageBox.Show("A new customer has been saved");
            txtAddress.Text = txtMobile.Text = txtName.Text = "";
            PbCustomer.Image = null;
        }
        else
            MessageBox.Show("A customer with the same mobile number\nalready exists, add new number!");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

But when I click the Add button (passing null image), I get this error:

procedure or function 'spAddCustomer' expects parameter '@cuImage' which was not supplied

Despite the table tblCustomers accept null values

Have you stepped through the code in the debugger to see what values are actually being passed in? And can you post the code for CreateParameter? — Tim
– Tim, Commented Aug 18, 2016 at 17:44
Note that an empty TextBox has a value of String.Empty, not null! Do things get better is you convert "" to null? — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Aug 18, 2016 at 17:52
better yet do something like this if the TextBox.Text is = to string.Empty DataAccessLayer.CreateParameter("@cuName", SqlDbType.VarChar).Value = string.IsNullOrEmpty(cusName) ? DBNULL.Value : cusName; — MethodMan
– MethodMan, Commented Aug 18, 2016 at 17:56
ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here — marc_s
– marc_s, Commented Aug 18, 2016 at 18:07
Thanks @marc_s, I know about text but I didn't know about image — mshwf
– mshwf, Commented Aug 18, 2016 at 18:10

mshwf · Accepted Answer · 2016-08-18 18:44:28Z

24

I just found that I can set default values for the parameter in the stored procedure:

ALTER proc [dbo].[spAddCustomer]
@cuName varchar(50)=null,
@cuAddress varchar(50)=null,
@cuMobile varchar(50)= null,
@cuImage image= null,
@cityId int= null,
@exist int output

And this solved my problem! This is helpful specifically with null images from the PictureBox, since I have a helper method that checks for empty strings.

edited Aug 18, 2016 at 18:44

answered Aug 18, 2016 at 18:14

mshwf

7,52716 gold badges69 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vadim K. · Accepted Answer · 2016-08-18 17:59:56Z

8

You need to check your input for null and use DBNull.Value when you creating the parameters. If you pass just null as a parameter - ADO.Net will ignore that.

EDIT:

You can add that check into your custom method DataAccessLayer.CreateParameter()

edited Aug 18, 2016 at 17:59

answered Aug 18, 2016 at 17:50

Vadim K.

1,1231 gold badge15 silver badges29 bronze badges

2 Comments

Olivier Jacot-Descombes Over a year ago

DataAccessLayer.CreateParameter("@cuName", SqlDbType.VarChar, String.IsNullOrWhitespace(cusName) ? DBNull.Value : cusName),

Vadim K. Over a year ago

I would rather fix DataAccessLayer.CreateParameter() method itself to check for null/whitespace parameter instead of using ternary operator ?: everytime.

Collectives™ on Stack Overflow

Stored procedure doesn't allow null parameters even the parameter represent accept nulls?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related