CODEWITHSUNDEEP
c#sqlsql-serverconnection
rammstein
rammstein

Reputation: 57

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

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);
            }

    }

Upvotes: 2

Views: 917

Answers (3)

BugFinder
BugFinder

Reputation: 17868

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.

Upvotes: 3

Tarun Dudhatra
Tarun Dudhatra

Reputation: 76

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

Upvotes: -1

Marc Gravell
Marc Gravell

Reputation: 1064184

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.

Upvotes: 3

Related Questions