Set table name and column name dynamically in SQL statement

Question

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.

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?

What is your ment looks like when you debug your code? What is your RoomId column type? What are your values? You should always use parameterized queries by the way. This kind of string concatenations are open for SQL Injection attacks. And your comboBox1.SelectedItem should be comboBox1.SelectedItem.Text in my opinion. — Soner Gönül
– Soner Gönül, Commented Aug 4, 2015 at 7:49
comboBox1.SelectedItem and comboBox1.Text should both be fine. But it's a bit hard to tell what's wrong with the syntax if we don't know what the query looks like. :) — waka
– waka, Commented Aug 4, 2015 at 8:00
@waka, i printed ment, it says, update Monday set T_8-9='Open' where RoomId='W002' — ApsSanj
– ApsSanj, Commented Aug 4, 2015 at 8:05

Han · Accepted Answer · 2015-08-04 08:36:19Z

1

Your table name or column name might have inproper characters. Wrap them in with character ` in MySQL or brackets in 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();

edited Aug 4, 2015 at 8:36

answered Aug 4, 2015 at 8:11

Han

3,0822 gold badges25 silver badges31 bronze badges

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

Comments

Eddie · Accepted Answer · 2017-10-02 14:22:33Z

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

You can use QuoteIndetifier, here is an example

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

Collectives™ on Stack Overflow

Set table name and column name dynamically in SQL statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related