andyw27
asked on
Passing Table-Valued Parameter (Computed Columns)
Hello,
I have created a table valued parameter that’s works fine in SQL. I’m now having trouble calling this from within a C# environment
Here is the type definition
And here is the code that I use to call it
I’m passing it a datable which contains the 2 columns – which match the non-computed ones?
Any idea if this is solvable?
Thanks.
I have created a table valued parameter that’s works fine in SQL. I’m now having trouble calling this from within a C# environment
Here is the type definition
CREATE TYPE ListTableStock AS TABLE (
item_number_concat varchar(10) NOT NULL,
item_desc varchar(50),
stock int NOT NULL,
item_number AS CAST( LEFT(item_number_concat, CHARINDEX('-', item_number_concat + '-') - 1) AS varchar(10) ), --split out the item_number
ver AS CASE WHEN ISNUMERIC(CAST( SUBSTRING(item_number_concat, CHARINDEX('-', item_number_concat + '-') + 1, 10) AS varchar(10) )) = 1
THEN CAST( SUBSTRING(item_number_concat, CHARINDEX('-', item_number_concat + '-') + 1, 10) AS varchar(10) )
ELSE NULL END
)
And here is the code that I use to call it
try
{
var cmd = new SqlCommand("get_item_id", connection)
{
CommandType = CommandType.StoredProcedure
};
var ItemsList = new SqlParameter
{
ParameterName = "@ItemsList",
Direction = ParameterDirection.Input,
TypeName = "ListTableStock",
Value = item_number_list,
};
cmd.Parameters.Add(ItemsList);
connection.Open();
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(allData);
connection.Close();
}
catch (Exception ex)
{
connection.Close();
}
The last two columns of the type are computed. Therefore the SQL exception I’m getting is that I’m passing 3 into a type that requires 5 ?I’m passing it a datable which contains the 2 columns – which match the non-computed ones?
Any idea if this is solvable?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER