We help IT Professionals succeed at work.

Issue with reading empty SQL table or nonexistent values

G F
G F asked
on
High Priority
71 Views
Last Modified: 2020-03-02
Hi,
So I need to pull a few values out of one row in a table, but the code I have skips a crucial part of the program (where the values are added together with other variables) if the table is empty.
Here is the code I'm working with:


 string newIDtemp;

            newIDtemp = txtBox_TD_Current_ID.Text;

            string constring = "datasource=localhost;port=3306;username=root;pwd=;";

            string Query = "SELECT * FROM bbb.approvaltotal WHERE TDId= '" + newIDtemp + "' ;";

            MySqlConnection conDataBase3 = new MySqlConnection(constring);
            MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase3);
            MySqlDataReader myReader;


            try
            {

                MessageBox.Show(tdapprovalcurrentjurfee.ToString());
                MessageBox.Show(tdapprovalcurrentpnfee.ToString());
                MessageBox.Show(tdapprovalcurrenttotal.ToString());



                conDataBase3.Open();
                myReader = cmdDataBase.ExecuteReader();

                while (myReader.Read())
                {
                   


                    string approvaltotalid = myReader.GetString("Id");
                    string approvaltotaltdid = myReader.GetString("TDId");
                    string approvalpermitnowtotal = myReader.GetString("PermitNowTotal");
                    string approvaljurisdictiontotal = myReader.GetString("JurisdictionTotal");


.
.
.
.

Basically, if the table returns Null, the While(myReader.Read()) does not execute.

I just need to pull the values out of the table, add some decimals to them, and then put them back in to the same spots.  Like an update but with addition.

Is there a better way than using a try-while statement?  The reason i'm using it is because its working for other parts of my program, but wasn't aware the while skips if the reader is empty.

I've read on other sites that it's better to use Execute Scalar, if it's just one row, but I'm having a hard time finding a good example that fits my situation.

Any help would be appreciated!

G
Comment
Watch Question

Chinmay PatelChief Technology Ninja
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

Hi There,


To handle null values we used this trick a LOT in past.

                     string approvalpermitnowtotal = myReader.GetString("PermitNowTotal") as string;


I also need to see your Catch statement, are you properly handling the exception.


If you read the GetString method's documentation https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.getstring?view=netframework-4.8, it mentions


No conversions are performed; therefore, the data retrieved must already be a string.

Call IsDBNull to check for null values before calling this method.

You will have to use IsDBNull, as shown here: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.isdbnull?view=netframework-4.8


Regards,

Chinmay.

it_saigeDeveloper
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
When reader.Read returns false, it means there were no rows read from the table based upon your query criteria.  If that is the case, you would need to insert new row(s) with the values that you want to set.

Chinmay is correct that when you get rows back, you will want to ensure that their field(s) do not contain DBNull (which is different from null).  To that end, I will usually use a few extension methods; e.g. -
static class Extensions
{
        public static T GetValue<T>(this object source, T defaultValue)
        {
            if (ReferenceEquals(source, null))
            {
                return defaultValue;
            }

            return !Equals(DBNull.Value, source) ? (T)Convert.ChangeType(source, typeof(T)) : defaultValue;
        }

        public static T GetValue<T>(this object source)
        {
            return source.GetValue(default(T));
        }
}

Open in new window

Proof of concept -
using System;

namespace EE_Q29174312
{
    class Program
    {
        static void Main(string[] args)
        {
            object number = DBNull.Value;
            object text = DBNull.Value;

            var values = new object[] { number.GetValue<int>(), text.GetValue<string>("") };

            foreach (var value in values)
            {
                Console.WriteLine($"{value} is type of {value.GetType()}");
            }
            Console.ReadLine();
        }
    }

    static class Extensions
    {
        public static T GetValue<T>(this object source, T defaultValue)
        {
            if (ReferenceEquals(source, null))
            {
                return defaultValue;
            }

            return !Equals(DBNull.Value, source) ? (T)Convert.ChangeType(source, typeof(T)) : defaultValue;
        }

        public static T GetValue<T>(this object source)
        {
            return source.GetValue(default(T));
        }
    }
}

Open in new window

Produces the following output -Capture.PNG-saige-
Chinmay PatelChief Technology Ninja
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

I am sorry, it_saige is right. I mis-read the original question, I thought you are getting some errors due to a null value in the GetString method. Your code will not execute if your table does not have any data.

G FGeneral Manager

Author

Commented:
Hi, saige, I'll be honest, I didn't know how to adapt your code to my project, so I'm attempting a re write, but crashes on the line:

string ow = reader.GetString(0);

with the error:  MySql.Data.MySqlClient.MySqlException: 'Invalid attempt to access a field before calling Read()'


Here is the code i'm working with:


 MySqlConnection cn = new MySqlConnection("datasource=localhost;port=3306;username=root;pwd;");
           
            cn.Open();
            MySqlCommand com = cn.CreateCommand();

            com.CommandType = System.Data.CommandType.Text;
            com.CommandText = "SELECT * FROM bds.approvaltotal WHERE TDId= '" + newIDtemp + "' ;";
            MySql.Data.MySqlClient.MySqlDataReader reader = com.ExecuteReader();


                                                                   
         

            if (reader.HasRows)
            {
 

               

                string ow = reader.GetString(0);
                string ow1 = reader[1].ToString();
                string ow2 = reader[2].ToString();
                string ow3 = reader[3].ToString();
                string ow4 = reader[4].ToString();

                MessageBox.Show(ow);
                MessageBox.Show(ow1);
                MessageBox.Show(ow2);
                MessageBox.Show(ow3);
                MessageBox.Show(ow4);

               
             
                MessageBox.Show("Approval total read successfully Has Rows");

                reader.Close();
            }
            else
            {
                MessageBox.Show("Table empty!  Inserting new record");

                MySqlCommand cmd = new MySqlCommand("INSERT INTO bds.approvaltotal (TDId,PermitNowTotal,JurisdictionTotal,ApprovalTotal)" + "VALUES(@TDId,@PermitNowTotal,@JurisdictionTotal,@ApprovalTotal); ", con);
                con.Open();


                cmd.Parameters.AddWithValue("@TDId", txtBox_TD_Current_ID.Text);
                cmd.Parameters.AddWithValue("@PermitNowTotal", tdapprovalcurrentpnfee);
                cmd.Parameters.AddWithValue("@JurisdictionTotal", tdapprovalcurrentjurfee);
                cmd.Parameters.AddWithValue("@ApprovalTotal", tdapprovalcurrenttotal);

               
                cmd.ExecuteNonQuery();
                con.Close();
               
            }
            cn.Close();



So, it identifies when there is no data in the table, which is good, but all I want right now, is to pull the data it finds and display in a message box. i just want to get that working.

So far, no luck..

Thanks,
G FGeneral Manager

Author

Commented:
Ok, so added an extra If statement on the reader...so far seems to work....


if (reader.HasRows)

            {

                if (reader.Read())

                {
                   




                    string ow = reader.GetString(0);
                    string ow1 = reader[1].ToString();
                    string ow2 = reader[2].ToString();
                    string ow3 = reader[3].ToString();
                    string ow4 = reader[4].ToString();

                    MessageBox.Show(ow);
                    MessageBox.Show(ow1);
                    MessageBox.Show(ow2);
                    MessageBox.Show(ow3);
                    MessageBox.Show(ow4);



                    MessageBox.Show("Approval total read successfully Has Rows");

                    reader.Close();
                }
            }
            else
            {
General Manager
Commented:
So this worked after much testing....


            reader.Read();
           

                if (reader.HasRows)

                        {

                        string ow2 = reader[2].ToString();
                         .
                         .

                         }

               else


                {

                   
                    MessageBox.Show("Table empty!  Inserting new record");

                    MySqlCommand cmd = new MySqlCommand("INSERT INTO bbb.approvaltotal (TDId,PermitTotal,JurisdictionTotal,ApprovalTotal)"
                     + "VALUES(@TDId,@PermitTotal,@JurisdictionTotal,@ApprovalTotal); ", con);

                    con.Open();
                     .
                     .

                }



Thanks!