Attempting to INSERT values into a MySQL DB from C# code within a form, but getting "Column cannot be NULL

Question

All - thanks in advance for your time. So, background info - I am trying to create a form for contact registration using C# to pass the information into my MySql DB. If I use the query directly in the code, it works. However, I have read that you should use a stored procedure for security. So, working code is:

using (MySqlCommand cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = @"INSERT INTO Contacts (contactID,last_name,first_name,address,city,state,zip_code,email_address,newsletter,is_Cell) VALUES (@ciD,@ln, @fn, @add, @city, @state, @zip, @email, @news, @cell)";
                //cmd.CommandText = "insert_contact";
                //cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add("@cID", MySqlDbType.VarChar);
                cmd.Parameters.Add("@ln", MySqlDbType.VarChar);
                cmd.Parameters.Add("@fn", MySqlDbType.VarChar);
                cmd.Parameters.Add("@add", MySqlDbType.VarChar);
                cmd.Parameters.Add("@city", MySqlDbType.VarChar);
                cmd.Parameters.Add("@state", MySqlDbType.VarChar);
                cmd.Parameters.Add("@zip", MySqlDbType.VarChar);
                cmd.Parameters.Add("@email", MySqlDbType.VarChar);
                cmd.Parameters.Add("@news", MySqlDbType.Bit);
                cmd.Parameters.Add("@cell", MySqlDbType.Bit);

                cmd.Parameters["@cID"].Value = default;
                cmd.Parameters["@ln"].Value = lastName_TextBox.Text;
                cmd.Parameters["@fn"].Value = firstName_TextBox.Text;
                cmd.Parameters["@add"].Value = address_TextBox.Text;
                cmd.Parameters["@city"].Value = city_TextBox.Text;
                cmd.Parameters["@state"].Value = state_DropDown.Text;
                cmd.Parameters["@zip"].Value = zipCode_TextBox.Text;
                cmd.Parameters["@email"].Value = email_TextBox.Text;
                cmd.Parameters["@news"].Value = newsletter_CheckBox.Checked;
                cmd.Parameters["@cell"].Value = cell_CheckBox.Checked;

                cmd.ExecuteNonQuery();
                conn.Close();

However, when I change the following lines to this, I get the "cannot be NULL error":

 conn.Open();
                //cmd.CommandText = @"INSERT INTO Contacts (contactID,last_name,first_name,address,city,state,zip_code,email_address,newsletter,is_Cell) VALUES (@ciD,@ln, @fn, @add, @city, @state, @zip, @email, @news, @cell)";
                cmd.CommandText = "insert_contact";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

My stored procedure on the DB is (I suspect this is where the error may be):

 BEGIN
 INSERT INTO Contacts (contactID,last_name,first_name,address,city,state,zip_code,email_address,newsletter,is_Cell) 
 VALUES (@ciD,@ln, @fn, @add, @city, @state, @zip, @email, @news, @cell);
 END

I have attempted the following, with the accompanying errors:

• Changed the "@" in the stored procedures to a "?" -(Get an error in SQL Syntax)

• Changing all of the columns to accept a NULL value. -(All columns then have a NULL value).

My apologies if this is something easy - just starting out learning.

Thanks in advance!

• Pherix

Answer 1

Ok, finally found what the Stored Procedure liked:

BEGIN 

INSERT INTO Contacts 
(last_name,first_name,address,city,state,zip_code,email_address,newsletter,is_Cell) 
VALUES 
(ln, fn, address, city, state, zip, email, news, cell); 

END

Apparently, it did not like the "@" in front of the passed values.

Answer 2

Your insert_contact stored procedure have to provide the parameters (with the type) as below:

CREATE PROCEDURE insert_contact
(
    IN cID VARCHAR,
    IN ln VARCHAR(30),
    IN fn VARCHAR(45),
    IN `add` VARCHAR(30),
    IN city VARCHAR(30),
    IN state VARCHAR(10),
    IN zip VARCHAR(20),
    IN email VARCHAR(45),
    IN news bit,
    IN cell bit,
)

BEGIN 

INSERT INTO Contacts 
(contactID,last_name,first_name,address,city,state,zip_code,email_address,newsletter,is_Cell) 
VALUES 
(@cID, @ln, @fn, @add, @city, @state, @zip, @email, @news, @cell); 

END

And in case there is any parameter which conflict with MySQL reserved words, you need to escape the reserved words with single quotes.

Note:

Your contactID column was int(11) type but you provide the cID parameter as VARCHAR type. You need to take concern that the column type was unmatched and possible lead an exception.

---

Reference

MySQL - Working with Stored Procedures