Structured types must have at least one field c#

11,732

ANSWER:

I was so dumb to ask that Question , but at least now i know that what causes this exception

thanks to @Gusman

If your DataTable is empty as it was in my case this exception can be raised

        string[] str = new string[] {"sheraz" , "ahmed" , "khan"};
        DataTable tvp = new DataTable();


        tvp.Columns.Add("names");
        foreach (string item in str)
        {
            tvp.Rows.Add(item);
        }

        foreach (DataRow r in tvp.Rows)
        {
            Console.WriteLine(r["names"].ToString());
        }

        SqlConnection conn = new SqlConnection("Data Source=SHZAK;Initial Catalog=synchroniztionTesing;Integrated Security=True");
        conn.Open();

        using (conn)
        {
            SqlCommand cmd = new SqlCommand("strpdPassAnStringArray", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
            tvparam.SqlDbType = SqlDbType.Structured;
            cmd.ExecuteScalar();
        }
Share:
11,732
Sheraz Ahmed
Author by

Sheraz Ahmed

Updated on July 25, 2022

Comments

  • Sheraz Ahmed
    Sheraz Ahmed almost 2 years

    What I actually want to do is I want to send a string array to SQL Server. I am using SQL Server 2008.

    This can be marked as duplicate but I am facing a whole other problem when implementing a solution from stactoverflow

    LINK: How to pass an array into a SQL Server stored procedure

    Here is my C# and stored procedure code

    C# code:

    string[] str = new string[] {"s" , "a" , "k"};
    
    DataTable dt = new DataTable();
    dt.Columns.Add("names");
    
    foreach (string item in str)
    {
        dt.Rows.Add(item);
    }
    
    foreach (DataRow r in dt.Rows)
    {
        Console.WriteLine(r["names"].ToString());
    }
    
    DataTable tvp = new DataTable();
    
    SqlConnection conn = new SqlConnection("Data Source=SHZAK;Initial Catalog=synchroniztionTesing;Integrated Security=True");
    conn.Open();
    
    using (conn)
    {
        SqlCommand cmd = new SqlCommand("strpdPassAnStringArray", conn);
        cmd.CommandType = CommandType.StoredProcedure;
    
        SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
        tvparam.SqlDbType = SqlDbType.Structured;
    
        cmd.ExecuteScalar();
    }
    

    Before making stored procedure I created a custom type:

    CREATE TYPE dbo.arrayOfNames
    AS TABLE
    (
      name  nvarchar(max)
    );
    

    Stored procedure code:

    CREATE PROCEDURE [dbo].[strpdPassAnStringArray]
          @List AS dbo.arrayOfNames READONLY
    AS
    BEGIN
        SET NOCOUNT ON;
    
        SELECT name FROM @List; 
    END
    

    But in application code it is raising this exception:

    There are not enough fields in the Structured type. Structured types must have at least one field.

    on line

    cmd.ExecuteScalar();