sqlParameter and NULL

[pgarn]

Hello folks,

I've got problems using a query like 'SELECT idx FROM foo WHERE
val=@val' and using SqlParameter. When I assign a non-null value to
this query and execute the command everything works fine. When trying
to perform the same query with sqlParameter.value = DBNull.Value
nothing happens.
It is very similiar like the query 'SELECT idx FROM foo WHERE
val=null'. When I use this query in server management studio (I use
framework 3.5 and sql server 2005). The native query cann be corrected
to '...WHERE val IS NULL', but when I use this with the SqlCommand an
exception occurs (something with wrong syntax near @val).

Is there another solution for querying NULL values via SqlParameter
than using the query without parameter?

Pascal

 

[William \(Bill\) Vaughn]

First, let's assume you're building a Command object with a Parameters collection and passing in the parameter value via the Parameter Value property. Trying to concatenate parameters into a query is a bad idea--it opens your application for SQL injection attacks. I also recommend using stored procedures to execute queries for lots of reasons--parameter management is one of them.
Given this assumption, to pass a NULL value to a parameter you have several choices:
a.. If you're using a SP (as I recommend), set NULL as the default value in the stored procedure for the selected parameter(s). In this case, if you don't append the named Parameter to the Parameters collection the NULL default value is used.
b.. Pass DBNULL.Value as the parameter value when you want to pass a NULL parameter value.
c.. Pass Nothing (in VB.NET) or null (in C#) to the value when you want to pass a NULL.
I would also enable the SQL Profiler to see what's getting executed when you have syntax issues with the commands you (or ADO) generate.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[pgarn]

Hello Willliam,

your assumption was right. I have something like the following:

SqlCommand cmd = new SqlCommand("SELECT idx FROM foo WHERE
val=@val", conn);
SqlParameter para = new SqlParameter();
para.ParameterName = "@val";
para.SqlDbType = System.Data.SqlDbType.Int;
para.Value = System.DBNull.Value; //1;
cmd.Parameters.Add(para);

SqlDataReader dr = cmd.ExecuteReader();

When I use the profiler is says:
exec sp_executesql N'SELECT * FROM foo WHERE val=@val',N'@val
int',@val=NULL
or (in case a numeric value)
exec sp_executesql N'SELECT * FROM foo WHERE val=@val',N'@val
int',@val=1

The results (no rows even if there are rows with val=null) are similar
to the queries when replacing the sqlparameter and using "query
analyzer". I have to replace the "WHERE val=null" with "WHERE val IS
null". That works fine in T-SQL but when trying this in the code, he
mentioned an "incorrect syntax near @val).

I didn't get your remark with sql-injection. I think sql injection is
only possible when using a command without using the
system.data.sqlclient.sqlparameters and concatening the values as
strings to the query (e.g. string.concat("SELECT * FROM foo WHERE col
= ","1 or 1=1; -- DROP DATABASE")).


Pascal

 

[William \(Bill\) Vaughn]

Yes, using ADO Parameters instead of concatenation is how to reduce SQL
injection opportunities.
I'm glad you got it working...

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________