CODEWITHSUNDEEP
c#visual-studio
Mike Hunt
Mike Hunt

Reputation: 37

C# Column name or number of supplied values does not match table definition error

I get this error when I try to add a new person to my listbox and into my database.

My code:

public void AddSpeler(string name, string club)
        {




        conn.Open();


            Speler speler = new Speler();
            speler.Name = name;
            speler.Club = club;


            string query = "INSERT INTO Speler OUTPUT Inserted.ID VALUES ('" + speler.Name + "', '" + speler.Club + "')";
            SqlCommand cmd = new SqlCommand(query, conn);


            speler.ID = (int)cmd.ExecuteScalar();

            conn.Close();
        }

I get the error on the part:

 "speler.ID = (int)cmd.ExecuteScalar();

And yes, I have my primary key (ID) set to increase by one.

Upvotes: 1

Views: 1099

Answers (2)

Steve
Steve

Reputation: 216293

As said in the comment above, you shouldn't write an sql command concatenating strings. This is well known to be a source of bugs and a big security risk called Sql Injection.

The second point to fix is the syntax used. In an INSERT INTO statement you should provide the column that matches the values inserted. If you don't supply the column names then you need to write the values for each column in the table and in the exact order in which the columns are defined

void AddSpeler(string name, string club)
{
    conn.Open();
    Speler speler = new Speler();
    speler.Name = name;
    speler.Club = club;
    string query = @"INSERT INTO Speler (Name, Club) OUTPUT Inserted.ID 
                     VALUES (@name, @club)";
    SqlCommand cmd = new SqlCommand(query, conn);
    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = speler.Name;
    cmd.Parameters.Add("@club", SqlDbType.NVarChar).Value = speler.Club;
    speler.ID = (int)cmd.ExecuteScalar();
    conn.Close();
}

Here I assume that your table Speler contains the columns named Name and Club, of course change them to your actual names.

EDIT
If you want to call this method from another class, you need to make it public so every callers that creates an instance of the class where the method is defined can use it.

By the way, the code in AddSpeler does some things that are wasted if you don't return them to your caller (void ??) Possibly you want to return the instance of the Speler class created in the code so change the method to 

public Speler AddSpeler(string name, string club)
{
    try
    {
       conn.Open();
       Speler speler = new Speler();
       .....

       ....
       return speler;
    }
    catch(Exception ex)
    {
        // display the ex.Message to know why your code fails
        MessageBox.Show(ex.Message);
        conn.Close();
        return null; // no Speler returned if code fails
    }
}

Upvotes: 2

Lucero
Lucero

Reputation: 60190

The problem is that you want to skip the ID column in the values provided, so you need to specify that.

    string query = "INSERT Speler (Name, Club) OUTPUT Inserted.ID VALUES ('" + speler.Name + "', '" + speler.Club + "')";

While this will solve the error, you should absolutely not concatenate SQL strings! Use parameters instead.

Upvotes: 0

Related Questions