Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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

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
)

Open in new window


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

Open in new window

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.
0
andyw27
Asked:
andyw27
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
You have an extra coma at the end of

 Value = item_number_list,       line 13
0
 
andyw27Author Commented:
ah yeah, fixed that, prob still remains.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now