System.Data.SqlClient.SqlException: Invalid column name (Error at line command.ExecuteNonQuery();)

Question

So I am getting that error all the time. Im not sure if the problem is in the SQL-server. This code for inserting the data into the database.

Send help.

I'm getting this message while executing the code

private void registracija_btn_Click(object sender, EventArgs e)                        
{
        string RegistracijaUporabnisko = RegistracijaUporabnisko_txt.Text;
        string RegistracijaGeslo = RegistracijaGeslo_txt.Text;
        string RegistracijaMail = RegistracijaMail_txt.Text;

            try
            {
                string queryReg = "INSERT INTO uporabnik2(uporabnisko_ime, geslo, email) " +
                     "VALUES(" + RegistracijaUporabnisko + ", " + RegistracijaGeslo + ", " + RegistracijaMail + ")";

                using (SqlCommand command = new SqlCommand(queryReg, con))
                {
                    con.Open();
                    command.ExecuteNonQuery();
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Napaka: " + ex);
            }

    }

The error message is clear enough na? It says the column name `geslo` is invalid/not found in table `uporabnik2` cross check your table name/column name — sujith karivelil, Feb 13 '19 at 08:59
Please include the table field list as the error itself seems pretty clear and definitive.. is "rok" one of the values of your variables? — BugFinder, Feb 13 '19 at 09:01
You should also read up on [Parameterised Queries](https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet) to help avoid SQL injection vulnerabilities — Diado, Feb 13 '19 at 09:04
@sujithkarivelil no one likes images with a bunch of text in, and not all image sites are available to all of us to look — BugFinder, Feb 13 '19 at 09:05
We collect questions and answers to serve as resource for people who are facing problems while programming. A problem adding images is that they may not be available in the future, but, as we're using Input, that's unlikely to happen. — abestrad, Feb 13 '19 at 09:07
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Feb 13 '19 at 09:41

score 3 · Answer 1 · answered Feb 13 '19 at 09:09

Parameters (below) fixes this and a range of other problems, including SQL injection, i18n/l10n, etc. It is also possible that you've simply typo'd a column name, in which case we can't help you with that as we don't know the real name.

string queryReg = "INSERT INTO uporabnik2(uporabnisko_ime, geslo, email) " +
     "VALUES(@uporabnisko_ime, @geslo, @email)";

using (SqlCommand command = new SqlCommand(queryReg, con))
{
    con.Open();
    command.Parameters.AddWithValue("@uporabnisko_ime", RegistracijaUporabnisko_txt.Text);
    command.Parameters.AddWithValue("@geslo", RegistracijaGeslo_txt.Text);
    command.Parameters.AddWithValue("@email", RegistracijaMail_txt.Text);
    con.Close();
}

I also never tire of recommending tools like Dapper for things like this:

con.Execute("INSERT INTO uporabnik2(uporabnisko_ime, geslo, email) " +
     "VALUES(@uporabnisko_ime, @geslo, @email)", new {
    uporabnisko_ime = RegistracijaUporabnisko_txt.Text,
    geslo = RegistracijaGeslo_txt.Text,
    email = RegistracijaMail_txt.Text });

which does everything including (if necessary) the connection open/close, command construction, parameter packing, etc.

Thank you very much! This worked for me and I have learnt a lot! — Rok Šekoranja, Feb 13 '19 at 09:31

score 3 · Accepted Answer · answered Feb 13 '19 at 09:15

Lets look at your code:

      string queryReg = "INSERT INTO uporabnik2(uporabnisko_ime, geslo, email) " +
             "VALUES(" + RegistracijaUporabnisko + ", " + RegistracijaGeslo + ", " + RegistracijaMail + ")";

        using (SqlCommand command = new SqlCommand(queryReg, con))
        {
            con.Open();
            command.ExecuteNonQuery();
            con.Close();
        }

Ok so if RegistracijaUporabnisko has the value Rok, RegistracijaGeslo the value Rok and RegistracijaMail the value Rok@home.. what does your string now look like? well

 string queryReg = "INSERT INTO uporabnik2(uporabnisko_ime, geslo, email) VALUES(Rok,Rok,Rok@home)";

the Rok it would look for then is a field, not a value. Hence it says invalid column.

So what if you did it a commonly adopted way of

  string queryReg = "INSERT INTO uporabnik2(uporabnisko_ime, geslo, email) VALUES(@RegistracijaUporabnisko ,@RegistracijaGeslo ,@email)";

    using (SqlCommand command = new SqlCommand(queryReg, con))
    {
        command.Parameters.AddWithValue("@RegistracijaUporabnisko ", RegistracijaUporabnisko );
        command.Parameters.AddWithValue("@RegistracijaGeslo ", RegistracijaGeslo );
        command.Parameters.AddWithValue("@email", email);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }

What happens now? Well, behind the scenes text is entered with quotes round it, dates are sent in an appropriate format, numbers all that.. handled for you. It protects you from injection so that if I entered a name of "; drop table uporabnik2 you wouldnt find yourself losing the table etc.

Thank you very much! This worked for me and I have learnt a lot! — Rok Šekoranja, Feb 13 '19 at 09:31
@RokŠekoranja Take a [tour] to learn how you can accept answers by clicking on checkmark near the answer, when a post answers your question. — Reza Aghaei, Mar 09 '19 at 16:29

score -1 · Answer 3 · edited Feb 13 '19 at 09:12

-1

try

string queryReg = "INSERT INTO uporabnik2(uporabnisko_ime, geslo, email) " +
                     "VALUES('" + RegistracijaUporabnisko + "', '" + RegistracijaGeslo + "', '" + RegistracijaMail + "')";

Generally, you only need to enclose value of string type data, however for safer side always enclose value into the single quote in insert statement

edited Feb 13 '19 at 09:12

Dale K

25,246
15
42
71

answered Feb 13 '19 at 09:07

Tarun Dudhatra

76
1
8

2

"however for safer side always enclose value into the single quote in insert statement" - oh dear god no; this is how you get hacked. The answer is **always** parameters. There are no interesting exceptions (in routine code, anyways). – Marc Gravell Feb 13 '19 at 09:13

System.Data.SqlClient.SqlException: Invalid column name (Error at line command.ExecuteNonQuery();)

3 Answers3