Char '00' not converting to int 01 from SQL Server in C#

Question

I am fetching a column from database of char(2) data type.

On an Event, I am changing the char data type to int and incrementing it by 1, with this code:

int i = 0;

using (SqlConnection sqlCon = new SqlConnection(Login.connectionString))
{
    string commandString = "SELECT MAX(CAST(Category_Code as INT)) FROM Category;";

    SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
    sqlCon.Open();

    SqlDataReader dr = sqlCmd.ExecuteReader();

    while (dr.Read())
    {
        i = 1;

        if (dr[0] == null)
        {
            Ctgry_CtgryCodeCb.Text = "1";
        }                
        else
        {
            int cat_next_code = int.Parse(dr[0].ToString());
            Ctgry_CtgryCodeCb.Text = (cat_next_code + 1).ToString();                
        }
    }
}

It is working properly but not for the first time (doesn't add 1 to empty or 0) as column is empty.It shows some error that it is not is correct format to convert. I also set the default value of the column to 0, but it shows ((0)) as default value.

Any help would be appreciated.

Answer 1

If you are using this code to increment primary key value of the database table, you shouldn't be doing this way. You should be using IDENTITY column feature available in the database.

Since you have not explained why you are not using IDENTITY column, looks like this code is for some other purpose.

As per your code you are getting Maximum value of some column from the database and incrementing it by one in the code.

When the table in the database is empty you not get anything is the reader. So While loop will not be executed at all. So even if you are checking for NullOrEmpty inside the while loop, it will never get executed.

Also you don't need to use SqlDataReader here. Since you are returning only one single value from the query you can use ExecuteScalar method of SqlCommand and get that value. It will be simpler.

var codeFromDb = sqlCmd.ExecuteScalar();
var cat_next_code = 0; 

if(!(codeFromDb is DBNull))
{
     cat_next_code =  Convert.ToInt32(codeFromDb);
}

Ctgry_CtgryCodeCb.Text = (cat_next_code + 1).ToString();

My strong recommendation is to use IDENTITY column instead of doing all this code.

This will help you resolve your current issue.

SqlDataReader is overkill in this case and I don't like to answer for the wrong approach but since you insist consider following.

SqlDataReader dr = sqlCmd.ExecuteReader();
int cat_next_code = 0;

if(dr.Read()) // while is not needed here. There will be only one row in the reader as per the query.
{
     i = 1;

     if(!dr.IsDBNull(0))
     {
         cat_next_code = int.Parse(dr[0].ToString());
     }
 }

 Ctgry_CtgryCodeCb.Text = (cat_next_code + 1).ToString();