Link to home
Start Free TrialLog in
Avatar of G F
G FFlag for Canada

asked on

Issue with reading empty SQL table or nonexistent values

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
Avatar of Chinmay Patel
Chinmay Patel
Flag of India image

Hi There,


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

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

Open in new window


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.

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 -User generated image-saige-

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.

Avatar of G F

ASKER

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,
Avatar of G F

ASKER

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
            {
ASKER CERTIFIED SOLUTION
Avatar of G F
G F
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial