Errror: Number of query values and destination fields are not the same

[Lin100]

System.Data.OleDb.OleDbException: 'Number of query values and destination fields are not the same.'
Code crashed at statement below. I did check the Access database and all three fields are there.
cmd.CommandText = "insert into Reservation values ('" + @Reservation_Number + "', '" + Property_Name.Text + "', '" + Unit_Number.Text + "')";

        private void Add_Record_Click(object sender, EventArgs e)
        {
           String Property_ID = "1";
           String Month_Var = "12";
           String Date_Var = "17";
           String Year_Var = "2022";
           String Reservation_Number = "R-" + Property_ID + "-" + Unit_Number.Text + "-" + Month_Var + "-"
                                          + Date_Var + "-" + Year_Var;
           MessageBox.Show(Reservation_Number);

           con.Open();
           OleDbCommand cmd = con.CreateCommand();
           cmd.CommandType = CommandType.Text;
           cmd.CommandText = "insert into Reservation values ('" + @Reservation_Number + "', '" + Property_Name.Text + "', '" + Unit_Number.Text + "')";
           cmd.ExecuteNonQuery();
           con.Close();
           MessageBox.Show("Record inserted successfully.");
        }

 

[Skydiver]

I did check the Access database and all three fields are there.

But the question is: How many total fields are there in that Reservation table? If there is more than 3 then, there is the source of your error.

 

[jmcilhinney]

When you write an INSERT statement without specifying the columns to insert to, it is assumed that you will insert a value into every column in the same order they appear in the table. Otherwise, how would the database know which columns to insert to? It is generally considered bad practice to do that in the first place. If you always specify the columns explicitly, anyone who reads your code - including you in a few months when you've forgotten everything about it - can see exactly what's going on. If you had done that here then either there wouldn't be a problem to begin with or we'd be able to see exactly what it was.

 

[Lin100]

When you write an INSERT statement without specifying the columns to insert to, it is assumed that you will insert a value into every column in the same order they appear in the table. Otherwise, how would the database know which columns to insert to? It is generally considered bad practice to do that in the first place. If you always specify the columns explicitly, anyone who reads your code - including you in a few months when you've forgotten everything about it - can see exactly what's going on. If you had done that here then either there wouldn't be a problem to begin with or we'd be able to see exactly what it was.

I have fix the problem and now when I run it, there is no error message, but the table did not have a new record in it.
1) Create a new table named Reservation_Small with only 3 fields.
2) Line 14 (insert record) changed to include the 3 fields.

       private void Add_Record_Click(object sender, EventArgs e)
        {
           String Property_ID = "1";
           String Month_Var = "12";
           String Date_Var = "17";
           String Year_Var = "2022";
           String Reservation_Number = "R-" + Property_ID + "-" + Unit_Number.Text + "-" + Month_Var + "-"
                                          + Date_Var + "-" + Year_Var;
           MessageBox.Show(Reservation_Number);

           con.Open();
           OleDbCommand cmd = con.CreateCommand();
           cmd.CommandType = CommandType.Text;
           cmd.CommandText = "insert into Reservation_Small (Reservation_Number, Property_Name, Unit_Number) values ('" + Reservation_Number + "', '" + Property_Name.Text + "', '" + Unit_Number.Text + "')";
           cmd.ExecuteNonQuery();
           con.Close();
           MessageBox.Show("Record inserted successfully.");

 

[jmcilhinney]

When you specify the line number(s) to highlight in your code, please don't wrap them in quotes. If you had looked, you would have seen that that failed to highlight the line(s) but it also breaks the rest of the page.

 

[jmcilhinney]

As for the issue, there are only three possible outcomes for that call to ExecuteNonQuery:

1. It fails and throws an exception.
2. It succeeds and returns zero.
3. It succeeds and returns a non-zero value.

Which is it in your case? If it was option 1 then I'm sure you would have told us about it. It is almost certainly option 3, which means that everything is working as it should. Most likely, you are just looking in the wrong database or in the right database at the wrong time. Please explain EXACTLY how you determined that the data is not present and also show us your connection string.

Note that, when things didn't work as expected, you should have immediately read the relevant documentation and found out for yourself what ExecuteNonQuery returned and then looked for yourself and been able to tell us. You always need to do what you can for yourself first and reading the relevant documentation is ALWAYS included in that. Given that you can click the method in code and press the F1 key to go straight to that documentation means there's no good reason not to. You may not always find what you need or understand what you find but you definitely won't if you don't look in the first place.

 

[cjard]

Please stop writing SQL in that way. Never, ever use string concatenation to concatenate user supplied values into an SQL; it's one of the simplest ways to enable hackers to take over the system you've written, and I've worked in places that have fired developers for doing it.

The correct way to pass user supplied values to a database is via a parameterized query. See bobby-tables.com: A guide to preventing SQL injection