Date Fields don't go into SQLExpress dB but No Error. Pulling Hair out. Help!

[Lynn]

I wrote the plea below several days ago and got some good help. Thanks
to Robin, who responded and helped me solve a real hair-puller for me.

Now I have a different question: the changes are appearing in my dB,
and all are ok except my dates don't go in. I have tthree date fields,
and all of them are defined as "not null". When I view the table, the
value for all added records in the date fields is 1/1/1900. That
surely is the default value. I am, indeed, sending valid dates, at
least they loook valid to me, adn all values in other fields are being
added without a problem. For the dates, though they are not being
added, I receive no error. What is it with SQL Express that misbehaves
but never reports an error? Snippet here. Any help appreciated, and
thanks again to all.

strInsertSQL = "Insert into TCSTPSUMM00
(EMPL_NUM,PYRL_NO,PERIOD_DATE,STEP_DATE,POINTS_QTY,PROCESS_DATE)
values (11111, 25,1/1/2005, 11/19/2002, 4,2/2/2005)";


SqlTransaction objTrans = null;
SqlCommand objCmd = null;

try
{
if (objConnection.State ==
System.Data.ConnectionState.Open)
{
objCmd = objConnection.CreateCommand();
objTrans =
objConnection.BeginTransaction("InsertRows");
objCmd.Connection = objConnection;
objCmd.Transaction = objTrans;
objCmd.CommandText = strInsertSQL;
objCmd.ExecuteNonQuery();
objTrans.Commit();
return true;
}
}
Thanks again to all.

===================================
THANKS FOR HELP ON THIS ONE. - DONE!

HELP! And thanks for any thoughts in advance. I am writing in C#,
using a SQLExpress database. I am able to retrieve data without a
problem, but I cannot write to the DB. I don't get any error - but the
rows don't appear in the database! It is driving me nuts. There is no
error, no nothing!

Snippet below. Any thoughts at all appreciated.

private static string strConnect =
ConfigurationManager.ConnectionStrings["nmMyNamespace.Properties.Settin
gs.MyConnection_ConnectionString"].ConnectionString;

internal static System.Data.SqlClient.SqlConnection
objConnection = new System.Data.SqlClient.SqlConnection(strConnect);

objConnection.Open();

try
{
if (objConnection.State ==
System.Data.ConnectionState.Open)
{
objCmd = objConnection.CreateCommand();
objTrans =
objConnection.BeginTransaction("InsertRows");
objCmd.Connection = objConnection;
objCmd.Transaction = objTrans;
objCmd.CommandText = strInsertSQL;
objCmd.ExecuteNonQuery();
objTrans.Commit();
return true;
}
}

 

[Marc Gravell]

Literals...

For 1/1/2005 The server will be calculating this as integer maths. For
pretty much any date, after dividing by the year the answer will be 0;
as it happens, "datetime" in sql-server casts to/from int / float, and
the "zero" datetime is 1 Jan 1900. For reference, the numeric
representation of dates in sqlserver is the (fractional) number of
days since 1 Jan 1900, so 1.5 will be midday on 2 Jan 1900.

You need to either use quotes '1/1/2005', else (better) switch to
typed parameters.

Marc

 

[Marc Gravell]

(or, of course, calculate the numeric offset from 1 Jan 1900... I've
never used this approach, but theoretically it might be quicker than
having the server parse the string literals, plus you don't have to
worry about your regional settings)

Final message for the day: avoid string concatenation for building SQL
queries. This is an easy way to accidentally slip SQL-injection
vulnerabilites into the code. Parameterised queries avoid this
completely, as the data is not part of the query.

If you *must* (for whatever reason) use concatenation, then in
particular *never* trust user data. For dates etc, don't accept and
concatenate literals from a user. Always load into a DateTime (via
..Parse) and then .ToString() it back out. Otherwise after inserting a
few combinations of quotes, "GO", "--", etc the user can do fun things
like reading / writing arbitrary tables.
Marc