The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = isnull ]

Question

I want to select from a table where a column matches a given parameter. If the parameter is null, I want to select all records from the table. The relevant code below is what throws this error.

    private static string _dbJobCreate = 
"CREATE TABLE Job (jID int primary key identity(1,1), jAddress nvarchar(64) not null);";

    private static string _dbJobSelect = 
"SELECT jID FROM Job WHERE jAddress = @jAddress OR @jAddress IS NULL";

    public static DataTable GetJobs(string jAddress)
    {
        SqlCeParameter pjAddress = new SqlCeParameter();
        pjAddress.ParameterName = "@jAddress";

        if (!string.IsNullOrEmpty(jAddress))
        {
            pjAddress.Value = jAddress;
        }
        else
        {
            pjAddress.Value = DBNull.Value;
        }

        return ExecuteDataTable(_dbJobSelect, pjAddress);
    }

Exception: The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = isnull ]

How can I efficiently accomplish this without error in SQLCE?

Answer 1

My solution is to select all rows from the database, and filter down the rows in .NET if a parameter was passed in. This could become troublesome if there were a large number of jobs, although I suppose I'd move to a real database if that ever happens.

private static string _dbJobSelect = "SELECT jID, jAddress FROM Job";

public static DataTable GetJobs(string jAddress)
{
    DataTable dt = ExecuteDataTable(_dbJobSelect);

    if (!string.IsNullOrEmpty(jAddress))
    {
        DataView dv = dt.DefaultView;
        dv.RowFilter = string.Format("jAddress = '{0}'", jAddress);
        dt = dv.ToTable();
    }

    return dt;
}