SqlParameterCollection accepts only non-zero objects of type SqlParameter, not DBNull objects

Question

Whenever I enter anything or zero into text fields, an error appears. My code is as follows:

SqlCommand cmd = con.CreateCommand(); cmd.CommandText = "INSERT INTO Records ([Student ID], [First Name], [Last Name], [Middle Initial], Gender, Address, Status, Year, Email, Course, [Contact Number]) VALUES (@StudentID, @FirstName, @LastName , @MiddleInitial, @Gender, @Address, @Status, @Year, @Email, @Course, @ContactNumber)"; SqlParameter p1 = new SqlParameter("@StudentID", SqlDbType.NChar); p1.Value = textBox1.Text; cmd.Parameters.Add(p1); SqlParameter p2 = new SqlParameter("@FirstName", SqlDbType.NVarChar); p2.Value = textBox2.Text; cmd.Parameters.Add(p2); SqlParameter p3 = new SqlParameter("@LastName", SqlDbType.NVarChar); p3.Value = textBox3.Text; cmd.Parameters.Add(p3); SqlParameter p4 = new SqlParameter("@MiddleInitial", SqlDbType.NChar); p4.Value = comboBox1.Text; cmd.Parameters.Add(p4); SqlParameter p5 = new SqlParameter("@Gender", SqlDbType.NChar); p5.Value = comboBox2.Text; cmd.Parameters.Add(p5); SqlParameter p6 = new SqlParameter("@Address", SqlDbType.VarChar); p6.Value = textBox4.Text; cmd.Parameters.Add(p6); SqlParameter p7 = new SqlParameter("@Status", SqlDbType.NChar); p7.Value = comboBox3.Text; cmd.Parameters.Add(p7); SqlParameter p8 = new SqlParameter("@Year", SqlDbType.VarChar); p8.Value = comboBox4.Text; cmd.Parameters.Add(p8); SqlParameter p9 = new SqlParameter("@Email", SqlDbType.VarChar); p9.Value = textBox5.Text; cmd.Parameters.Add(p9); SqlParameter p10 = new SqlParameter("@Course", SqlDbType.VarChar); p10.Value = comboBox5.Text; cmd.Parameters.Add(p10); SqlParameter p11 = new SqlParameter("@ContactNumber", SqlDbType.VarChar); p11.Value = textBox6.Text; cmd.Parameters.Add(p11); textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = ""; textBox6.Text = ""; comboBox1.Text = ""; comboBox2.Text = ""; comboBox3.Text = ""; comboBox4.Text = ""; comboBox5.Text = ""; if (cmd.Parameters.Contains(System.DBNull.Value)) { MessageBox.Show("Please complete the fields", "Information...", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } else { MessageBox.Show("Data Inserted!", "Information ... ", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); } cmd.ExecuteNonQuery(); con.Close(); 

Error included:

 if(cmd.Parameters.Contains(System.DBNull.Value)) 

I am using SQL Server and C #.

Answer 1

 SqlCommand cmd = con.CreateCommand(); cmd.CommandText = "INSERT INTO Records ([Student ID], [First Name], [Last Name], [Middle Initial], Gender, Address, Status, Year, Email, Course, [Contact Number]) VALUES (@StudentID, @FirstName, @LastName , @MiddleInitial, @Gender, @Address, @Status, @Year, @Email, @Course, @ContactNumber)"; Control[] controls = {textBox1,textBox2, textBox3, textBox4, textBox5, textBox6, comboBox1, comboBox2, comboBox3, comboBox4, comboBox5}; foreach(Control c in controls){ if(c.Text.Trim() == "") { MessageBox.Show("Please complete the fields", "Information...", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); c.Focus();//Focus it to let user enter some value again. return; } } //Initialize your parameters here //.... //.... //.... try { cmd.ExecuteNonQuery(); MessageBox.Show("Data Inserted!", "Information ... ", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); foreach(Control c in controls) c.Text = ""; }catch{} finally { con.Close(); } 

Answer 2

First of all, you need to find out if the value property is any of the DBNull parameters. You are trying to check if the object itself is a DBNull parameter, but you should look at the Value property. You can do it like this:

 if ( cmd.Parameters.Any(p => DBNull.Value.Equals(p.Value)) ) 

Secondly, the .Text property of your text .Text control will never be null. In the worst case, it will be an empty string. So what you really want is the code:

 if ( cmd.Parameters.Any(p => string.IsNullOrWhiteSpace((string)p.Value)) ) 

Finally, aside, even if the .Text property can be null , it is not the same as DBNull . These are two different things.

Answer 3

A clean and efficient approach. I will help you in subsequent events. Change your code like this:

• Create a class called student and inside this class create properties for FirstName , LastName and others as required. Something like that:

   public class Student { public int StudentId {get;set;} public string FirstName {get;set;} public string FirstName {get;set;} public string MiddleInitial{get;set;} // upto ContactNumber or as required. } 

• Create a separate method to assign SqlCommand parameters. Here in these methods you can also put checks if you need to use the ternary operations ie?,:

   private void CreateParameterList(ref Student s, ref SqlCommand cmd) { var parameters = new [] { new SqlParameter("@FirstName",s.FirstName), new SqlParameter("@LastName",s.LastName), : : new SqlParameter("@ContactNumber",s.ContactNumber) } cmd.Parameters.AddRange(parameters); } 

• Create a save method and pass the above class as a parameter. You can also create a separate partial class called Student to access members using the same instance. Call the method in step 2 to use the parameters.

   protected void MySaveFunction(Student s) { using(SqlConnection con= new SqlConnection(your_Connection_String)) { using(SqlCommmand cmd = new SqlCommand()) { cmd.CommandType = CommandType.Text/StoredProcedure; // it depends on what you are using cmd.Connection = con; cmd.CommandText=Your Query or Name of SP; con.Open(); CreateParameterList(ref s, ref cmd); try { int i = cmd.ExecuteNonQuery(); if(i>0) { //Show Success or return Success } } catch(SqlException sqlex) { //catch and log exception } finally { con.Close(); con.Dispose() } } } } 

Answer 4

Why don't you start over and do the right check before you reach this point? Never start creating objects and stuff if you don't need it. So do yourself a favor and add a verification method, for example, to

 private bool IsValid() { return !Controls.OfType<TextBox>().Any(c=> String.IsNullOrEmpty(c.Text)); } 

and then add a call immediately before the code to create the sqlcommand command and a collection of parameters

 if(IsValid()) { // do you stuff here } else { MessageBox.Show("Please complete the fields", "Information...", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } 

Keep in mind that this has not been verified, I do not have VS now, and everything is written by hand, so maybe the Control Collection does not have access this way.