SqlCommand.Prepare method requires all parameters to have an explicitly set type

Question

I have the following snippet of code in my WCF web service that builds a set of where conditions according to the formatting of the values of a provided dictionary.

public static Dictionary<string, string>[] VehicleSearch(Dictionary<string, string> searchParams, int maxResults)
{
    string condition = "";
    foreach (string key in searchParams.Keys)
    {
        //Split out the conditional in case multiple options have been set (i.e. SUB;OLDS;TOY)
        string[] parameters = searchParams[key].Split(';');
        if (parameters.Length == 1)
        {
            //If a single condition (no choice list) check for wildcards and use a LIKE if necessary
            string predicate = parameters[0].Contains('%') ? " AND {0} LIKE @{0}" : " AND {0} = @{0}";
            condition += String.Format(predicate, key);
        }
        else
        {
            //If a choice list, split out query into an IN condition
            condition += string.Format(" AND {0} IN({1})", key, string.Join(", ", parameters));
        }
    }

    SqlCommand cmd = new SqlCommand(String.Format(VEHICLE_QUERY, maxResults, condition));
    foreach (string key in searchParams.Keys)
        cmd.Parameters.AddWithValue("@" + key, searchParams[key]);
    cmd.Prepare();

Note that the values in the dictionary are explicitly set to strings and that they are the only items being sent into the AddWithValue statements. This produces SQL like this:

SELECT TOP 200 MVINumber AS MVINumber
    , LicensePlateNumber
    , VIN
    , VehicleYear
    , MakeG
    , ModelG
    , Color
    , Color2
FROM [Vehicle_Description_v]
WHERE 1=1 AND VIN LIKE @VIN

And errors out saying that

System.InvalidOperationException: SqlCommand.Prepare method requires all parameters to have an explicitly set type.

All searching that I have done says that I need to tell AddWithValue the type of values that I'm preparing, but all of my prepared values are strings and all examples I've seen don't perform anything extra when they're dealing with strings. What am I missing?

why `1=1` ? Considering it will be always true, and you're using `AND`, it will always depends of `Like @vin`, which means is totally innecesary the `1=1` .. — Gonzalo.-, Aug 13 '12 at 17:10
ElVieejo, I sometimes use something like WHERE 1=1 when building a dynamic query in code somewhere so the rest of my lines are " AND something" instead of trying to figure out which line is first and putting a WHERE just for that line and AND for the rest of the lines. — Jim, Aug 13 '12 at 17:34
EIVieejo, yes, exactly what Jim said. I use 1=1 at the beginning of the statement so that all I have to do is append ' AND' for every condition I have. Since the 1=1 won't really apply, using it keeps the code really clean. — taiidani, Aug 13 '12 at 18:50

marc_s · Accepted Answer · 2016-04-11T16:38:57.127

16

Instead of this:

cmd.Parameters.AddWithValue("@" + key, searchParams[key]);

you need to use something like this:

cmd.Parameters.Add("@" + key, SqlDbType.******).Value = searchParams[key];

You need to be able to somehow determine what datatype your parameters will have to be.

This can be something like:

SqlDbType.Int for integer values
SqlDbType.VarChar for non-Unicode strings (don't forget the specify a length of the string!)
SqlDbType.UniqueIdentifier for Guids
etc.

Using AddWithValue is convenient - but it leaves it up to ADO.NET to guess the datatype, based on the value passed in. Most of the time, those guesses are pretty good - but at times, they can be off.

I would recommend that you always explicitly say what datatype you want.

edited Apr 11 '16 at 16:38

answered Aug 13 '12 at 17:10

marc_s

732,580
175
1,330
1,459

1

That worked with some small changes. Using SqlDbType in place of the value produced the same error, I presume because it was expecting a value in that argument. Instead I assigned it to a prep variable and added `prep.SqlDbType = SqlDbType.VarChar;`, then specified a Size for prep as well. – taiidani Aug 13 '12 at 17:53
2

To expand on taiidani's answer, you can do this in one line like so: `cmd.Parameters.Add("@PARAM",SqlDbType.TYPE, VALUE.Length).Value = VALUE;` – Chris Dec 07 '12 at 16:24
I believe you meant `Add` instead of the second `AddWithValue` @marc_s. Otherwise [people get confused](http://stackoverflow.com/q/36554082/11683). – GSerg Apr 11 '16 at 16:25
@GSerg: oh yes, I did ! :-) Thanks - fixed. – marc_s Apr 11 '16 at 16:39

score 1 · Answer 2 · answered Aug 13 '12 at 17:32

1

If you read the documentation, you'll see that when you're using SQLCommand.Prepare, you need to use Parameters.Add and assign a datatype to each parameter. There is a good code sample in that link that will show you how to do it.

answered Aug 13 '12 at 17:32

Jim

3,482
22
18

SqlCommand.Prepare method requires all parameters to have an explicitly set type

2 Answers2

Linked