CODEWITHSUNDEEP
c#asp.netsql-serverdatabase
Ger Mc
Ger Mc

Reputation: 640

using parameters for unknown column name s?

Is it possible to write a query similar to the one in my code below where the column that is set can vary depending on user selection from the front end? In this case @Column and @Value are selected from an interface

  SqlConnection connect = new SqlConnection(ConfigurationManager.ConnectionStrings
["connectToEnterpriseAssignmentDB"].ToString());
                SqlCommand cmd1 = new SqlCommand();

                string currency = lstCurrency.SelectedItem.Value.ToString();
                string columnCurr = lstColumnCurr.SelectedItem.Value.ToString();
                double value = double.Parse(txtValue.Text);
                currency= currency.Trim();
                columnCurr=columnCurr.Trim();         

                cmd1.CommandText = "UPDATE CurrencyTbl SET @Column = '@Value' WHERE CurrencyName = '@Currency'";

                cmd1.Parameters.Add("@Currency", SqlDbType.Char).Value = currency;
                cmd1.Parameters.Add("@Column", SqlDbType.Char).Value = columnCurr;
                cmd1.Parameters.Add("@Value", SqlDbType.Float).Value = value;

                cmd1.Connection = connect;
                connect.Open();
                cmd1.ExecuteNonQuery();

                connect.Close();

Upvotes: 0

Views: 115

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You cannot use column name as variable, instead you can use that from C# directly as below:

SqlConnection connect = new SqlConnection(ConfigurationManager.ConnectionStrings
["connectToEnterpriseAssignmentDB"].ToString());
                SqlCommand cmd1 = new SqlCommand();

                string currency = lstCurrency.SelectedItem.Value.ToString();
                string columnCurr = lstColumnCurr.SelectedItem.Value.ToString();
                double value = double.Parse(txtValue.Text);
                currency= currency.Trim();
                columnCurr=columnCurr.Trim();         

                cmd1.CommandText = "UPDATE CurrencyTbl SET "+columnCurr + " = '@Value' WHERE CurrencyName = '@Currency'";

                cmd1.Parameters.Add("@Currency", SqlDbType.Char).Value = currency;
                //cmd1.Parameters.Add("@Column", SqlDbType.Char).Value = columnCurr;
                cmd1.Parameters.Add("@Value", SqlDbType.Float).Value = value;

                cmd1.Connection = connect;
                connect.Open();
                cmd1.ExecuteNonQuery();

                connect.Close();

Upvotes: 2

Related Questions