ExecuteNon query error at date time

suhel jamadar

5y874

SqlCommand cmd = new SqlCommand("INSERT INTO Monitor VALUES(@LCDSrNo, @ProductName, @LCDProductNo, @Date)", con);

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@LCDSrNo", textBox2.Text);

cmd.Parameters.AddWithValue("@ProductName", textBox3.Text);

cmd.Parameters.AddWithValue("@LCDProductNo", textBox4.Text);

cmd.Parameters.AddWithValue("@Date", dateTimePicker1);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
'Column name or number of supplied values does not match table definition.'

it means in database table there are more or less colun than you are trying to insert thorugh Store procedure.
check your Id column is identity (auto incremented)
Kiran Mohanty5y ago
Specify column values you wish to insert in the query as below. And then modify command parameter value. That should solve your problem.
cmd.Parameters.AddWithValue("@Date", dateTimePicker1.Value.Date);
"INSERT INTO Monitor(LCDSrNo, ProductName, LCDProductNo, Date) VALUES(@LCDSrNo, @ProductName, @LCDProductNo, @Date)
Mageshwaran R5y ago
Hi suhel,
Id is not identity column so you pass the id value.

SqlCommand cmd = new SqlCommand("INSERT INTO Monitor(Id,LCDSrNo,ProductName,LCDProductNo,Date)

SELECT Id=@Id,LCDSrNo=@LCDSrNo,ProductName=@ProductName,LCDProductNo=@LCDProductNo,Date=@Date", con);

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@Id", 1);

cmd.Parameters.AddWithValue("@LCDSrNo", textBox2.Text);

cmd.Parameters.AddWithValue("@ProductName", textBox3.Text);

cmd.Parameters.AddWithValue("@LCDProductNo", textBox4.Text);

cmd.Parameters.AddWithValue("@Date", dateTimePicker1);

con.Open();

cmd.ExecuteNonQuery();

con.Close();
Sachin Singh5y ago
@suhel
still your id is not auto incremented
1. right click on Id column (key symbol) and go to properties
2. under properties double click on Identity specification
suhel jamadar5y ago
CHECK THIS
Sachin Singh5y ago
CREATE TABLE Monitor(
ID int IDENTITY(1, 1) PRIMARY KEY,
Name varchar(50)

)
Mageshwaran R5y ago
Hey Suhel,
please submit your table script
Mageshwaran R5y ago
Try this,
SqlCommand cmd = new SqlCommand("INSERT INTO Monitor(LCDSrNo,ProductName,LCDProductNo,Date)
SELECT LCDSrNo=@LCDSrNo,ProductName=@ProductName,LCDProductNo=@LCDProductNo,Date=@Date", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@LCDSrNo", textBox2.Text);
cmd.Parameters.AddWithValue("@ProductName", textBox3.Text);
cmd.Parameters.AddWithValue("@LCDProductNo", textBox4.Text);
cmd.Parameters.AddWithValue("@Date", dateTimePicker1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
suhel jamadar5y ago
'Column name or number of supplied values does not match table definition.'
Mageshwaran R5y ago
1. SqlCommand cmd = new SqlCommand("INSERT INTO Monitor VALUES(@LCDSrNo, @ProductName, @LCDProductNo, @Date)", con);
2. cmd.CommandType = CommandType.Text;
3. cmd.Parameters.AddWithValue("@LCDSrNo", textBox2.Text);
4. cmd.Parameters.AddWithValue("@ProductName", textBox3.Text);
5. cmd.Parameters.AddWithValue("@LCDProductNo", textBox4.Text);
6. cmd.Parameters.AddWithValue("@Date", dateTimePicker1.Value.Date);
7. con.Open();
8. cmd.ExecuteNonQuery();
9. con.Close();
Hi Suhel,
please try this code,

Sachin Singh5y ago

the problem is with the datetimePicker date format and Sql server date format please use like below

var date=dateTimePicker1.Value.ToString("M/d/yyyy");
//or
var date=dateTimePicker1.Value.ToString("MM/dd/yyyy");
//or var date=dateTimePicker1.Value.Date;
cmd.Parameters.AddwithValue('@Date',date);