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

本文关键字：设置 动态 SQL 语句 | 更新日期: 2023-09-27 18:06:08

我是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();

给出一个异常。

提示" '-'附近语法错误"。

知道我错过了什么吗?

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

您的表名或列名可能有不正确的字符。在MySQL中用字符'或在MSSQL中用括号括起来。

该软件版本。

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版本。

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();

我知道这个帖子很老了，但是上面@han的正确答案是容易SQL注入的。

你可以使用quoteindefier，下面是一个例子

 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();
                }
            }
相关文章：