SQL error on insert statments with runtime controls

Question

I am using C#, VS 2005 and SQL 2000

My code is:

StringBuilder sb = new StringBuilder();
sb.Append("insert into dummy(name,amount)values");
foreach (Control ctl in this.flowLayoutPanel1.Controls) 
{
  if (ctl.Name.Contains("tb") && ctl is TextBox) 
  {
     sb.Append(ctl.Text);
  }
} 

foreach(Control bbl in this.flowLayoutPanel1.Controls)
{
   if(bbl.Name.Contains("bb") && bbl is TextBox)
   {
     sb.Append(bbl.Text);
   }
}

SqlCommand cmd = new SqlCommand(sb.ToString(), con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

It throws an error like this:

Incorrect syntax near values

Please help me.

Answer 1

Looks like you are missing a space after the brackets and after VALUES. And you are missing an opening and closing bracket for your VALUES statement. You will also need to add commas after the values that you are inserting to seperate them. So your syntax should look something like:

insert into dummy(name,amount) values (textBox1value, textBox2value)

EDIT

Assuming that you only have 2 TextBox controls in your flowLayoutPanels, then you could do the following:

StringBuilder sb = null; 
sb = new StringBuilder(); 
sb.Append("insert into dummy(name,amount)values ("); 
foreach (Control ctl in this.flowLayoutPanel1.Controls)  
{ 
   if (ctl.Name.Contains("tb") && ctl is TextBox)  
  { 
     sb.Append(ctl.Text); 
  } 
}  

sb.Append(",");

foreach(Control bbl in this.flowLayoutPanel1.Controls) 
{ 
   if(bbl.Name.Contains("bb") && bbl is TextBox) 
   { 
    sb.Append(bbl.Text); 
   } 
} 

sb.Append(")");

But, I would strongly look into the solution suggested by Jon.

Answer 2

@Ardman has mentioned the syntax errors, but there's something much more important: you should not be appending user-entered values into your SQL like this.

Use a parameterized SQL statement instead, and set the values into the parameters. Otherwise you're open to SQL injection attacks.

Answer 3

The values themselves need to be in brackets and separated by commas as well.

I think your code produces this:

insert into dummy(name,amount)valuesthisname100

But you need to change it to produce this:

INSERT INTO dummy (name, amount) VALUES ('thisname', 100)

some example code that will do this is:

StringBuilder sb = null;
sb = new StringBuilder();
sb.Append("insert into dummy(name,amount)values (");
foreach (Control ctl in this.flowLayoutPanel1.Controls) 
{
   if (ctl.Name.Contains("tb") && ctl is TextBox) 
   {
      sb.Append("'" + ctl.Text + "'");
   }
} 
sb.Append(", ");
foreach(Control bbl in this.flowLayoutPanel1.Controls)
{
   if(bbl.Name.Contains("bb") && bbl is TextBox)
   {
       sb.Append(bbl.Text);
   }
}
sb.Append(")");
SqlCommand cmd1 = new SqlCommand(sb.ToString(), con);
cmd1.CommandType = CommandType.Text;
cmd1.ExecuteNonQuery();

This code is far from ideal, but it should fix your SQL syntax error. Some other enhancements you should think about are:

• Make sure only one text box is ever found in each of the foreach loops. If more than one then the field count won't match.
• Put validation or fix-up code in to ensure that no single quote characters appear in text thats entered by the user, or change the SQL to use parameters (thanks Jon Skeet).
• Put validation to ensure that your second text box is parseable as a number (see Int.TryParse()), assuming that your Amount field is a numeric.

However, a MUCH better way would be to do this (EDITED to help mahesh with his coding, now includes multiple inserts):

string sName = null;
double? nAmount = null;

foreach (Control ctl in this.flowLayoutPanel1.Controls) 
{
   if (ctl.Name.Contains("tb") && ctl is TextBox) sName = ctl.Text;
   if (ctl.Name.Contains("bb") && ctl is TextBox) 
   {
       double nTmp = 0;
       if (double.TryParse(ctl.Text, out nTmp)) nAmount = nTmp;
   }

   if (sName != null && iAmount != null) 
   {
      SqlCommand cmd1 = new SqlCommand("INSERT INTO dummy (name, amount) VALUES (@name, @amount)", con);
      cmd1.Parameters.Add("@name", SqlDbType.VarChar).Value = sName;
      cmd1.Parameters.Add("@amount", SqlDbType.Decimal).Value = nAmount;
      cmd1.ExecuteNonQuery();
      sName = null;
      nAmount = null;
   }
}