Geek Answers Handbook

Unable to add User-Defined-Data Type parameter to my query

I am trying to use User-Defined-Data Type with C # and SQL Server 2008, but I have an exception that occurs when the command is executed. ExecuteReader ().

string qyery = "SELECT * FROM Product WHERE IsAvailableProduct < @Param"; SqlCommand command = new SqlCommand(qyery, conn); SqlParameter param = new SqlParameter("@Param", SqlDbType.Structured); param.UdtTypeName = "MyBolean"; param.SqlDbType = SqlDbType.Structured; param.Value = 1; command.Parameters.Add(param); SqlDataReader reader = command.ExecuteReader();

Raised Exception:

Failed to convert parameter value from a Int32 to a IEnumerable``1.

What is wrong in my code?

EDIT :

If I change SqlDbType to Udt , I have this exception: Specified type is not registered on the target server.System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

If I set the DbType property as Int (which is the base type "MyBolean"), the result is a Failed to convert parameter value from a Int32 to a IEnumerable exception Failed to convert parameter value from a Int32 to a IEnumerable 1., too.

EDIT :

I changed the name because in SQL Server 2008 it is "User Define DATA Type" and "User Defined Types"

+4
source share
4 answers

SqlDbType value of the Structured enumeration is used to indicate that you are going around the Value Parameter table , so you get an error message that you get as the value you pass is an integer, and not a "list of something" to use as a TVP .

You need to install param.SqlDbType based on the underlying data type for MyBolean , which I suspect is probably Bit or Int .

+2
source

At first glance, I would suggest that you at least need to do SqlDbType = Udt (not Stuctured).

0
source

so when you set the value of a property, at least IEnumerable (i.e. a list of objects) must be implemented, but you provide an integer value, and this causes an error

0
source

I have custom table types in my database (suppose this is MyTableTypeList). They are used as parameters in my stored procedure. To pass values ​​through this data type, I do the following:

Declare a DataTable similar to my user-defined table type, fill in the values ​​in it.

Assign the SqlParameter SqlDbType property as SqlDbType.Structured "param.SqlDbType = SqlDbType.Structured"

Assign a TypeName property with a user-defined table type name. "param.TypeName =" MyTableTypeList "

0
source

More articles:


All Articles