Parameterized query and NULL DateTime value

Question

I'm working a program that is pulling a string field from an access database, separating out a name (first and last) from a date then save the name separately from the date in a different access database.

I've got everything done except some of the date values are null so I need to parametrize the SQL but I haven't been able to figure out how to do make the parametrization work.

I've put in dummy values for the variable and it adds them to the table just fine. I've cut out the other variables in the code snippet below since they're all repeats of what's there. os is a list holding data from a structure.

string sqlcmd = "INSERT INTO signatures VALUES ('" + os.QASignature + "', 'QADate = @QADATE'";
System.Data.OleDb.OleDbCommand SQLCommand = new System.Data.OleDb.OleDbCommand(sqlcmd, Connection);
using (SQLCommand)
{
    SQLCommand.Parameters.Add("@QADATE", System.Data.OleDb.OleDbType.Date).Value = os.QADate;
    SQLDataReader = SQLCommand.ExecuteReader();
}

dash · Accepted Answer · 2012-07-17 22:47:44Z

6

Something like the following should be what you want:

string sqlcmd = "INSERT INTO signatures (QASignature, QADate) VALUES (?, ?)";
using (System.Data.OleDb.OleDbCommand SQLCommand = new System.Data.OleDb.OleDbCommand(sqlcmd, Connection))
{
    SQLCommand.Parameters.Add(new OleDbParameter() { Name = "QASignature", Value = os.QASignature, DbType = DbType.String});
    SQLCommand.Parameters.Add(new OleDbParameter() { Name = "QADATE", Value = os.QADate, DbType = DbType.DateTime});
    SQLCommand.ExecuteNonQuery(); //Use ExecuteReader or ExecuteScalar when you want to return something
}

If os.QADate is nullable (DateTime? or System.Nullable<DateTime>), then you would do the following:

if(os.QADate == null) //Could easily be os.QADate == DateTime.MinValue too, for example
{
    SQLCommand.Parameters.Add(new OleDbParameter() { Name = "@QADATE", Value = DBNull.Value, DbType = DbType.DateTime});
}
else{
    SQLCommand.Parameters.Add(new OleDbParameter() { Name = "@QADATE", Value = os.QADate, DbType = DbType.DateTime});
}

Note that you shouldn't mix string concatenation and parameters like in your original example - it's one or the other! And really, it should be just parameterization to guard against SQL Injection, and to gain other benefits (like easier typing and, in some RDBMS, parameterized queries perform better).

Also note that OleDBCommand does not benefit from named parameters - parameters must be added to the query in the order they appear in the SQL. This is why the SQL Query contains two question marks - they are simply placeholders.

edited Jul 17, 2012 at 22:47

answered Jul 17, 2012 at 22:36

dash

91.7k4 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Donnachaidh Over a year ago

After I put that into my program I get the following errors: "The name 'Value' does nt exist in the current context" "'System.Data.DbType' is a 'type' but is used like a 'variable'".

Donnachaidh Over a year ago

Nevermind, It needed to be like "SQLCommand.Parameters.Add(new OleDbParameter(Name = "QASignature", DbType.String).Value = os.QASignature)" rather than what you had.

RobIII · Accepted Answer · 2012-07-17 22:42:16Z

4

You need to "convert" null to DBNull.Value. One way to achieve that, assuming QADate is a nullable DateTime (so DateTime?) might be:

... .Value = os.QADate ?? DBNull.Value;

?? being the null-coalescing operator.

Edit: You might actually need to cast to make sure both operands are of the same type:

... .Value = (object)os.QADate ?? DBNull.Value;

Also, why don't you use a parameter for QASignature?? Why are you "inlining" that value? I don't know what QASignature would contain, but this leaves you vulnerable to SQL Injection.

And finally, why are you using ExecuteReader() for an insert? Why not use ExecuteNonQuery()?

edited Jul 17, 2012 at 22:42

answered Jul 17, 2012 at 22:32

RobIII

8,8414 gold badges46 silver badges99 bronze badges

Comments

Steve · Accepted Answer · 2012-07-17 23:01:32Z

1

DateTime cannot be null, if the value is not initialized it is DateTime.MinValue. You need to test for this case and use the parameters also for the string values.

using System.Data.OleDb;
....

string sqlcmd = "INSERT INTO signatures VALUES (@QASignature, @QADATE)"; 
using(OleDbCommand SQLCommand = new OleDbCommand(sqlcmd, Connection))
{ 
    SQLCommand.Parameters.AddWithValue("@QASignature",  os.QASignature);
    SQLCommand.Parameters.Add("@QADATE", OleDbType.Date).Value = 
               (os.QADate == DateTime.MinValue 
               ? (object)DBNull.Value
               : (object)os.QADate); 
    SQLDataReader = SQLCommand.ExecuteNonQuery(); 
}

By the way, an insert statement usually is executed by ExecuteNonQuery

edited Jul 17, 2012 at 23:01

answered Jul 17, 2012 at 22:37

Steve

217k22 gold badges242 silver badges296 bronze badges

5 Comments

RobIII Over a year ago

DateTime cannot be null indeed, DateTime? however, can :-) I am assuming the latter since user1533116 states: 'some of the date values are null'.

dash Over a year ago

Sorry, @Steve, I accidentally edited your post. Have rolled back to original version. I removed the System.Data.OleDb.OleDbCommand section so didn't change the nature of it. I must be going blind.

Steve Over a year ago

@dash, OK, however your edit was right. Let me restore your changes :-)

dash Over a year ago

The only other comment I had was that you can't set the value that way because there is no conversion between DBNull.Value and a DateTime - you can't use the ternary [conditional] operator this way unfortunately. However, it's still a +1 in that you are the only person to mention DateTime.MinValue in context.

Steve Over a year ago

@dash, right I haven't noticed that. However adding a cast to object works. Definitively your answer is better.

Heinzi · Accepted Answer · 2012-07-17 22:33:26Z

0

Use DBNull.Value:

SQLCommand.Parameters.Add("@QADATE", 
                          System.Data.OleDb.OleDbType.Date).Value = DBNull.Value;

As a side note, using OleDbParameterCollection.AddWithValue automatically infers the data type and allows you to shorten your code:

SQLCommand.Parameters.AddWithValue("@QADATE", DBNull.Value);

answered Jul 17, 2012 at 22:33

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Comments

Dillie-O · Accepted Answer · 2012-07-17 22:35:30Z

0

Consider using the Nullable(of T) structure. Then you can set it only if there is actually a variable in your source object. Otherwise the value is set to Null and passes itself nicely into SQL Parameters. A lot of the old TableAdapter classes and newer EntityFramework objects work with the Nullable (of T) structure as well.

answered Jul 17, 2012 at 22:35

Dillie-O

29.9k14 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

Parameterized query and NULL DateTime value

5 Answers 5

2 Comments

Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related