asked on

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.

Scott Fell

For the field with zero padding.

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

Stefan Motz

ASKER

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.

Scott Fell

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

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Big Monty

it wasn't a duplicate comment, it may not work because the " " may be ignored, that's why I suggested adding  .

don't give me points for it, i was just mentioning it

Stefan Motz

ASKER

Thank you guys.
Both the single quote and the space trick worked.
I actually used this code:
<td align="center"> <%=rs("Emp_Id")%></td>

Scott Fell

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.

Stefan Motz

ASKER

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  

Scott Fell

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.

Stefan Motz

ASKER

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.