ADO.NET setup for passing collection to be used as a TABLE VALUED PARAMETER in stored procedure RRS feed

  • Question

  • Hi All,

    I am getting "Failed to convert parameter value from List'1 to IEnumerable'1" on an Execute Non Query when using the following code:

    private void button16_Click(object sender, EventArgs e)
                SqlOperations sql = new SqlOperations();

                // no parameters to simple get radios proc
                ArrayList radios2 = sql.GetObjects(typeof(Radio2), "dbo.GetTestRadios", null);                                                

                List<Radio2> listOfRadios = new List<Radio2>();

                foreach (object obj in radios2)
                    Radio2 radio2 = (Radio2)obj;


                List<SqlParameter> prms = new List<SqlParameter>();

                prms.Add(sql.MakeParameter("@tvpRadio2", SqlDbType.Structured, 0, ParameterDirection.Input, listOfRadios, true, "dbo.Radio2TableType"));           
                sql.ExecuteNonQuery(CommandType.StoredProcedure, "Test.dbo.UseTableParameter", prms, false);

     The Type and Procedure:

    CREATE TYPE [dbo].[Radio2TableType] AS TABLE(
     [RadioID] [nvarchar](50) NULL,
     [RadioSerialNumber] [nvarchar](50) NULL

    CREATE PROCEDURE [dbo].[UseTableParameter]
    (  @tvpRadio2 dbo.Radio2TableType READONLY
     INSERT INTO dbo.RadioTestTwo (RadioID,RadioSerialNumber)
      SELECT RadioID,RadioSerialNumber FROM @tvpRadio2 

    The class in the collection:

        public class Radio2
            private string radioID;
            public string RadioID
                get { return radioID; }
                set { radioID = value; }

            private string radioSerialNumber;
            public string RadioSerialNumber
                get { return radioSerialNumber; }
                set { radioSerialNumber = value; }

    TIA Doyle

    Saturday, May 28, 2011 6:45 PM


  • Hello Doyle,

    According to your description, we can convert List to IEnumerable manually. Please try to use the following code.

    Just an example:

    List<string> myList = new List<string>(); 
    IEnumerable<string> myEnumerable = myList; 
    List<string> listAgain = myEnumerable.ToList(); 

    The code above can convert a List to an IEnumerable and back again.

    I hope this can help you.


    Nice weekend,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Jackie-Sun Tuesday, June 7, 2011 9:21 AM
    • Marked as answer by DoyleJofTexas Wednesday, June 8, 2011 1:34 PM
    Friday, June 3, 2011 8:35 AM

All replies