Sqldatareader problem

[AngelicOne]

SqlCommand cmd = new SqlCommand("SELECT MAX(Register_ID) AS Register_ID FROM EnrollTbl", con);
        SqlDataReader reader;
        con.Open();
        reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                RegisterID.Text = (Convert.ToInt32(reader["Register_ID"]) + 1).ToString();
            }
        }
        else
        {
            RegisterID.Text = "10000";
        }
        reader.Close();

It says Object cannot be cast from DBNull to other types. I believe i'm not doing the sqldatareader right. could someone correct my code?

[MeSampath]

Hi,

When no data available in the table ("EnrollTbl"), it will return null value for the MAX(Register_ID) query and while trying to convert the null value into Int32 it throws the error. So change the query as follows;

SqlCommand cmd = new SqlCommand("SELECT [B]Isnull(MAX(Register_ID),9999)[/B] AS Register_ID FROM EnrollTbl", con);

By adding the null checking, if the MAX is null it will return the given value.

Thank you.

Good luck.

[alobal]

this is wrong:

RegisterID.Text = (Convert.ToInt32(reader["Register_ID"]) + 1).ToString();

use:

RegisterID.Text = ((Convert.IsDbNull(reader["Register_ID"]))?0:Convert.ToInt32(reader["Register_ID"])).ToString();

[AngelicOne]

Then what is the use of reader.hasrows?

I thought it's conditional statement that when there is row returned then the reader.read() will execute, else i'll do something..

Correct me if i'm wrong..

[Geekitygeek]

You are correct; reader.hasrows DOES check if the datareader has retrieved any rows, and if you had used "SELECT * FROM EnrollTbl" and the table was empty then reader.HasRows would have returned false.
However, you used an aggregate function (MAX). These ALWAYS return a value. If there is no data to process then the return value is null (or zero in the case of the COUNT function).

Checking reader.hasrows in this instance is redundant since it will ALWAYS have a row.

Either amend your SQL query to replace a NULL with a value (as MeSampath showed you), although i would probably replace null with 0 or -1 rather than 9999 if you are intending on using the "next available" ID.
The other option is to change your code to handle a null return value; alobal's code uses an inline if statement which will set the text to zero if the return value is DBNull or the converted return value if it isn't DBNull.

[AngelicOne]

This is now my code. What you are saying is hasrows is unnecessary? Then should I omit it?

if (reader.HasRows)
        {
            while (reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                    RegisterID.Text = (Convert.ToInt32(reader["Register_ID"]) + 1).ToString();
                }
                else
                {
                    RegisterID.Text = "10000";
                }
            }
        }
        reader.

[Geekitygeek]

I would leave it in personally. In hindsight I should qualify my previous post by saying "under normal circumstances, it will ALWAYS have a row". The query itself will always return a value, but there may be some exceptional situation where the query does not run fully/correctly and reader is left empty. In this instance a call to reader.Read() would throw an exception. You could add an else after the if(reader.HasRows) block that informs the user the read failed.

[nick.crane]

You can also use the ExecuteScalar() command instead of ExecuteReader() .
This does the reading for you and extracts the value (value can still be DBNull though).

To get aroung the DBNull issue it is possible to use a nullable type. MSDN Nullable Types.
E.G.

int? value = cmd.ExecuteScalar() as int?;
label2.Text = value.HasValue ? value.Value.ToString() : "DBNull";

[Edit] Just read a bit more on nullable types and I noticed the ?? operator.
This allows you to do something like this.

label2.Text = ((int)(value ?? 10000)).ToString();

Personally, I think the first option is more understandable from the point of view of later maintenance. But the ?? format might have its uses.

[AngelicOne]

To clarify things, I only want this for the first entry of record.. I have a registration form that if it is the first time of entry, which means the database has yet no records, the registration no. will be 10000..

[Geekitygeek]

why not make it an identity field in the database and seed it from 10000. That way every time you insert a new record it will automatically get assigned the next number in sequence and avoid you having to do this :)

[AngelicOne]

if i make a first entry in my database for register id, it might get deleted by someone..

[Geekitygeek]

I'm not sure i follow :/ Do other users have access to the database to delete a record, or do you mean you need to check if a register id already exists so your program doesnt overright it? If its the later, then an identity field is ideal, identities are never reused. If you add your first user it will have register id 10000, then you add second user who automatically gets given register id 10001. If you deleted the first user then added a third, the third would be given register id 10002 and id 10000 would no longer exist.

[AngelicOne]

A user can delete a record.. I need the registration ID for MAX function, to increment the id.. The registration ID is entered automatically in a textbox where user cannot edit it.

[AngelicOne]

Yesterday I read about declaring the value type of a column as IDENTITY.. Which is much better? Using SQL Data Reader with max function or the use of identity in sql itself?

[Geekitygeek]

why not make it an identity field in the database and seed it from 10000. That way every time you insert a new record it will automatically get assigned the next number in sequence and avoid you having to do this :)

Thats what i've been telling you to use :p If a user deletes a record, would you want to 'reuse' the ID? I wouldnt...generally i would have a "Deleted" field, depending on what your database is for.

Lets for a moment imagine you have a database to store users and orders for an ecommerce application. If a user deletes their account, you no longer want the login to work or allow them to access their account details; But would you want to delete all records of their previous orders? By having a Deleted field you can check it to prevent access to deleted records, but the user record still exists to maintain referencial integrity for your old orders.

An Identity field will not reuse old values after they are deleted. It is essentially a counter which you start (or seed) at an initial value and is incremented automatically each time a record is created. It will automate the whole process of assigning a Register_ID and ensure there are no duplicates. Why re-invent the wheel :p