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.

If `dr[0]` is null or empty string , it can't be converted to number. That's why you are getting this error, you should check for `dr[0].ToString()` null or empty string and set next_code to zero. You should be using identity column in database instead of incrementing in code. — Chetan, Aug 11 '18 at 21:48
I am sure you know how to use `if` to do condition checking. If not try learning it and using it. If you Still having issue in that, I will share the code. — Chetan, Aug 11 '18 at 21:52
Sorry to say,but it is still not working...Kindly help with code.... — Hafiz M Taha Waseem, Aug 11 '18 at 21:58
Can you update your code in the question? I will tell you what to correct. — Chetan, Aug 11 '18 at 21:59
What's not still working? What exactly is your error? Have you tried testing for null? Have you read the docs for int.TryParse? — Flydog57, Aug 11 '18 at 22:02
If `Category_Code` contains an `int` then store it as an `int`, you shouldn't need to CAST it. Rather than `int.Parse(dr[0].ToString())` consider `dr.GetInt32(0)`. SqlDataReader is IDisposable so should be in a `using` block. If you only want the first value from the first row, then consider ExecuteScalar rather than ExecuteReader. `cat_next_code` is the wrong name for the variable, since it holds the last code, not the next one. — Richardissimo, Aug 12 '18 at 06:08

score 2 · Accepted Answer · edited Aug 12 '18 at 06:24

2

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();

edited Aug 12 '18 at 06:24

marc_s

732,580
175
1,330
1,459

answered Aug 11 '18 at 22:19

Chetan

6,711
3
22
32

if I write this in my current code,then it should be written outside the loop? – Hafiz M Taha Waseem Aug 11 '18 at 22:21
You don't need loop and reader. That's what I have explained in my code. Remove everything after `sqlCon.Open();` in the using block and just put two lines from the answer after `sqlCon.Open();` – Chetan Aug 11 '18 at 22:27
Brother! It is showing another error... Object cannot be cast from DBNull to an other type – Hafiz M Taha Waseem Aug 11 '18 at 22:28
Updated the answer – Chetan Aug 11 '18 at 22:36
Thanks! It solved my problem. But Kindly guide that by using SQLDataadapter , I could easily store value of a column in a variable by syntax : variable_name = dr["column_Name"]; So,what should I do in this method? – Hafiz M Taha Waseem Aug 11 '18 at 22:40
SqlDataReader is overkill for your case and I would not recommend it. I have updated answer though. But don't use it. – Chetan Aug 11 '18 at 22:59
My whole code is working as desired but now I am facing a problem.It is that the code increamented was displayed as 02 but it displays as 2. It is very important for me to write code as 02,002 etc. – Hafiz M Taha Waseem Aug 12 '18 at 11:46
You need to ask a separate question for that particular problem. – Chetan Aug 12 '18 at 16:07

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

1 Answers1