There must be a simple solution to this problem but for the love of god I cannot figure it out!
I'm not too practiced with outputting data to Excel because I normally use reporting tools like Business Objects. But, I am just trying to modify an existing report on an existing C# web app. I am appending 25 additional columns of data to an Excel sheet. I have written a simple stored procedure that accepts params and outputs one row of data with 25 columns. I then call this function to execute for each mentee_id I pass into it. It works great but it prepends the 25 columns with a blank column. If anyone has any suggestions I would very much appreciate it....
//here is the function that executes the stored procedure:
protected string GetServiceCodes_byColumns(object mentee_id)
{
//string id = mentee_id.ToString();
//string id = "4530"; //for testing
string value = "0";
string retString = string.Empty;
try
{
//Displays current information from the database
string strConn = ConfigurationManager.AppSettings.Get("SQLConnectionString");
SqlConnection myDBConnection = new SqlConnection(strConn);
string sProc = ("[spMSSreport_MenteeSummaryByCenter_ServiceCodes]");
SqlCommand spCmd = new SqlCommand(sProc, myDBConnection);
spCmd.CommandType = CommandType.StoredProcedure;
SqlParameter pCntr = new SqlParameter("@cntr", ddlCenter.SelectedValue);
SqlParameter pStartDate = new SqlParameter("@start", txtStartDate.Text);
SqlParameter pEndDate = new SqlParameter("@end", txtEndDate.Text);
SqlParameter pUserID = new SqlParameter("@userid", mentee_id);
spCmd.Parameters.Add(pCntr);
spCmd.Parameters.Add(pStartDate);
spCmd.Parameters.Add(pEndDate);
spCmd.Parameters.Add(pUserID);
myDBConnection.Open();
SqlDataReader rdr = spCmd.ExecuteReader(); //should retrieve one row with 25 columns
rdr.Read();
for (int i = 0; i < 25; i++)
{
retString += "<td align='right'>" + rdr.GetValue(i).ToString() + "</td>";
}
value = retString;
dr.Close();
myDBConnection.Close();
}
catch (Exception ex)
{
Response.Write(ex);
}
return value;
}
// here is the bit of aspx that calls the function (it's the last line that calls the function)
<ItemTemplate>
<tr valign="top">
<td><%#DataBinder.Eval(Container.DataItem, "MentorName" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "MenteeName" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "MenteeWSUID" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "gender" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "sttype" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "ethniccd")%></td>
<td><%#DataBinder.Eval(Container.DataItem, "center1" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "center2" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "TMPcollege" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "FCOC" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "CAMP" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "SSS" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "CSF" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "Lead1000" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "year" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "term" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "fieldofstudy" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "street" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "city" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "st" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "zip" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "phone" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "email" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "email2" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "AssignedMentor" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "AssignedMentor2" )%></td>
<td><%#DataBinder.Eval(Container.DataItem, "AssignedMentor3" )%></td>
<td><%#DataBinder.Eval(Container.DataItem, "AssignedMentor4" )%></td>
<td><%#DataBinder.Eval(Container.DataItem, "AssignedMentor5" )%></td>
<td><%#DataBinder.Eval(Container.DataItem, "mentor_and_mentee" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "active" ) %></td>
<td><%#DataBinder.Eval(Container.DataItem, "inactive_reason" ) %></td>
<td align="right"><%#GetEngagementLevel(DataBinder.Eval(Container.DataItem, "mentee_id") ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "A" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "B" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "C" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "D" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "E" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "F" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "G" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "H" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "I" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "J" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "K" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "L" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "M" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "N" ) %></td>
<td align="right"><%#DataBinder.Eval(Container.DataItem, "O" ) %></td>
<td align="right"><%#GetServiceCodes_byColumns(DataBinder.Eval(Container.DataItem, "mentee_id") )%></td>
</tr>
</ItemTemplate>
It appears you are embedding the 25 td's within the final td. Remove the <td align='right' from your last line so it is simply:
<%#GetServiceCodes_byColumns(DataBinder.Eval(Container.DataItem, "mentee_id") )%>