CODEWITHSUNDEEP
c#sql-serverstored-procedures
mshwf
mshwf

Reputation: 7449

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

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

Upvotes: 3

Views: 36763

Answers (2)

mshwf
mshwf

Reputation: 7449

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.

Upvotes: 23

Vadim K.
Vadim K.

Reputation: 1101

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()

Upvotes: 8

Related Questions