how to fix sql query using stringbuilder loop.TQ

Question

How to fix the SQL query that come out like this

insert into T (NO_ID, Room, RoomID, Name,)
values (10, 10, 10, 10,)

The button 3 when click will save the data to SQL

private void button3_Click(object sender, EventArgs e)
{
    string[] text = new string[DT.Columns.Count];

    foreach (DataColumn DC in DT.Columns)
    {
        for (int k = 0; k < DT.Columns.Count; k++)
        {
            // to save the datacolumn headertext name to string[]
            text[k] = DC.Table.Columns[k].ToString();
        }
    }
}

The SQL parts t11 is SQL connection string

SqlConnection SC = new SqlConnection(T11);
SC.Open();

// SQL query parts
StringBuilder command = new StringBuilder("insert into ");
//T33 is the table name
command.Append(T33).Append("(");

// I use the forloop to keep add string on `string[]`
for (int i = 0; i < DT.Columns.Count; i++)
{
    command.Append(text[i]+",");
}

command.Append(")values(");

for (int l= 0; l < DT.Columns.Count; l++)
{
    command.Append("10"+",");
}
command.Append(")");

using (SqlCommand sqlCommand = new SqlCommand(command.ToString(), SC))
{
    sqlCommand.ExecuteNonQuery();
}

The error screenshot:

enter image description here

Does this answer your question? [Best way to remove the last character from a string built with stringbuilder](https://stackoverflow.com/questions/17215045/best-way-to-remove-the-last-character-from-a-string-built-with-stringbuilder) — devNull, Apr 05 '20 at 06:06
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Apr 05 '20 at 07:50
Generic advise when you have an error with some concatenated command string: check what *is* that final string exactly? — Hans Keﬆing, Apr 05 '20 at 09:09

score 2 · Accepted Answer · answered Apr 05 '20 at 06:05

You are generating your inputs in a loop by adding the value and a comma. This then means that you end up with 1 comma too many like this;

values(10,10,10,10,

So after you have generated your inputs you need to remove this. You can do this be reducing the length of the Stringbuilder, like this;

for (int i = 0; i < DT.Columns.Count; i++)
{
    command.Append(text[i]+",");
}

//Reduce the length of StringBuilder by 1
sb.Length--;
command.Append(")values(");

for (int l= 0; l < DT.Columns.Count; l++)
{
    command.Append("10"+",");
}

//Reduce the length of StringBuilder by 1
sb.Length--;
command.Append(")");

BTW: You need to switch to using AddParameter for your values, it is safer against SQL injection.

i not very familiar with sqlparameter. but thank you for your remind. — 1878 alianware, Apr 05 '20 at 06:29

how to fix sql query using stringbuilder loop.TQ

1 Answers1