Date "value" not working on INSERT Query

Hi,

The following INSERT query works in all aspects apart from the date value:

String InsertCmd =string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);

The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900").

In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT.

What am I doing wrong? Thanks very much.

Regards

Gary

That is because C# replace {1} with txtPaymentDate.Text, so the insert statement is changed to :

INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES (...,13/04/2006,...,...)

Where '...' stand for other texts. If you perform this command in Query Analyzer, you'll find 1900-01-01 is inserted instead of you expected value, as SQL needs a char value for datetime data type, not just the formated date without quotes. What we need to do is embed the date in a pair of quotes, so try to write your insertCmd as:

String InsertCmd =string.Format("SET DATEFORMAT dmy;INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},'{1}',{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);

That works in my testing.

|||Google "parameterized sql query".|||

Thanks, Guys - you confirmed that I was on the right track.

'Found I had a combination of issues. First the issue the Iory Jay pointed out - and I had tried (putting the single quotes around the {1} parameter). Second a MM/dd/yyyy versus dd/MM/yyyy format problem.

I'm in New Zealand, which uses dd/MM/yyy format - and it's seems incredibly hard to solve these sorts of problems without resorting to brute-force programming - which I've had to do.

I guess, someday, I'll find the elegant way to convert from one format to the other - and in the mean time I'll continue to shake my head in wonder that software isn't reverse-engineered to make learning to use it easy - at least for straightforward things like referencing a control on a formview (FormView1.FindControl("controlname") is hardly quick and easy is it?), managing default values in DropDownLists (What a mission!), creating a link to another page (Response.Redirect? Come on!) and inserting a date into a database!

Regards

Gary