Passing multiple tables to stored procedure with multiple table-valued parameters problem

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:

Trying to pass a table-valued parameter with 13 column(s) where the corresponding user-defined table type requires 17 column(s).

Here's my code..

Stored Procedure header:

CREATE PROCEDURE TestProc
@Parm1 Temp1TableType READONLY,
@Parm2 Temp2TableType READONLY,
@Parm3 Temp3TableType READONLY,
@Parm4 Temp4TableType READONLY,
@Parm5 Temp5TableType READONLY

@Parm1 has 17 columns, @Parm2 13 columns, @Parm3 3 columns, @Parm4 11 columns and @Parm5 has 8 columns.

C# code (I use Microsoft.ApplicationBlocks)

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);

Today I tried using SqlClient instead of Microsoft.ApplicationBlocks but I still got the same error.

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();

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?

TIA!

Answers


Found the problem.. Forgot to mention that DataTable data is sent through a web service, so it was using the DataSet method GetXml. The problem was that the GetXml method does not include NULL columns hence the missing columns.

Thanks guys!


Need Your Help

url rewrite internal server error

apache mod-rewrite

I am getting thrown errors every time i implement this code for mod-rewriting in my .htaccess file

same values from php array in new array

php arrays

i have a problem with arrays, there are not my friends :)

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.