Adding textbox values to an SQL database in c#

Question

I'm trying to add values from a textbox into a datagridview, I have asked this question before but I'm now getting a different error saying

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This is the code causing the error

private void SaveBtn_Click(object sender, EventArgs e)
{
    SqlConnection sc = new SqlConnection();
    SqlCommand com = new SqlCommand();
    sc.ConnectionString = ("Data Source=localhost;Initial Catalog=LoginScreen;Integrated Security=True");
    sc.Open();
    com.Connection = sc; 
    com.CommandText = ("INSERT INTO Stock (Prod_ID, Prod_Name, Prod_Cat, Supplier, Cost, Price_1, Price_2, Price_3) VALUES ('"+ProdID.Text+"''"+ProdName.Text+"'+'"+ProdCat.Text+"'+'"+ProdSup.Text+"'+'"+ProdCost.Text+"'+'"+ProdPrice1.Text+"'+'"+ProdPrice2.Text+"'+'"+ProdPrice3.Text+"');");
    com.ExecuteNonQuery();
    sc.Close();
}

my database

No commas between your value data fields, also please parameterize this! http://stackoverflow.com/questions/11905185/executing-query-with-parameters — Alex K., Apr 25 '13 at 13:26

score 5 · Answer 1 · answered Apr 25 '13 at 14:03

The direct cause of the error is omitted commas (",") in the "value" section of the query. You should have put it like that

