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
– spender, Commented Feb 26, 2017 at 18:03
You need to format the cell as text. stackoverflow.com/questions/2067926/… — SqlZim
– SqlZim, Commented Feb 26, 2017 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
– Brian Wang, Commented Feb 26, 2017 at 18:11

Community · Accepted Answer · 2017-05-23 12:25:10Z

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, 2017 at 12:25

CommunityBot

11 silver badge

answered Feb 26, 2017 at 18:06

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Wang Over a year ago

Thank you very much, I never considered this would be a problem at the Excel's end.

Collectives™ on Stack Overflow

C# SQL Server String Casted to Int During Retrieval

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related