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 — devNull
– devNull, Commented Apr 5, 2020 at 6:06
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Apr 5, 2020 at 7:50
Generic advise when you have an error with some concatenated command string: check what is that final string exactly? — Hans Keﬆing
– Hans Keﬆing, Commented Apr 5, 2020 at 9:09

jason.kaisersmith · Accepted Answer · 2020-04-05 06:05:46Z

2

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.

answered Apr 5, 2020 at 6:05

jason.kaisersmith

9,7233 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

1878 alianware Over a year ago

i not very familiar with sqlparameter. but thank you for your remind.

Collectives™ on Stack Overflow

how to fix sql query using stringbuilder loop.TQ

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related