CODEWITHSUNDEEP
c#sql-server
Brian Wang
Brian Wang

Reputation: 58

C# SQL Server String Casted to Int During Retrieval

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++;
}

Upvotes: 2

Views: 64

Answers (1)

SqlZim
SqlZim

Reputation: 38033

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#?

Upvotes: 1

Related Questions