Zeb Rawnsley Zeb Rawnsley - 2 months ago 6
C# Question

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

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

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;
}