By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,567 Members | 1,742 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,567 IT Pros & Developers. It's quick & easy.

Parameterized Query, UPDATE statement, and Nulls

P: n/a
I am writing a parametrized query, where Im building an UPDATE
statement through concatenation, and then adding the parameters such
as:

UPDATE myTable SET [columna] = @newcolumna, [columnb] = @newcolumnb
WHERE [columna] = @origcolumna
AND [columnb] = @origcolumnb

This works fine, unless one of the original database values are a
NULL. Im creating the parameters as:

SqlParameter sqlOrgParam = new SqlParameter();
sqlOrgParam.SqlDbType = this.GetColumnType(tableName,
rowUpdate.ColumnName); // my own method for type
sqlOrgParam.ParameterName = "@org_" + rowUpdate.ColumnName;
if (rowUpdate.OriginalValue == "") // By now, an empty
string would represent a null
sqlOrgParam.Value = DBNull.Value; //This line executes as it
should
else
sqlOrgParam.Value = rowUpdate.OriginalValue;
sqlCmd.Parameters.Add(sqlOrgParam);

The query is called without failure, its just that it doesn't find the
null in the original record, and as such, doesn't perform any
updates. If I remove the null in the table, everything works fine.
It seems like parameter substitution doesnt like DBNull.Value for some
reason. Any thoughts?
Jul 6 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
This is an SQL issue not a .Net issue.

A column with a null value does NOT = a parameter with a null value -
it 'Is Null'.

So - your SQL needs to be something like

WHERE (columna is null and @origValue is null)
OR )columna = @origValue
...

On Jul 7, 9:19*am, xlar54 <scott.hut...@gmail.comwrote:
I am writing a parametrized query, where Im building an UPDATE
statement through concatenation, and then adding the parameters such
as:

UPDATE myTable SET [columna] = @newcolumna, [columnb] = @newcolumnb
WHERE [columna] = @origcolumna
AND [columnb] = @origcolumnb

This works fine, unless one of the original database values are a
NULL. Im creating the parameters as:

SqlParameter sqlOrgParam = new SqlParameter();
sqlOrgParam.SqlDbType = this.GetColumnType(tableName,
rowUpdate.ColumnName); *// my own method for type
sqlOrgParam.ParameterName = "@org_" + rowUpdate.ColumnName;
if (rowUpdate.OriginalValue == "") * * * * * * *// By now, an empty
string would represent a null
* * * *sqlOrgParam.Value = DBNull.Value; * //This line executes as it
should
else
* * * *sqlOrgParam.Value = rowUpdate.OriginalValue;
sqlCmd.Parameters.Add(sqlOrgParam);

The query is called without failure, its just that it doesn't find the
null in the original record, and as such, doesn't perform any
updates. *If I remove the null in the table, everything works fine.
It seems like parameter substitution doesnt like DBNull.Value for some
reason. *Any thoughts?
Jul 7 '08 #2

P: n/a
On Jul 6, 7:00*pm, ".\\\\axxx" <mailma...@gmail.comwrote:
This is an SQL issue not a .Net issue.

A column with a null value does NOT = a parameter with a null value -
it 'Is Null'.

So - your SQL needs to be something like

WHERE (columna is null and @origValue is null)
OR )columna = @origValue
...
Perhaps you are not understanding the issue. I can not say "is null"
in a .NET C# parametrized query.

Thanks
Jul 7 '08 #3

P: n/a
I can not say "is null" in a .NET C# parametrized query.
Why not? The query is just TSQL; you can say whatever you want...
under ANSI SQL nulls are never equal, so you have to do something...

the "([foo] IS NULL AND [bar] IS NULL) OR ([foo] = [bar])" is quite a
common bit of SQL (replace [foo] and [bar]...) - alternatively you can
build the SQL based on the values (via a StringBuilder etc) and append
only the correct construction. Or sometimes you can get creative with
ISNULL/NULLIF/COALESCE - but this isn't always a good idea ;-p

Of course, another option would be to use a timestamp for concurrency
checking, or any of the ORM tools - LINQ-to-SQL will do either
timestamp or column-based concurrency checking.

Marc
Jul 7 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.