Call stored procedure with user-defined table type as parameter; error: column name or number of supplied values does not match table definition

Question

I have tried several ways to make this work but getting the same error when ExecuteReader is called:

Column name or number of supplied values does not match table definition

Looks like the table type column names are not getting mapped with datatable column name but not sure why. Following is the .net and SQL code which has worked for other people who have written blogs and made videos where I got this code from but unfortunately does not work for me. Please reply if you see anything wrong or I have missed anything.

Thank you.

.Net code:

var dt = new DataTable();
dt.Columns.Add(new DataColumn("DataColumnName",typeof(string)));
       
foreach(var item in request.ControlValues)
{
    var dataRow = dt.NewRow();
    dataRow["DataColumnName"] = item.DataColumnName;                        
    dt.Rows.Add(dataRow);
}

SqlConnection conn = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand("[dbo].[GetQuoteSearchData]", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter cob = cmd.Parameters.AddWithValue("@cob", request.Cob);
cob.SqlDbType = SqlDbType.VarChar;
cob.Direction = ParameterDirection.Input;

SqlParameter function = cmd.Parameters.AddWithValue("@function", request.Function);
function.SqlDbType = SqlDbType.VarChar;
function.Direction = ParameterDirection.Input;

SqlParameter searchColumnName = cmd.Parameters.AddWithValue("@searchColumnName", Helper.GetControlSearchColumnName(request.SearchType));
searchColumnName.SqlDbType = SqlDbType.VarChar;
searchColumnName.Direction = ParameterDirection.Input;

SqlParameter controlValues = cmd.Parameters.AddWithValue("@controlValues", dt);
controlValues.Direction = ParameterDirection.Input;
controlValues.SqlDbType = SqlDbType.Structured;
controlValues.TypeName = "[dbo].[ControlValuesTable]";

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

SQL Server / stored procedure code:

CREATE PROCEDURE [dbo].[GetQuoteSearchData]
    @cob nvarchar(50),
    @function nvarchar(50),
    @searchColumnName nvarchar(50),
    @controlValues as [dbo].[ControlValuesTable] READONLY
AS
BEGIN
END

User-defined table type :

CREATE TYPE dbo.ControlValuesTable AS 
    TABLE
    (
        DataColumnName varchar(100) 
    );
GO

Answer 1

Here is a simplified version of your code (changed so it can run stand alone):

        string ConnectionString = "cs"; // set your connection string here

        var dt = new DataTable();
        dt.Columns.Add(new DataColumn("DataColumnName", typeof(string)));

        var dataRow = dt.NewRow();
        dataRow["DataColumnName"] = "col1";
        dt.Rows.Add(dataRow);

        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("[dbo].[GetQuoteSearchData]", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter cob = cmd.Parameters.AddWithValue("@cob", "aa");
            cob.SqlDbType = SqlDbType.VarChar;
            cob.Direction = ParameterDirection.Input;

            SqlParameter function = cmd.Parameters.AddWithValue("@function", "bb");
            function.SqlDbType = SqlDbType.VarChar;
            function.Direction = ParameterDirection.Input;

            SqlParameter searchColumnName = cmd.Parameters.AddWithValue("@searchColumnName", "cc");
            searchColumnName.SqlDbType = SqlDbType.VarChar;
            searchColumnName.Direction = ParameterDirection.Input;

            SqlParameter controlValues = cmd.Parameters.AddWithValue("@controlValues", dt);
            controlValues.Direction = ParameterDirection.Input;
            controlValues.SqlDbType = SqlDbType.Structured;
            controlValues.TypeName = "[dbo].[ControlValuesTable]";

            conn.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            conn.Close();
        }

I also added a line to the body of your stored proc, as the stored proc you posted will give an error like:

Incorrect syntax near 'END'.

without it.

drop proc [dbo].[GetQuoteSearchData]
GO
drop type dbo.ControlValuesTable
GO
CREATE TYPE dbo.ControlValuesTable AS 
    TABLE
    (
        DataColumnName varchar(100) 
    );
GO

CREATE PROCEDURE [dbo].[GetQuoteSearchData]
    @cob nvarchar(50),
    @function nvarchar(50),
    @searchColumnName nvarchar(50),
    @controlValues as [dbo].[ControlValuesTable] READONLY
AS
BEGIN
 select * from @controlValues
END
GO

This ran ok on my system. If you still can't get it to work, perform an sql profiler trace to capture the raw sql request being sent to Sqlserver, and try replaying the request in SSMS.
e.g. the request trace should look something like:

declare @p4 dbo.ControlValuesTable
insert into @p4 values(N'col1')

exec [dbo].[GetQuoteSearchData] @cob='aa',@function='bb',@searchColumnName='cc',@controlValues=@p4

which when replayed in SSMS returns a row.

As a side note, I think you should also be able to remove this line:

controlValues.TypeName = "[dbo].[ControlValuesTable]";  

as it's not required when CommandType = CommandType.StoredProcedure.