Funny search error

I have two fields I am searching on ... address and zipCode

When I have both pieces of data, everything is fine, i get back the expected results, but i am unable able to search on the individual pieces. Here is the SQL I am using. It's placed within a store procedure in Sqlserver that I amreferencing from my VB.net code... any help??

 IF ((NOT @.address IS NULL) AND( NOT @.zipCode IS NULL))
 BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @.address AND ShpAddr_ZipCode = @.zipCode
 END
ELSE IF NOT @.address IS NULL
 BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @.address
 END
ELSE IF NOT @.zipCode IS NULL
 BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_ZipCode = @.zipCode
 END

any help would be great. thanksMy guess is that your parameters (@.address and @.zipCode) contain empty strings, not NULLs.

You VB.NET code should be setting the parameter value to SQLString.Null if the user did not choose to use the parameter.

I would then also consider changing your stored procedure to simply this:

SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = ISNULL(@.address,ShpAddr_Address1) AND ShpAddr_ZipCode = ISNULL(@.zipCode,ShpAddr_ZipCode)

I make this suggestion for ease of code maintenance. I myself have gotten burned several times over the past month by not changing all relevant similar blocks of code in a stored procedure when the logic needed to change.

Terri|||I'm trying this right now as we speak, but my program refuses to reconize what a "SqlString" is. Is there anything special i have to import?|||It's in the System.Data.SqlTypes namespace, so you can either import the namespace or reference it with the fully qualified name: System.Data.SqlTypes.SqlString.Null.

Terri