The major problem is that you are concatenating strings to form your SQL command. Don't.
Firstly, because it leave you wide open to SQL Injection attacks which can damage or destroy your database.
Secondly because it leaves you wide open to problems like this.
When you concatenate strings, it builds a string value to pass to SQL. So, to cut down your code a little:
Query = "Insert Into TableBill (TableNo,BillDate,ItemName) Values (txttableno.Text & "," & DateTimePicker1.value& ",
This does a bunch of ToString calls behind the scenes and you end up with the equivalent of:
Query = "Insert Into TableBill (TableNo,BillDate,ItemName) Values (1234,1/12/2013,'The Item I Want')"
Which is passed to SQL for parsing. It looks at it, takes an integer value 1, divides it by 12 to give 0, which it then divides by 2013 to still give 0. It then converts the zero to a date and comes up with the wrong value. The same thing happens if your system is set to US: 12/1/2013 is still zero, and so forth.
Use parameterized queries. Not only do such problems not occur, but your database is safe from me typing commands to destroy it into your text boxes...
Submit an article or tip