How to fix "System.Data.SqlClient.SqlException: 'Column name or number of supplied values does not match table definition.'" in asp.net

Question

I'm trying to connect my SQL Server with ASP.NET, and when I run my insert function, it displays an error.

SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Insert into Table1 values('"+firstname.Text+"','"+lastname.Text+"','"+city.Text+"')";
cmd.ExecuteNonQuery();

firstname.Text = "";
lastname.Text = "";
city.Text = "";

I expect to show the inserted values but it displays this error:

System.Data.SqlClient.SqlException: 'Column name or number of supplied values does not match table definition.'

Where Id is auto incremented.

If, in your INSERT statement, you don't specify the columns names that receives your values then you are required to provide values for all columns. Please provide the names of columns in your table _Table1_ — Steve, May 18 '19 at 09:14
Also after you have resolved this little problem you have a bigger one. Do not concatenate strings together to form an SQL Statement. This is a well know source of bugs in parsing the resulting text but it could be used to hack your database. Search about Sql Injection — Steve, May 18 '19 at 09:16
Agree with @Steve. You need to create a stored procedure and call it from the ADO.net code. Also, first run the SP in the database, check if it works as expected. Then come to ADO.net & test. — Prateek Kumar Dalbehera, May 18 '19 at 09:20
https://stackoverflow.com/questions/11292940/use-sql-server-stored-procedure-output-in-asp-net-c-sharp — Prateek Kumar Dalbehera, May 18 '19 at 09:22
Rather than pointing OP to use Sprocs, which is another layer of SQL learning, you could point to the use of Parameters, which (a) solves the injection problem and (b) keeps OP on the Ado.Net learning curve — Chris F Carroll, May 18 '19 at 11:00

score 2 · Accepted Answer · answered May 18 '19 at 10:57

You need urgently research about SQL injection, and STOP USING string concatenation for building your SQL insert statement RIGHT NOW.

You need to use the proper technique - parametrized queries -- always - NO exceptions!

And also, it's a commonly accepted Best Practice to list the columns in your INSERT statement, to avoid trouble when tables change their columns (read more about this here: Bad habits to kick: using SELECT * / omit the column list ).

Use this code as a sample/template:

string insertQuery = @"INSERT INTO dbo.Table1 (FirstName, LastName, City)
                       VALUES (@FirstName, @LastName, @City);";

using (SqlCommand cmd = new SqlCommmand(insertQuery, con))
{
    cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = firstname.Text;
    cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = lastname.Text;
    cmd.Parameters.Add("@City", SqlDbType.VarChar, 50).Value = city.Text;

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close()
}

score 0 · Answer 2 · edited May 18 '19 at 10:53

You should specify the columns names. For example:

cmd.CommandText = $"Insert into Table1 ({ColumnName of firstname}, { ColumnName of lastname}, { ColumnName of city}) 
                    values({firstname.Text}, {lastname.Text}, {city.Text})";

You can better use a stored procedure - something like that:

cmd.CommandText = "your SP name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = firstName.Text;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lastName.Text;
etc...

How to fix "System.Data.SqlClient.SqlException: 'Column name or number of supplied values does not match table definition.'" in asp.net

2 Answers2