Converting a C# DateTime to an SQL DateTime in input into the database

Question

I am trying to input these dates into a database from an excel spreadsheet. I have been able to get C# to read the date from the spreadsheet but now the SQL command won't allow me to insert these lines into the database. I need some help getting this formatted correctly for the database to except it.

In debug mode, this is the date: 'UpdateDate '3/28/2013 12:00:00 AM' and this is what it looks like in the excel sheet: 3/28/2013 2:04:49 PM. Below is my code:

private static bool SentenceMeasures_Update(DataRow dr)
{
   bool inserted = false;
   DateTime dt;
   Database pbkDB = DatabaseFactory.CreateDatabase("PbKConnectionString");

   try
   {
      ChargeCode = dr["ChargeCode"].ToString().Trim();
      MeasureCode = dr["MeasureCode"].ToString().Trim();
      UpdateUserId = String.IsNullOrEmpty(dr["UpdateUserId"].ToString().Trim()) ? "KSCONV" : dr["UpdateUserId"].ToString().Trim();
      UpdateDate = DateTime.TryParse(dr["UpdateDate"].ToString(), out dt) ? dt : DateTime.Now;
      DbCommand dbCommand = pbkDB.GetSqlStringCommand(string.Format(@"Update tblCtStateChargeSentenceMeasures set  MeasureCode = '{1}', UpdateUserId = '{2}', UpdateDate '{3}' where ChargeCode = '{0}')", ChargeCode, MeasureCode, UpdateUserId, UpdateDate));

      pbkDB.ExecuteNonQuery(dbCommand);
      inserted = true;
   }

   catch (Exception ex)
   {
      Console.WriteLine(ex.ToString());
   }
   return inserted;
}

What is your db? What type of date field is in the db? Is it Date, timestamp, etc.? — NoChance
– NoChance, Commented Oct 25, 2013 at 20:37
I was getting an SQL error. The first answer solved my issue. Thanks everyone. — Katherine
– Katherine, Commented Oct 25, 2013 at 20:43
@Katherine I commented on the answer below is well, go ahead and check it off as solving your issue. Welcome to Stack Overflow! ;) — ahsteele
– ahsteele, Commented Oct 25, 2013 at 20:49

ahsteele · Accepted Answer · 2013-10-25 20:47:22Z

A parametrized query is clearer, faster and safer. Clearer because the code is easier to read; faster because SQL will reuse the query execution plan; and safer because it will protect against SQL injection. Below is your code refactored as a parameterized query:

DbCommand dbCommand = pbkDB.GetSqlStringCommand(
    @"Update tblCtStateChargeSentenceMeasures set (MeasureCode = @MeasureCode 
    , UpdateUserId = @UpdateUserId
    , UpdateDate = @UpdateDate)
    where ChargeCode = @ChargeCode");
dcCommand.Parameter.Add("ChargeCode",ChargeCode);
dcCommand.Parameter.Add("MeasureCode",MeasureCode);
dcCommand.Parameter.Add("UpdateUserId",UpdateUserId);
dcCommand.Parameter.Add("UpdateDate",UpdateDate);

pbkDB.ExecuteNonQuery(dbCommand);

CDspace · Accepted Answer · 2013-10-25 20:37:26Z

1

Shouldn't you simply need an equals when setting UpdateDate? I.e.

... UpdateDate = '{3}' where ChargeCode = '{0}')", ...
               ^

You might also need to format the DateTime object to fit with what SQL expects. See here and here

answered Oct 25, 2013 at 20:37

CDspace

2,69719 gold badges32 silver badges39 bronze badges

2 Comments

CDspace Over a year ago

@Katherine Not a problem

ahsteele Over a year ago

@Katherine if this answered your question you should use the check mark on the left of the question to indicate so. :)

Collectives™ on Stack Overflow

Converting a C# DateTime to an SQL DateTime in input into the database

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related