@SQLParam Null? 1

[jgoodman00]

I am executing some SQL to insert a record into my underlying DB.

I have a number of parameters, one of which is a date field. This may or may not be null. If it is null, I want null inserted, rather than the 01/01/1901 default.

I am therefore using the following, which I am sure worked a few days ago:

cmdSQL.Parameters.Add("@DateRequired",strDateRequired).Value = System.DBNull.Value

I tested this a few days ago & was sure it worked, however it is now inserted null no matter what is entered.

Am I going crazy?
Any suggestions?

Cheers,




James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[DaZZleD]

have you by any chance modified the table design so that it doesn't allow null values?

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[jgoodman00]

No, it still allows null to be inserted.
If I remove

.Value = System.DBNull.Value[code] it will allow me to insert a date, but will fail on null.

Is my code (theoretically) correct? Should it work?

James Goodman MCSE, MCDBA
[URL unfurl="true"]http://www.angelfire.com/sports/f1pictures[/URL]

 

[DaZZleD]

to be more certain that the code works try this

SqlParameter param_dateRequired = new SqlParameter("@dateRequired", SqlDbType.DateTime);
param_dateRequired.Value = DBNull.Value;
cmdSQL.Parameters.Add(param_dateRequired);

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[jgoodman00]

I now have it working by cobbling the two together:

Dim SQL_P as SQLParameter
SQL_P = New SQLParameter("@DateRequired",SqlDbType.DateTime)
If strDateRequired <> "" Then
SQL_P.Value = CDate(strDateRequired)
Else
SQL_P.Value = DBNull.Value
End If

This now appears to be working but looks a little ungainly...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[DaZZleD]

dunno about vb but in C# you can do this:

SQL_P.Value = strDateRequired <> "" ? CDate(strDateRequired) : DBNull.Value

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[mp89]

Am I missing something here...if you have a datetime field that allows nulls and insert a record into it without passing a value to the datetime field it will be set to null anyway. Why would you want to set a default value for a field that in some circumstances needs to be set to null, surely that's just going to complicate things unnecessarily?

 

[DaZZleD]

yeah but what if the query string is fixed and you don't want to bother creating it differently for each param that could be null

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[jgoodman00]

Am I missing something here...if you have a datetime field that allows nulls and insert a record into it without passing a value to the datetime field it will be set to null anyway. Why would you want to set a default value for a field that in some circumstances needs to be set to null, surely that's just going to complicate things unnecessarily?

My SQL is in the form of

INSERT INTO Table(Field1,Field2) VALUES(@Param1,@Param2)

If I do not provide a value for a param which inserts into a datetime column it seems to insert 01/01/1901...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[jgoodman00]

I have ended up writing a small wrapper function:

Function CreateSQLDateParam(ByRef Name As String, ByRef Val As String) As SqlParameter
Dim SQL_P As SqlParameter
SQL_P = New SqlParameter(Name, SqlDbType.DateTime)
If Val <> "" Then
SQL_P.Value = CDate(Val)
Else
SQL_P.Value = DBNull.Value
End If
Return SQL_P
End Function

James Goodman MCSE, MCDBA
[URL unfurl="true"]http://www.angelfire.com/sports/f1pictures[/URL]

 

[jgoodman00]

I have ended up writing a small wrapper function:

Function CreateSQLDateParam(ByRef Name As String, ByRef Val As String) As SqlParameter
Dim SQL_P As SqlParameter
SQL_P = New SqlParameter(Name, SqlDbType.DateTime)
If Val <> "" Then
SQL_P.Value = CDate(Val)
Else
SQL_P.Value = DBNull.Value
End If
Return SQL_P
End Function

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures