title: Passing multiple tables to stored procedure with multiple table-valued parameters problem
body: <p>I am trying to pass DataTables to a stored procedure that accepts multiple table-valued parameters. The problem is, it seems that all the parameters are being passed to the first one so I am getting errors like this:</p>
  
  <blockquote>
    <p>Trying to pass a table-valued
    parameter with 13 column(s) where the
    corresponding user-defined table type
    requires 17 column(s).</p>
  </blockquote>
  
  <p>Here's my code..</p>
  
  <p>Stored Procedure header:</p>
  
  <pre><code>CREATE PROCEDURE TestProc
  @Parm1 Temp1TableType READONLY,
  @Parm2 Temp2TableType READONLY,
  @Parm3 Temp3TableType READONLY,
  @Parm4 Temp4TableType READONLY,
  @Parm5 Temp5TableType READONLY
  </code></pre>
  
  <p>@Parm1 has 17 columns, @Parm2 13 columns, @Parm3 3 columns, @Parm4 11 columns and @Parm5 has 8 columns.</p>
  
  <p>C# code (I use Microsoft.ApplicationBlocks)</p>
  
  <pre><code>DataTable dtTable1 = dsSource.Tables[0];
  DataTable dtTable2 = dsSource.Tables[1];
  DataTable dtTable3 = dsSource.Tables[2];
  DataTable dtTable4 = dsSource.Tables[3];
  DataTable dtTable5 = dsSource.Tables[4];
  
  SqlParameter param1 = new SqlParameter("@Parm1", dtTable1);
  SqlParameter param2 = new SqlParameter("@Parm2", dtTable2);
  SqlParameter param3 = new SqlParameter("@Parm3", dtTable3);
  SqlParameter param4 = new SqlParameter("@Parm4", dtTable4);
  SqlParameter param5 = new SqlParameter("@Parm5", dtTable5);
  
  param1.SqlDbType = SqlDbType.Structured;
  param2.SqlDbType = SqlDbType.Structured;
  param3.SqlDbType = SqlDbType.Structured;
  param4.SqlDbType = SqlDbType.Structured;
  param5.SqlDbType = SqlDbType.Structured;
  
  SqlParameter[] parms = new SqlParameter[5];
  parms[0] = param1;
  parms[1] = param2;
  parms[2] = param3;
  parms[3] = param4;
  parms[4] = param5;
  
  returnVal = SqlHelper.ExecuteScalar(CfgKeys.ConnString, CommandType.StoredProcedure, "TestProc", parms);
  </code></pre>
  
  <p>Today I tried using SqlClient instead of Microsoft.ApplicationBlocks but I still got the same error.</p>
  
  <pre><code>SqlConnection conn = new System.Data.SqlClient.SqlConnection(CfgKeys.ConnString);
  conn.Open();
  using (conn) {
     SqlCommand cmdTestDataToServer= new SqlCommand("TestProc", conn);
     cmdTestDataToServer.CommandType = CommandType.StoredProcedure;
  
     SqlParameter param1 = new SqlParameter("@Parm1", dtTable1);
     SqlParameter param2 = new SqlParameter("@Parm2", dtTable2);
     SqlParameter param3 = new SqlParameter("@Parm3", dtTable3);
     SqlParameter param4 = new SqlParameter("@Parm4", dtTable4);
     SqlParameter param5 = new SqlParameter("@Parm5", dtTable5);
  
     param1.SqlDbType = SqlDbType.Structured;
     param2.SqlDbType = SqlDbType.Structured;
     param3.SqlDbType = SqlDbType.Structured;
     param4.SqlDbType = SqlDbType.Structured;
     param5.SqlDbType = SqlDbType.Structured;
  
     cmdTestDataToServer.Parameters.Add(param1);
     cmdTestDataToServer.Parameters.Add(param2);
     cmdTestDataToServer.Parameters.Add(param3);
     cmdTestDataToServer.Parameters.Add(param4);
     cmdTestDataToServer.Parameters.Add(param5);
  
     returnVal = cmdTestDataToServer.ExecuteScalar();
  }
  
  conn.Close();
  </code></pre>
  
  <p>When I run this, it seems that all the parameters are being passed to @Param1 hence the error I mentioned earlier. It works when I manually run the procedure in TSQL, so the error is on my code. Can anyone spot where I went wrong?</p>
  
  <p>TIA!</p>