C# SQL Server String Casted to Int During Retrieval

Question

I have the following c# code to retrieve a bunch of literal strings from SQL Server Database. The table contains several column that includes a mix of one and two characters length of codes, such as "AT", "01", "BC". Column data type is VARCHAR, and codesheets is a Microsoft.Office.Interop.Excel._Worksheet.

The problem is that when I get "01" as the query result, the Excel Sheet shows 1 instead of 01. This causes problem when I do further analysis on the table. I suspect that during the retrieval, 01 somehow gets cast as integer instead of string. Can someone point me why this happens?

sqlString = "SELECT DISTINCT(BUSCODES) AS COLCODES FROM ANALYSISTABLE";
SqlCommand codeRetrieval = new SqlCommand(sqlString, dbConn);
codeRetrieval.CommandTimeout = 0;
SqlDataReader codeReader = codeRetrieval.ExecuteReader();


while (codeReader.Read())
{
    if (codeReader["COLCODE"] == DBNull.Value)
        codeSheets.Cells[1, colIndex] = "NULL";
    else if (string.Compare(codeReader["COLCODE"].ToString(), "") == 0)
        codeSheets.Cells[1, colIndex] = "Empty String";
    else if (string.Compare(codeReader["COLCODE"].ToString(), " ") == 0)
        codeSheets.Cells[1, colIndex] = "Single Space";
    else if (string.Compare(codeReader["COLCODE"].ToString(), "  ") == 0)
        codeSheets.Cells[1, colIndex] = "Double Space";
    else
        codeSheets.Cells[1, colIndex] = codeReader["COLCODE"].ToString();

    colIndex++;
}

Are you sure that this isn't the excel cell being formatted as a number? — spender, Feb 26 '17 at 18:03
You need to format the cell as text. http://stackoverflow.com/questions/2067926/format-an-excel-column-or-cell-as-text-in-c — SqlZim, Feb 26 '17 at 18:04
Thank you guys for the comments, I never thought of the Excel auto-formatted it. I assumed if I store a string type, it will be a string type in Excel. But I guess I was way wrong, LOL. — Brian Wang, Feb 26 '17 at 18:11

score 1 · Accepted Answer · edited May 23 '17 at 12:25

1

add this line:

    codeSheets.Cells[1, colIndex].NumberFormat = "@"; // cell as a text

or format your columns as text prior to this, as seen here: Format an Excel column (or cell) as Text in C#?

edited May 23 '17 at 12:25

Community

1
1

answered Feb 26 '17 at 18:06

SqlZim

37,248
6
41
59

Thank you very much, I never considered this would be a problem at the Excel's end. – Brian Wang Feb 26 '17 at 18:13

C# SQL Server String Casted to Int During Retrieval

1 Answers1