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

Hints for debug & fix: Check whether which table column is NOT NULLABLE, and you are providing NULL value to that column or you didn't provide any value to that column. Would be great if you can also provide the table schema. — Yong Shun, Apr 16 '22 at 01:40
And does your Stored Procedure script contains parameters such `CREATE PROCEDURE insert_contact (@ciD varchar, /* The rest parameters */)`? As you just post the part of the Stored Proc. Make sure that in your C# code you provide all the parameters with the correct type and value according to the Stored Proc.'s parameters. — Yong Shun, Apr 16 '22 at 01:48
CREATE TABLE `Contacts` ( `contactID` int(11) NOT NULL AUTO_INCREMENT, `last_name` varchar(30) NOT NULL, `first_name` varchar(30) NOT NULL, `address` varchar(45) DEFAULT NULL, `city` varchar(30) DEFAULT NULL, `state` varchar(10) DEFAULT NULL, `zip_code` varchar(20) DEFAULT NULL, `email_address` varchar(45) NOT NULL, `pref_phone` varchar(30) DEFAULT NULL, `newsletter` tinyint(1) NOT NULL DEFAULT 0, `is_Cell` tinyint(1) DEFAULT 0, PRIMARY KEY (`contactID`), UNIQUE KEY `email_address` (`email_address`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 — Pherix, Apr 16 '22 at 01:54
I used MySql Workbench to create the procedure, and it shows this: CREATE DEFINER=``@`%` PROCEDURE `insert_contact`() 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 — Pherix, Apr 16 '22 at 01:58

Yong Shun · Answer 1 · 2022-04-16T02:17:26.907

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

score 0 · Accepted Answer · answered Apr 30 '22 at 15:19

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.

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

2 Answers2