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

Answer 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