Sql C# Invalid column name

Question

private void button4_Click_1(object sender, EventArgs e)
    {
        string s = textBox1.Text;
        SqlCeConnection conn = new SqlCeConnection(@"Data Source=D:\Desktop\DB2\DB2\Database1.sdf");
        try
        {
            conn.Open();

            SqlCeCommand cmd = new SqlCeCommand(" update Kambariai set Kliento ID = Kliento ID + s Where Kliento ID = 0 ", conn);
            cmd.ExecuteNonQuery();
            toolStripStatusLabel1.Text = "Duomenys įrašyti";
            conn.Close();
        }
        catch (Exception ee)
        {
            MessageBox.Show(ee.Message);
        }
    }

Datatable schema shows that column exists but I still get error that Column name invalid maybe I forgot to write something please help me :) What I am trying to do is take text from textbox1.Text and update it datatable values where coulmn name is Kliento ID and value is 0.

https://i.stack.imgur.com/ZM4LN.jpg enter image description here

you should not be putting spaces in column name, put underscore not whitespaces — Ehsan Sajjad, May 11 '14 at 10:20
Kliento ID = Kliento ID + s?? what is that?? But that is not the questio, you put spaces in your column name, this is why mysql does not understand, surround your column name with `\`` — Anthony Raymond, May 11 '14 at 10:21
You code is subject to SQL injection, better practice would be parameterized queries — Peter, May 11 '14 at 11:23

Raging Bull · Accepted Answer · 2014-05-11T11:18:53.400

1

For MySQL: (since the original question tagged mysql)

Use backticks(`) around the field name.

Try this way: SqlCeCommand cmd = new SqlCeCommand(" update Kambariai set Kliento ID = Kliento ID + "+s+" Where Kliento ID = 0 ", conn);

Side note: Back ticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set it is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

Back ticks are necessary for situations like the following:
```
SELECT id, `my name`, `another field` , `field,with,comma`
```

For SQL Server: (since the image shows sql server)

Try [] instead:

SqlCeCommand cmd = new SqlCeCommand(" update Kambariai set [Kliento ID] = [Kliento ID] + "+s+" Where [Kliento ID] = 0 ", conn);

edited May 11 '14 at 11:18

answered May 11 '14 at 10:20

Raging Bull

18,593
13
50
55

Making Kliento ID with ` ` does not help token line offset= 23, Token in error =` – user3625236 May 11 '14 at 10:25
Hmm still no good it is not about s(textbox) error begins after set `Kliento ID` – user3625236 May 11 '14 at 10:28
@user3625236: `s` is of type string, right? So you need to take it out of the quotes. Edited my answer. – Raging Bull May 11 '14 at 10:31
It has nothing to do with my problem somehow ` characters makes errors – user3625236 May 11 '14 at 10:33
Fixed my problem by renaming Names to klientas no errors now :) Now I will know that names shouldn't be used with whitespaces. Ty for helping me anyways guys. – user3625236 May 11 '14 at 10:39
@user3625236: Your image says you are using SQL Server, but you have tagged mysql. My answer was for MySQL. – Raging Bull May 11 '14 at 11:11
@user3625236: Tagging of invalid RDBMS made the confusion, my friend. You should have been specific over there. – Raging Bull May 11 '14 at 11:16
I am really sorry I was confused by myself. – user3625236 May 11 '14 at 11:24
I can accept only one answer I didn't noticed that gave u accept back :) I got another problem maybe u can help me out? – user3625236 May 11 '14 at 11:34
@user3625236: If it is a small one, we can figure it out here itself. Otherwise you should ask another question. – Raging Bull May 11 '14 at 11:36
http://stackoverflow.com/questions/23592104/c-sharp-sql-column-name-is-not-valid here I think you can help me. – user3625236 May 11 '14 at 11:39

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

1

Try this:

SqlCeCommand cmd = new SqlCeCommand("update Kambariai set [Kliento ID] = [Kliento ID] + " + s + " Where [Kliento ID] = 0", conn);

answered May 11 '14 at 10:46

Tarik

10,810
2
26
40

1

But be aware that putting whitespaces in your column names is the worst naming strategy. – Tolga Evcimen May 11 '14 at 11:13

Sql C# Invalid column name

2 Answers2