Inserting Date Value

Question

Hi all i am trying to insert a string of data into an sql database table from a number of textboxes, however the date value 17/07/2011 in my textbox4 is not being inserted and haulting the whole processes.

Below is the code i am using

string insertString = "insert into Herdregistar(month,uic,motheruic,mother,name,animalsex,tagno,status,owner,presence,year,wkno,day,date) values ('" + textBox6.Text + "','" + textBox11.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + textBox12.Text + "','" + comboBox3.Text + "','" + textBox13.Text + "','" + comboBox1.Text + "','" + textBox15.Text + "','" + comboBox4.Text + "','" + textBox7.Text + "','" + textBox5.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')"SqlCommand
cmd.ExecuteNonQuery();
cmd = new SqlCommand(insertString, conn);in my sql database i have set the value of the date column as "Date"

your kind advice will be highly appreciated.
Best regards

Vulpes · Answer

For SQL Server you may need to switch the order of the day and month so that the month comes first. Try the following: string insertString = insert into Herdregistar(monthuicmotheruicmothernameanimalsextagnostatusownerpresenceyearwknodaydate) values (' + textBox6.Text + '' + textBox11.Text + '' + textBox8.Text + '' + textBox9.Text + '' + textBox12.Text + '' + comboBox3.Text + '' + textBox13.Text + '' + comboBox1.Text + '' + textBox15.Text + '' + comboBox4.Text + '' + textBox7.Text + '' + textBox5.Text + '' + textBox3.Text + '' + DateTime.Parse(textBox4.Text).ToString(MM/dd/yyyy) + ');

Mike Jonson · Answer

strSql = Insert InTo MoneyEntries (Amount EntryDate Description IDCategory) Values (' + am.ToString() + '' + {d' + dt.ToString() + '}' + '' + desc.ToString() + '' + rdr[0].ToString() +')'; Show error Incorrect syntax near '18.07'.. Cant undestand why?

Marvin kakuru · Answer

Thanks Vulpes changing the format was the solution to my small problem now i can save the date values. Thanks again men.

Zoran Horvat · Answer

P.S. You can ensure that textBox4 contains correctly formatted date by using DateTimePicker control instead of it. In that case you would have to set its Format and CustomFormat properties to appropriate values like this: dateTimePicker1.Format = DateTimePickerFormat.Custom; dateTimePicker1.CustomFormat = yyyy-MM-dd; In that case your code would look like this: string insertString = insert into Herdregistar(monthuicmotheruicmothernameanimalsextagnostatusownerpresenceyearwknodaydate) values (' + textBox6.Text + '' + textBox11.Text + '' + textBox8.Text + '' + textBox9.Text + '' + textBox12.Text + '' + comboBox3.Text + '' + textBox13.Text + '' + comboBox1.Text + '' + textBox15.Text + '' + comboBox4.Text + '' + textBox7.Text + '' +textBox5.Text + '' + textBox3.Text + '' + {d ' +dateTimePicker1.Text + '}) Zoran

Zoran Horvat · Answer

Marvin Sutish is right - you should not write hard-coded queries but rather make them with bind variables. That saves you from conversion problems and from SQL injection attacks. Now if you want your query to be formatted as a hard-coded query i.e. to have constant date value then you should better use date literal format which is accepted by MSSQL: {d 'yyyy-mm-dd'}. For example: CREATE TABLE SimpleTable(DateColumn DATE) INSERT INTO SimpleTable(DateColumn) VALUES({d '2011-07-16'}) This should be guaranteed to work fine on MSSQL. Now I suppose that textBox4 contains full date in your case. Then make sure that date is contained in format yyyy-mm-dd and then your code should be modified like this: string insertString = insert into Herdregistar(monthuicmotheruicmothernameanimalsextagnostatusownerpresenceyearwknodaydate) values (' + textBox6.Text + '' + textBox11.Text + '' + textBox8.Text + '' + textBox9.Text + '' + textBox12.Text + '' + comboBox3.Text + '' + textBox13.Text + '' + comboBox1.Text + '' + textBox15.Text + '' + comboBox4.Text + '' + textBox7.Text + '' +textBox5.Text + '' + textBox3.Text + '' + {d ' + textBox4.Text + '}) And again - this is very bad method of dealing with queries and you should learn to use bind variables as soon as you can. Zoran

Suthish Nair · Answer

These type of errors can be avoided by using Parametrized Queries..

John Penn · Answer

To remove any ambiguity use a format that uses a month abbreviation (dd-MMM-yyyy as in 17-Jul-2011).