Add empty string to Parameter List for Insertion into AccessDB

Question

I'm trying to insert data into an Access database using the System.Data.OleDb namespace and the method OleDbCommandObject.ExecuteNonQuery(), but I am receiving errors along the lines of:

The OleDbParameterCollection only accepts non-null OleDbParameter type objects. Parameter name: value

and

Parameter ? _[#] has no default value

Most of my parameters are string objects that work fine when a value is assigned to them via a form. However, when a non-required field is left blank (ie someString = "") and the form is submitted, then the error occurs. I have looked around for answers on this site and others that mention possible solutions like:

• OleDb uses ? instead of name values
• Make sure the number of parameters matches the number of ?'s and that they are inserted in the right order.
• Use command.Parameters.Add(new OleDbParameter("Name", OleDbType.VarChar, 50, "Column").Value = someString); or something along those lines.
• Use command.Parameters.AddWithValue("parameterName", someString);

I could set the value of the string to "[space]", but then this removes the placeholder in the form, which is not ideal, and one I do not believe should be necessary. If I have to check for the empty string and add the space before adding the value to the parameter list, then I will, but I hope to avoid the extra code if possible.

Here is an example of the code I am using:

Customer.cs

    public class Customer
    {
    [Required]
    public string ContactFirstName { get; set; }

    [Required]
    public string ContactLastName { get; set; }

    public string Email {get; set; }
    public string Phone {get; set; }

    public Customer()
    {
        this.ContactFirstName = "";
        this.ContactLastName = "";
        this.Email = "";
        this.Phone = "";
    }
    }

Method used to insert data

public int InsertValues(Customer customer)
{
this.dbConn.Open(); //System.Data.OleDb.OleDbConnection

this.query = "INSERT INTO someTable (ContactFirstName, ContactLastName, Email, Phone) VALUES (?,?,?,?)";
this.dbComm = new OleDbCommand(this.query, this.dbConn); //System.Data.OleDb.OleDbCommand

//Add parameters
this.dbComm.Parameters.AddWithValue("ContactFirstName", customer.ContactFirstName);
this.dbComm.Parameters.AddWithValue("ContactLastName", customer.ContactLastName);
this.dbComm.Parameters.AddWithValue("Email", customer.Email);
this.dbComm.Parameters.AddWithValue("Phone", customer.Phone);

this.dbComm.ExecuteNonQuery();

//--Snip--
}

The ODBC driver I am using is a Microsoft.OleDb.ACE.12.0. Any help or information that could lead me in the right direction would be greatly appreciated.

Answer 1

I'm sure there may be other solutions to this problem, but this is one I have found through trial and error that adds a small amount of code, and which can be improved by making a method that will do this instead.

As pointed out in the OP and in Gökhan Girgin's response, the AddWithValue method should appear similar to:

OleDbCommandObject.Parameters.AddWithValue("Whatever you want here", Value);

Now, my main focus is on adding a string value to the parameter list that contains no value (i.e. in the form the user left the field blank). The string is simply blank as in "[no_space]". What appears to be happening is that upon submission of the form, this value is changed from "[no_space]" to null. Attempting to type cast the Value to a string type does not solve the problem. The solution I have found involved concatenating another blank string to the Value.

Any of the combinations below I have found work though this list is most likely not exhaustive. Make sure when adding parameters with OleDb that it is in the same order as your INSERT statement.

OleDbCommandObject.Parameters.AddWithValue("@Phone", "" + customer.Phone);
OleDbCommandObject.Parameters.AddWithValue("@Phone", customer.Phone + "");
OleDbCommandObject.Parameters.AddWithValue("@Phone", "" + customer.Phone + "");

Once again, to me it does not make sense that this needs to be done unless the value is changed to null on form submission because the string is already set to "[no_space]" in the constructor. I do not see how adding another "[no_space]" changes anything, but it was accepted and no error occurs. It will be greatly appreciated if anyone can shed light on why this works.

To others who have run into this issue, I hope this helps.