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?

Useful discussion: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/83821937-3929-4901-a78e-b675cf8a70ca/how-to-send-a-null-value-as-a-parameter — Andersson Melo, Jul 11 '13 at 14:32

score 2 · Answer 1 · answered Nov 20 '13 at 15:51

2

You can avoid this error by specifying the types of parameters which are passed to the query. So all you need to do is:

pjAddress.DbType = DbType.String;

answered Nov 20 '13 at 15:51

Chris W.

366
4
15

1

For me this was the perfect solution. – Phil Feb 24 '16 at 09:34

Zeb Rawnsley · Accepted Answer · 2012-12-30T12:26:17.343

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

score 0 · Answer 3 · answered May 14 '14 at 08:03

0

You can cast it to varchar

SELECT jID FROM Job WHERE jAddress = @jAddress 
    OR cast(@jAddress AS varchar(4000)) IS NULL

answered May 14 '14 at 08:03

VahidN

18,457
8
73
117

score 0 · Answer 4 · answered Oct 21 '16 at 14:41

I ran into this problem recently and discovered that I hadn't actually added the query parameter to the IDbCommand. I'm not sure what your ExecuteDataTable method looks like, but my code was similar to the following (where db is an IDbConnection instance):

var sql = "SELECT jID FROM Job WHERE jAddress = @jAddress OR @jAddress IS NULL";
var cmd = db.CreateCommand();
cmd.CommandText = sql;
var param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@jAddress";
param.Value = string.IsNullOrEmpty(pjAddress) ? DBNull.Value : (object)pjAddress;
cmd.Parameters.Add(param);  // THIS WAS THE STEP I WAS MISSING!!!
// ... rest of the code to execute the query and load the results ...

After adding the cmd.Parameters.Add(param), the exception disappeared.

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

4 Answers4