ASP.NET - Sql Server Data is not binding to Datatable

Asked By Venkatesh Desai on 24-Sep-14 04:07 PM
I have 2 different databases in my project.  Examples like MyDB1 and MyDB2.In MyDB2 am having a procedure, for that am sending 73 parameters.  My DB is taking all the parameters data which i sent from code behind.  Then it is showing selectant output.up to here it is fine.  But in front end that selectant data is not coming to the data table means it is not properly binding to the database.  Example in Selectant output am having 30 columns out of that 30 columns i am getting hardly 5 columns and the remaining columns are showings as empty.

My required and database output is like this,


But am getting like this


My Code goes like this,

  DataTable dt = new DataTable();
        dt= Cost(Parameter1,Parameter2,Parameter3......);
  public DataTable Cost(int Parameter1, int Parameter2, int Parameter3,........)
        {
    DataTable dt = null;
            DBConnect objdb = new DBConnect();
            errmsg = string.Empty;
      try
            {
System.Data.SqlClient.SqlParameter[] p = new SqlParameter[73];
                p[0] = new SqlParameter("@Parameter1", Parameter1);
                p[1] = new SqlParameter("@Parameter2", Parameter2);
                p[2] = new SqlParameter("@Parameter3", Parameter3);
----------------------------------------------------------
----------------------------------------------------------
----------------------------------------------------------
----------------------------------------------------------
               p[72] = new SqlParameter("@Parameter72", Parameter72);
         dt = objdb.ExecuteProcForData("MyProcedureName", errmsg, p);
               objdb = null;
  }
    catch (Exception ex)
          {
               ex.Message.ToString();
          }

          return dt;
}
Sreenath Kalahasti replied to Venkatesh Desai on 26-Sep-14 06:55 PM
Venkatesh,

WOW... 73 parameters? 1st of all you want to rewrite sending an object to the stored procedure by making use of "User Defined Table Types" in SQL Server. 

1) Create a class file with all the fields and populate those fields
2) Create a User Defined table Type
3) Create a procedure which acccepts this User Defined Table Type as parameter.
4) Call the procedure within your application and pass the object along with it.

Thanks,
Sree