VALUES ('"+ProdID.Text+"', '"+ProdName.Text+", '+'"+ProdCat.Text+", '+'"+ProdSup.Text+...

instead of

VALUES ('"+ProdID.Text+"''"+ProdName.Text+"'+'"+ProdCat.Text+"'+'"+ProdSup.Text+...

Your code is also vulnerable to so called SQL-injection attack (imagine someone has put '" delete from Stock --' into ProdID.Text: the execution'll result in Stock table clearance)

The recommended way looks something like this:

using(SqlConnection sc = new SqlConnection()) {
  sc.ConnectionString = "Data Source=localhost;Initial Catalog=LoginScreen;Integrated Security=True";
  sc.Open();

  using (SqlCommand com = sc.CreateCommand()) {
    com.CommandText =
      "insert into Stock(\n" + 
      "  Prod_Id,\n" + 
      "  Prod_Name,\n" +
      "  Prod_Cat,\n" +
      "  Supplier,\n" +
      "  Cost,\n" +
      "  Price_1,\n" +
      "  Price_2,\n" +
      "  Price_3)\n" +
      "values(\n" +
      "  @prm_Prod_Id,\n" +
      "  @prm_Prod_Name,\n" +
      "  @prm_Prod_Cat,\n" +
      "  @prm_Supplier,\n" +
      "  @prm_Cost,\n" +
      "  @prm_Price_1,\n" +
      "  @prm_Price_2,\n" +
      "  @prm_Price_3)";

    //TODO: Change my arbitrary "80" to actual Stock fields' sizes! 
    com.Parameters.Add("@prm_Prod_Id", SqlDbType.VarChar, 80).Value = ProdID.Text;
    com.Parameters.Add("@prm_Prod_Name", SqlDbType.VarChar, 80).Value = ProdName.Text;
    com.Parameters.Add("@prm_Prod_Cat", SqlDbType.VarChar, 80).Value = ProdCat.Text;
    com.Parameters.Add("@prm_Supplier", SqlDbType.VarChar, 80).Value = ProdSub.Text;
    com.Parameters.Add("@prm_Cost", SqlDbType.VarChar, 80).Value = ProdCost.Text;
    com.Parameters.Add("@prm_Price_1", SqlDbType.VarChar, 80).Value = ProdPrice1.Text;
    com.Parameters.Add("@prm_Price_2", SqlDbType.VarChar, 80).Value = ProdPrice2.Text;
    com.Parameters.Add("@prm_Price_3", SqlDbType.VarChar, 80).Value = ProdPrice3.Text;

    com.ExecuteNonQuery();
  }
}

score 3 · Answer 2 · edited May 23 '17 at 12:04

You are missing commas in your values part of your sql. When ever you are doing something like this (big concatination of a string) you should know two things. First, a good way to test is to write out to console, messagebox, ext. You often will see the error right away. The next thing to know is that if you are concatintating to insert into a DB, dont do it. Use parameterized queries. -> How do parameterized queries help against SQL injection?

com.CommandText = ("INSERT INTO Stock (Prod_ID, Prod_Name, Prod_Cat, Supplier, Cost, Price_1, Price_2, Price_3) VALUES ('"+ProdID.Text+"''"+ProdName.Text+"'+'"+ProdCat.Text+"'+'"+ProdSup.Text+"'+'"+ProdCost.Text+"'+'"+ProdPrice1.Text+"'+'"+ProdPrice2.Text+"'+'"+ProdPrice3.Text+"');");

should be something like this

   com.CommandText = (@"INSERT INTO Stock (Prod_ID, Prod_Name, Prod_Cat, Supplier, Cost, Price_1, Price_2, Price_3) VALUES ('"+ProdID.Text+"','"+ProdName.Text+"','"+ProdCat.Text+"','"+ProdSup.Text+"','"+ProdCost.Text+"','"+ProdPrice1.Text+"','"+ProdPrice2.Text+"','"+ProdPrice3.Text+"');"));

@ReeceCottam please make sure you read my last edit about SQL Injection. Its very bad to take input from a user and build a query using concatenation. Very Very bad. I mean the worst thing you can do. — Scott Adams, Apr 25 '13 at 13:34

score 1 · Answer 3 · answered Apr 25 '13 at 13:29

1

Checkbox values in a form either result in nothing whatsoever if no boxes are checked, or a comma delimted list of values. The worst thing you can possibly do is to store this list in a single record. That would result in unusable data.

Instead, you want to change not only your code, but possible your database design so that you have a single record for every box that was checked. Remember to account for the scenario where no boxes are checked.

answered Apr 25 '13 at 13:29

Dan Bracuk

20,699
4
26
43

+1, and there are a lot of there problems here. SQL injection issues, DB access from ui, no using statement. Hopefully this is just a test/ learning app. – Scott Adams Apr 25 '13 at 13:43

score 0 · Answer 4 · answered Apr 25 '13 at 13:28

0

Try :

com.CommandText = ("INSERT INTO Stock (Prod_ID, Prod_Name, Prod_Cat, Supplier, Cost, Price_1, Price_2, Price_3) VALUES ('"+ProdID.Text+"','"+ProdName.Text+"','"+ProdCat.Text+"','"+ProdSup.Text+"','"+ProdCost.Text+"','"+ProdPrice1.Text+"','"+ProdPrice2.Text+"','"+ProdPrice3.Text+"');");

answered Apr 25 '13 at 13:28

Obama

2,586
2
30
49

This is an option, but it's vulnerable to SQL injection. It's better to use prepared statements. – Stefan Apr 26 '13 at 13:29
@StefanM but this is what he wants! – Obama May 01 '13 at 14:23

score -1 · Answer 5 · edited Apr 25 '13 at 13:45

You should Replace

 ('"+ProdID.Text+"''"+ProdName.Text+"'+'"+ProdCat.Text+"'+'"+ProdSup.Text+"'+'"+ProdCost.Text+"'+'"+ProdPrice1.Text+"'+'"+ProdPrice2.Text+"'+'"+ProdPrice3.Text+"');");`

with

('"+ProdID.Text+"','"+ProdName.Text+"','"+ProdCat.Text+"','"+ProdSup.Text+"','"+ProdCost.Text+"','"+ProdPrice1.Text+"','"+ProdPrice2.Text+"','"+ProdPrice3.Text+"');");`

(VALUES part needs commas for each column)

Adding textbox values to an SQL database in c#

5 Answers5