C# Sql Column name is not valid

Question

private void button4_Click_1(object sender, EventArgs e)
    {
        string s = textBox1.Text;

        string s1 = comboBox1.Text;
        string s2 = comboBox2.Text;
        SqlCeConnection conn = new SqlCeConnection(@"Data Source=D:\Desktop\DB2\DB2\Database1.sdf");
        try
        {
            conn.Open();
            SqlCeCommand cmd = new SqlCeCommand(" update Kambariai set Klientas=[s]  Where [Kambario rūšis]=[s1]  ", conn);
            cmd.ExecuteNonQuery();
            toolStripStatusLabel1.Text = "Duomenys įrašyti";
            conn.Close();
        }
        catch (Exception ee)
        {
            MessageBox.Show(ee.Message);
        }
    }

I am trying to update my datatable by updating Klientas value with textbox1.Text which is made to string = s. It should work fine as Sql But I get an error saying that The column name is not valid Column = s1. s1 shouldn't be targeted as column name it should be used as column row value.

This is outdated image Kliento ID is changed to Klientas

C# variables do not magically get into an SQL query, even if mentioned in there in brackets. See e.g. http://stackoverflow.com/a/8218932/11683 — GSerg, May 11 '14 at 11:43

Raging Bull · Accepted Answer · 2014-05-11T12:01:37.947

1

Try this:

SqlCeCommand cmd = new SqlCeCommand("update Kambariai set Klientas="+s+"  Where [Kambario rūšis]='"+s1+"' ", conn);

Analysis:

From what you have tried, cmd has value like :

update Kambariai set Klientas=s Where [Kambario rūšis]=s1

From by putting proper double and single quotes around it, the value would be like:

update Kambariai set Klientas=1 Where [Kambario rūšis]='bar'

Side Note:

I would not recommend this method since it increases the risk of SQL injection. Use parameterized query instead.

edited May 11 '14 at 12:01

answered May 11 '14 at 11:42

Raging Bull

18,113
13
47
53

Done as suggested above now it understands value of s1 but still it shows that it is column name. – user3625236 May 11 '14 at 11:45
@user3625236: Edited my answer. – Raging Bull May 11 '14 at 11:46
Ok some part works it inserts value to Klientas column but the value is Klientas but textbox1.Text is not Klientas. set Klientas="'+s"' Something must be done with this part . – user3625236 May 11 '14 at 11:52
@GSerg: I am not a big fan of SQL injection, myself. Just trying to help OP to get what he is after. Anyway, Updated my answer. – Raging Bull May 11 '14 at 11:55
Kliento ID is changed to Klientas read in description near Image. – user3625236 May 11 '14 at 12:00
@RagingBull you said you edited answer I don't see anything edited maybe you forgot to edit it? – user3625236 May 11 '14 at 12:01
@user3625236: Since Klientas is of type int, you don't have to use single quotes ('). See the edit. – Raging Bull May 11 '14 at 12:02
Btw how to put multiple values in where section I try Where [Kambario rūšis]='"+s1+"', [Vietų skaičius]='"+s2"' but it shows error token ir error = = – user3625236 May 11 '14 at 12:17
@user3625236: You should use `AND` or `OR` instead of comma. `AND` needs both conditions to be true whereas `OR` needs only one condition to be true. – Raging Bull May 11 '14 at 12:19

score 1 · Answer 2 · answered May 11 '14 at 11:42

1

Try This :

     SqlCeCommand cmd = new SqlCeCommand(" update Kambariai set Klientas='" + s +"'  Where [Kambario rūšis]='" + s1 + "'", conn);

answered May 11 '14 at 11:42

Ramy M. Mousa

5,301
3
37
43

C# Sql Column name is not valid

2 Answers2

Linked