System.ArgumentException: The table type parameter must have a valid type name

Question

I am trying to pass in a user defined table type into a query in C#.

the type is defined with 2 columns (org and sub org)

this is what my code looks like:

DataSet ds = new DataSet();
try
{
    DataTable FilteredOrgSubOrg = new DataTable("OrgSubOrgValueType");
    FilteredOrgSubOrg.Columns.Add("org", typeof(string));
    FilteredOrgSubOrg.Columns.Add("subOrg", typeof(string));
    FilteredOrgSubOrg.Rows.Add(org, orgsub);
    using (SqlConnection conn = new SqlConnection(cCon.getConn()))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = 
                "select * from myTable ex where year = @year' and qtr = @qtr" +
                " and EXISTS(SELECT 1 FROM @OrgSubOrg tt  WHERE ex.org like tt.org" +
                " AND ex.orgsub = tt.suborg  )"+
                " order by ex.org,year, qtr DESC";
            // 2. set the command object so it knows
            // to execute a stored procedure
            // 3. add parameter to command, which
            // will be passed to the stored procedure
            cmd.Parameters.Add(new SqlParameter("@OrgSubOrg", FilteredOrgSubOrg));
            cmd.Parameters.Add(new SqlParameter("@year", year));
            cmd.Parameters.Add(new SqlParameter("@qtr", qtr));
            conn.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = cmd;
            sqlDA.Fill(ds);
        }
    }

am i passing the parameters in incorrectly?

when i do it in SQL server like so:

declare @OrgSubOrg OrgSubOrgValueType
insert into @OrgSubOrg  values ('05%','00000000')
insert into @OrgSubOrg values ('03%','00000000')
------------ complete -----------------------------------
select * from myTable ex
where 
year = '2013' and qtr = '1' 
and EXISTS(
               SELECT 1 
               FROM @OrgSubOrg tt               
               WHERE ex.org like tt.org
                 AND ex.orgsub = tt.suborg  )
order by ex.org,year, qtr DESC
everything works like it should.

i also tried passing it in like so:

  SqlParameter p = cmd.Parameters.Add(new SqlParameter("@OrgSubOrg", SqlDbType.Structured));
                     p.Value = FilteredOrgSubOrg;

but am getting the same error

The table type parameter '@OrgSubOrg' must have a valid type name.

could it be that i can't pass it to a SQL command, i have similar code in another place, that works great with a stored procedure...?

Solution 1

Set mapping to your type in SqlServer using TypeName property that: Gets or sets the type name for a table-valued parameter, that has to fix .

p.TypeName = "dbo.MyType";

Check as well Table-Valued Parameters post

Solution 2

Note that this may also happen when you're executing a stored procedure and you don't have the SqlCommand.CommandType set to CommandType.StoredProcedure, as such:

using (SqlCommand cmd = new SqlCommand("StoredProcName", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
}

Solution 3

You can get this error also when you wanna pass table params into stored procedure. There is happen if you use entity famework Context.Database.SqlQuery(). You must necessary set TypeName property for your table params.

SqlParameter codesParam = new SqlParameter(CODES_PARAM, SqlDbType.Structured);
            SqlParameter factoriesParam = new SqlParameter(FACTORIES_PARAM, SqlDbType.Structured);
            codesParam.Value = tbCodes;
            codesParam.TypeName = "[dbo].[MES_CodesType]";
            factoriesParam.Value = tbfactories;
            factoriesParam.TypeName = "[dbo].[MES_FactoriesType]";
            var list = _context.Database.SqlQuery<MESGoodsRemain>($"{SP_NAME} {CODES_PARAM}, {FACTORIES_PARAM}"
                , new SqlParameter[] {
                   codesParam,
                   factoriesParam
                }
                ).ToList();

Comments

[Madam Zu Zu]

didn't work as a query enbded in code, once i made a stored procedure out of it, all my problems went away.

[clweeks]

That was exactly my problem. Thanks!

[Michael Bray]

My problem too, more or less.. In my case, it was already a stored procedure, but I was executing the call to database with QueryType.Text. Changing to QueryType.StoredProcedure fixed it.

[Bennett Elder]

^ I would check this before looking at TypeName. Seems more likely this is the cause for any vanilla SqlCommand getting this error