在SQL语句中动态设置表名和列名

I am new to C#. I have to get inputs from windows form and execute a sql statement. Here I have to get the table name and column name from user inputs. I wrote a code like this.

我是C#的新手。我必须从Windows窗体获取输入并执行sql语句。在这里,我必须从用户输入中获取表名和列名。我写了这样的代码。

string ment = String.Format("update {0} set {1} ='" + radioButton1.Text + "' where RoomId='" + textBox8.Text + "'", textBox7.Text, comboBox1.SelectedItem); 
cmd = new SqlCommand(ment, con);
cmd.ExecuteNonQuery();

This gives an exception.

这给了一个例外。

It says "Incorrect syntax near '-'".

它说“''''附近的语法不正确。

Any idea on what I missed?

我错过了什么?

2 个解决方案

#1

Your table name or column name might have inproper characters. Wrap them in with character ` in MySQL or brackets in MSSQL.

您的表名或列名可能包含不正确的字符。用MySQL中的字符`或MSSQL中的括号包装它们。

MSSQL version.

string ment = String.Format("update [{0}] set [{1}] ='" + radioButton1.Text + "' where RoomId='" + textBox8.Text + "'", textBox7.Text, comboBox1.SelectedItem); 
cmd = new SqlCommand(ment, con);
cmd.ExecuteNonQuery();

MySQL version.

string ment = String.Format("update `{0}` set `{1}` ='" + radioButton1.Text + "' where RoomId='" + textBox8.Text + "'", textBox7.Text, comboBox1.SelectedItem); 
cmd = new SqlCommand(ment, con);
cmd.ExecuteNonQuery();

#2

i know this thread is old but the correct answer above from @han is sql injection prone..

我知道这个帖子已经老了,但@han上面的正确答案是sql注入的问题。

You can use QuoteIndetifier, here is an example

你可以使用QuoteIndetifier,这是一个例子

 StringBuilder SQLtext = new StringBuilder();
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder();
            string MyColumn = sqlBuilder.QuoteIdentifier(Radio_range.SelectedValue);
            SQLtext.AppendLine(" With ctemp as( ");
            SQLtext.AppendLine(" select convert(varchar(10),sysDate,102) sysDate,convert(varchar(10),WeekDate,102) WeekDate,[Month],[Quarter],[Year] ");
            SQLtext.AppendLine(" from sysCalendar ");
            SQLtext.AppendLine(" where sysdate<=(select max(nominal_date) from ATTENDANCE_AGENT_T) ");
            SQLtext.AppendLine(" and sysDate>=dateadd(MONTH,-12,getdate()) ");
            SQLtext.AppendLine(" ) ");
            SQLtext.AppendFormat(" select distinct {0} as mydate from ctemp order by {1}  desc ", MyColumn, MyColumn);
            string constr = ConfigurationManager.ConnectionStrings["CIGNAConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(SQLtext.ToString()))
                {
                    cmd.CommandType = CommandType.Text;
                    //cmd.Parameters.AddWithValue("@mydate", Radio_range.SelectedValue);
                    cmd.Connection = con;
                    con.Open();
                    DropDownList_Date.DataSource = cmd.ExecuteReader();
                    DropDownList_Date.DataTextField = "mydate";
                    DropDownList_Date.DataValueField = "mydate";
                    DropDownList_Date.DataBind();
                    con.Close();
                }
            }

#1