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...?

volody · Answer 1 · 2016-12-29T01:15:53.547

83

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

edited Dec 29 '16 at 01:15

answered Jul 23 '13 at 18:35

volody

6,946
3
42
54

4

didn't work as a query enbded in code, once i made a stored procedure out of it, all my problems went away. – Madam Zu Zu Aug 15 '13 at 20:06
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. – Michael Bray Jul 20 '17 at 14:44

score 18 · Answer 2 · answered Dec 16 '16 at 17:46

18

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

answered Dec 16 '16 at 17:46

bvoyelr

920
8
11

2

^ I would check this before looking at TypeName. Seems more likely this is the cause for any vanilla SqlCommand getting this error – Bennett Elder Jan 11 '18 at 00:51

score 7 · Answer 3 · answered Nov 17 '16 at 08:52

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();

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

3 Answers3

Linked