• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Export to Excel from SQL Server - Wrong Data Type - Classic ASP

Hi Experts,
I'm trying to export data from my SQL Server database to an Excel file. I am using Classic ASP.
The problem is with the Emp_Id column. The data type is Varchar(6) . Some Emp_Id strart with a zero. After the export the 0 disappears from the beginning of these employee numbers. How could I prevent this?
<%
 'Build & Execute Your SQL Command

 strSQL = "SELECT * FROM Workgroups order by Last_Name"
 Set rs = Conn.Execute(strSQL)

 'Set the HTTP Header
 Response.ContentType = "application/vnd.ms-excel"
 Response.AddHeader "Content-Disposition", "filename=""AllEmployees.xls"""

 'Output Table %>
<table border="1" cellspacing="0" cellpadding="5">
<tr bgcolor="#000080">
<th align="center"><font color="#ffffff"><b>Emp#</b></font></th>
<th align="center"><font color="#ffffff"><b>Last Name</b></font></th>
<th align="center"><font color="#ffffff"><b>First Name</b></font></th>
<th align="center"><font color="#ffffff"><b>Work Area</font></th>
<th align="center"><font color="#ffffff"><b>Assignment</font></th>
<th align="center"><font color="#ffffff"><b>Supervisor</font></th>
</tr>
  <% Do While Not rs.EOF %>
<%
 if color = "#fffff0" then
 color = "#D6E3E6"
 else
 color = "#fffff0"
 end if
 %>
<tr style="background-color:<%=color%>">
<td align="center"><%=rs("Emp_Id")%></td>
<td align="left" nowrap><%=rs("Last_Name")%></td>
<td align="left"><%=rs("First_Name")%></td>
<td align="center"><%=rs("Work_Area")%></td>
<td align="center"><%=rs("Assignment")%></td>
<td align="left"><%=rs("Supervisor")%></td>

</tr>
  <% rs.MoveNext : Loop %>
</table>

 <%
 'Stop Processing Page Now
 Response.End
 %>

Open in new window

Please take a look at my code; I would appreciate your help.
1
romsom
Asked:
romsom
  • 5
  • 4
1 Solution
 
Scott Fell, EE MVEDeveloperCommented:
For the field with zero padding.  

<%=right("000000"&rs("Emp_Id"),6)%>
0
 
romsomAuthor Commented:
Hi Padas,
I tried it but unfortunately the zeroes still disappear after the export.
First I thought it was because of the browser's history, and I went to a different computer. I didn't work either.
0
 
Scott Fell, EE MVEDeveloperCommented:
In other words, the number could be 10, 11, 12, 100.  But you want 000010, 000011,000012,000100?

The code I provided should be adding 6 zeros to the left, then only giving you the right 6.

What about
<%=cstr(right("000000"&rs("Emp_Id"),6))%>
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott Fell, EE MVEDeveloperCommented:
Ah, you mean the zeros are being lost in excel? or the screen.

Try this, add a space to the right and left.  

<%=" "&cstr(right("000000"&rs("Emp_Id"),6))&" "%>

or try a single quote to the left.
<%="'"&cstr(right("000000"&rs("Emp_Id"),6))%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
it wasn't a duplicate comment, it may not work because the " " may be ignored, that's why I suggested adding &nbsp;.

don't give me points for it, i was just mentioning it
0
 
romsomAuthor Commented:
Thank you guys.
Both the single quote and the space trick worked.
I actually used this code:
<td align="center">&nbsp;<%=rs("Emp_Id")%></td>
0
 
Scott Fell, EE MVEDeveloperCommented:
In excel, the single quote is what you use to designate text and not a number.  If that works, that would probably be the cleanest route.  If you are going to add spaces, I would use a space at both ends.  The reason is because you want it centered.  With one space, you will not have it perfectly centered.  With the single quote trick or space on either end, it will be centered.  

Glad it worked.
0
 
romsomAuthor Commented:
First I tried hard coding the zero, like this:
<td align="center">0<%=rs("Emp_Id")%></td>
After the export the 0 disappeared. Adding the spaces didn't help either.
<%=" "&cstr(right("000000"&rs("Emp_Id"),6))&" "%>
It worked only after I typed &nbsp;
0
 
Scott Fell, EE MVEDeveloperCommented:
Yea, hard coding the zero would still make it a number for excel.  That's why I thought of the space.  Did the single quote work?  It sounds like the actual number was in the emp_id and you didn't need the leading zeros.
0
 
romsomAuthor Commented:
The single quote also worked. Yes, the employee numbers I had problems with were like these:
0756B4
00324A
012345
etc.
Thank you very much again. Now I have a different question, I'll be posting it now.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now