Getting error message when trying to execute code

Question

con.Open();
string qry="insert into reg1 values ('"+txtname.Text+"','"+txtaddress.Text+"','"+txtpin.Text+"','"+txtage.Text+"','"+txtgender.Text+"','"+txtcourse.Text+"','"+txtcollege.Text+"','"+txtfname.Text+"','"+txtoccup.Text+"','"+txtmname.Text+"','"+txtskills.Text+"','"+txtmobile.Text+"','"+txtemail.Text+"')";
SqlCommand cmd = new SqlCommand(qry, con);
cmd.ExecuteNonQuery();
con.Close();

I am receiving the following error message when I try to execute this code:

Column name or number of supplied values does not match table definition.

Can anyone help me find the error?

Column name or number of supplied values does not match table definition. — user3239745, Feb 04 '14 at 07:26
please take `(column name)` as well ... or check the number of values you have taken and number of fields in db — The Hungry Dictator, Feb 04 '14 at 07:30
cid int Unchecked name varchar(20) Checked address varchar(20) Checked pin varchar(20) Checked age varchar(20) Checked gender varchar(20) Checked course varchar(20) Checked college varchar(20) Checked fname varchar(20) Checked occupation varchar(20) Checked mname varchar(20) Checked skills varchar(20) Checked mobile varchar(20) Checked email varchar(20) Checked — user3239745, Feb 04 '14 at 07:33
you need to supply value for `cid` field or make it `identity` (auto-incrementing) — Igor, Feb 04 '14 at 07:38
@KillerR Thanks all,i got the error rectified by a friend.for varchar(20).I had given more than 20. — user3239745, Feb 04 '14 at 10:11
In the current form, the question would be unlikely to be useful to "future generations". Voting to close. — DarkWanderer, Feb 04 '14 at 13:24

score 5 · Answer 1 · edited May 23 '17 at 11:56

Sounds like your column numbers in your table and your SqlCommand does not match. But since we didn't know anything about your table design, we never know..

If your INSERT command and your table doesn't have the same column number, you have to declare your column names which you want to insert these values..

I count 14 columns on your table from your comment, but you try to add 13 values. These are doesn't match.

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

con.Open();
string qry = @"insert into reg1
               values(@name, @address, @pin, @age, @gender, @course, @college, @fname
               @occup, @mname, @skills, @mobile, @email)";
SqlCommand cmd = new SqlCommand(qry, con);
cmd.Parameters.AddWithValue("@name", txtname.Text);
cmd.Parameters.AddWithValue("@address", txtaddress.Text);,
.....
cmd.ExecuteNonQuery();
con.Close();

score 0 · Answer 2 · edited May 23 '17 at 11:56

you entered to much or to few values into the table in other words your table has 5 columns and you entered 6 values or 4 check that the values you entered corresponds with your table

best advice i can give is always use a procedure it is recommended to protect against sql injection and it will reduce the chance of this happening ,it is also a lot more efficient than hard coding it from c# end

create a proc and just call it from c# end done ;)

SQL Server stored procedure beginner's guide

     create proc example_insert
     @values varchar(100) <--- declare  parameters

     as 

     insert into [Table]([Column],[Column],[Column])
     values('','','',)<--- -this should be the same number as your columns

calling the stored procedure this is in vb but the principle is the same

        s1.Open() 'opens the connection
        Dim cmd As New SqlCommand("example_insert", s1)

        cmd.CommandType = CommandType.StoredProcedure


        cmd.Parameters.AddWithValue("@Value", textbox.Text)<--- a lot simpler

if further help is required script your table to a query and post it — Wolf, Feb 04 '14 at 07:31

Getting error message when trying to execute code

2 Answers2