SQL Record Search But Field is Sometimes NULL?

Question

Hi,

I am trying to do a SQL query to see if an Address already exists in my database. My addresses are structure like this:

line1
line2
line3
city
state
zipcode
country

Sometimes line2 and line3 are NULL values in my database.

I am using .NET TableAdapter and DataTable to make my query. When I try to pass in my line2 parameter (@line2) it is equal to a string with value = nothing. My database does not interpret this as NULL and says the records don't match, but I know they do?

How do I pass in a string = nothing and have it compare to a NULL value in the database as being equal?

I have tried string = DBNULL.value but won't compile. I have done similar comparisons with integers etc. using integer = Nullable(of integer) but this does not exist for strings.

Thanks.

Answer 1

You need to use DBNull.Value

Answer 2

Null is special in most queries. Usually the way to specify matching null is "line2 is NULL"

Answer 3

Use DBNull.Value is a start, but I don't think it will work because when it will do NULL = NULL, it will always return false.

If an empty string is the same as NULL for you, then you can change your query to to ISNULL(line2,"") as line2, etc... and pass "" as parameter when your string is Nothing.

If you can't do that, you can modify your query to check if your parameter is null and if so, then verify that line2 IS NULL and not when it's not.

Hope that helps.