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.
romsomIT DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
For the field with zero padding.  

<%=right("000000"&rs("Emp_Id"),6)%>
0
romsomIT DeveloperAuthor 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 MVEDeveloper & EE ModeratorCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
romsomIT DeveloperAuthor 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 MVEDeveloper & EE ModeratorCommented:
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
romsomIT DeveloperAuthor 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 MVEDeveloper & EE ModeratorCommented:
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
romsomIT DeveloperAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.