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();
}
![Sheraz Ahmed](https://i.stack.imgur.com/uj4w2.jpg?s=256&g=1)
Author by
Sheraz Ahmed
Updated on July 25, 2022Comments
-
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();