Update Table, Where Column Name is selected dynamically - DropDown.SelectedValue, C#, VB.NET

Question

Hi,

I want to Update Column of a Table where ColumnName will be DropDown.SelectedValue.

Example: A set of RECORDS will be displayed from a Customer Table where CUstNo ='1234' and City= 'Chicago' and Grade ='B'

Once displayed I want to Update the grade to 'A' of all those customers from the above criteria.

In My case I have like 100 Columns, so in where Clause Column Names will be selected from a DaropDown.

In My Query, Update Customer SET ColumnName= 'some value' where ColumnName ='ActualValue'

So how can I pass the ColumnName which is Dropdown Selected Value.

I believe I can't give as Update Customer SET DropDown.SelectedValue = 'some value' where DropDown.SelectedValue ='ActualValue'

how can I resolve this ?...

Answer 1

So it sounds like you want to build dynamic sql based on your grid? If that is the case you could always do something like

string updateStmt = String.Format("UPDATE Customer SET {0} = 'some value' WHERE...", dropDown.SelectedValue);

And then you could execute this statement. I don't really know exactly what you are using but there are far better ways of making db updates. You could use an ORM like Linq to Sql or Entity Framework or you could even use something like a SqlCommandBuilder

EDIT: Here is an example in ASP.NET (although a winform app would be very similar)

Assume this dropdownlist is populated from some datasource

<form id="form1" runat="server" method="post" target="Default2.aspx">              
    <asp:DropDownList ID="test" runat="server" AutoPostBack="true" OnSelectedIndexChanged="test_SelectedIndexChanged">
        <asp:ListItem Value="" Selected="True"/>
        <asp:ListItem Value="Col1"/>
        <asp:ListItem Value="Col2"/>
        <asp:ListItem Value="Col3"/>
    </asp:DropDownList>
</form>

In the event handler build the updateStatement based on the selected column

protected void test_SelectedIndexChanged(object sender, EventArgs e)
{
    string updateStatement = String.Format("UPDATE Customer SET {0} = 'some value' WHERE {0} = 'some other value'", test.SelectedValue);
    //execute the updatestatement;
}

The update statement string will look like this after the string is initialized(if Col1 is selected):

"UPDATE Customer SET Col1 = 'some value' WHERE Col1 = 'some other value'"

EDIT 2: Ok here is a sample in a winforms app

First i added a few values to the combobox. Next when my button on the form is clicked i take the selected value from the combo box and use it in the dynamic sql string. It will give you the same result as the asp.net solution above.

public Form1()
    {
        InitializeComponent();
        //add values to combobox from some datasource
        comboBox1.Items.Add("Col1");
        comboBox1.Items.Add("Col2");
        comboBox1.Items.Add("Col3");
    }

    private void button1_Click(object sender, EventArgs e)
    {
        string updateStatement = String.Format("UPDATE Customer SET {0} = 'some value' WHERE {0} = 'some other value'", comboBox1.SelectedItem);
        //execute the updatestatement;
    }