SQL Insert Error Through Runtime Control In FLP

[maheshwag]

am using C#, VS-2005, and SQL-2000

am trying to insert into sql database via 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);
   }
}

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

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

but it's gives "incorrect syntex error near values"

I don't know what's wrong with the code please help me.

[MartijnHoekstra]

syntax should be:

insert into dummy (name,amount) values ( 'text','amount' );

meaning you are missing the bold parts: as in: ( ' ' , ' ' )

[maheshwag]

sir, how can i do it in foreachloop when my controls like two textboxes as above code in FLP and they are increment may be it's two or three records so how can i perform it. please guide me proper.

thx for ur feedback.

[MartijnHoekstra]

uncertain to me is what you exactly want, and what the controls exactly refer to.

You want to add a 'Name' and 'Amount' - pair in database.
Am I correct that the controls containing 'tb' contain names, and 'bb' contain amounts ?

[maheshwag]

as per my above code how to i modify it as per ur suggestion i don't know how to it with foreach loop where as am not familiar of stringbuilder so how to i quot it with it. how to append sql query in correct format with stingbuilder that's what i am demanding.

[MartijnHoekstra]

Try something like this:

sb.Append("insert into dummy(name,amount)values"); 

bool first=true;
string values = "";
foreach (Control ctl in this.flowLayoutPanel1.Controls)  
{ 
   if (ctl.Name.Contains("tb") && ctl is TextBox) 
   {
      if (!first) sb.Append(","); first=false;
      values = "('" + ctl.Text + "'";
   }
   if(ctl.Name.Contains("bb") && ctl is TextBox) 
   {
      values += ",'" + ctl.Text + "')";
      sb.Append(values); 
   } 
}

This will most likely NOT work, but the SQL syntax would be correct.
Since I am not sure what refers to 'Name' or 'Amount', you need to make the adjustments properly.

[maheshwag]

very true sir here am accept ur answer. it's my mistake that i not tell u in details. exactly sir the name tb and bb in control collection is textbox u know. but it's increment by user it may two or three whatever it's upto user. in short it's in multiple that's why am demanding this way. Anyway i have done it in parameterized way. once again thx.