We help IT Professionals succeed at work.

Format of MySQL result

G F
G F asked
Hi,

I have a small method that pulls the largest number out of a Column that is in a table.  It then displays it in a couple of text boxes.

The code works good, but in testing I want a decimal number (xx.x) but only getting an integer right now.

Here is the complete Method:


using (MySqlCommand cmd = new MySqlCommand("SELECT MAX(TDNumber) FROM bbb.traveldocuments", con1))
            {

                con1.Open();

                object result = cmd.ExecuteScalar();
                result = (result == DBNull.Value) ? null : result;
                decimal blah = Convert.ToDecimal(result);
                MessageBox.Show(blah.ToString());   // something to show me what the value of blah is at this point
                txtBox_TD_MAX_NUMBER.Text = blah.ToString();
               
                con1.Close();

                decimal newtd = blah + 1;
                txtBox_TD_nextTDNumber.Text = newtd.ToString();


            }

It's a little complex since my original code was crashing if the value was null (empty database).  Now I get a zero, which isn't bad, just wondering if it's because my Database is empty and the 0 is actually a null?

The format of the Column is 10.1 decimal unsigned.

Thanks!

G
Comment
Watch Question

View Solution Only

slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

Convert.ToDecimal will return a 0 with a null string.


if 10.1 comes back form the database, it should be 10.1 in the tetbox.  If you want 0.0 displayed, you'll likely need to format it.


Any reason you just don't display what comes back with forced string formatting without calling Convert.ToDecimal ?


https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings

ZvonkoSystems architect
Top Expert 2006

Commented:

Change this:

  
 result = (result == DBNull.Value) ? null : result;

To this:

  
 result = (result == DBNull.Value) ? 0: result;



G FGeneral Manager

Author

Commented:
I guess i'm not sure what the output from the SQL statement is or what exactly the DBNull does.

You mean just use the 'D' Format specifier?

G
G FGeneral Manager

Author

Commented:
Hi Zvonko,  I made the change but the result was the same, just a '0' in the textbox, not a 0.0.

Thank you!

G
Systems architect
Top Expert 2006
Commented:

Check this:


  
  txtBox_TD_nextTDNumber.Text = string.Format("{0:0.0}", newtd );
slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

>> what exactly the DBNull does


The documentation is always your friend:

https://docs.microsoft.com/en-us/dotnet/api/system.dbnull?view=netframework-4.8


The DBNull class represents a nonexistent value. In a database, for example, a column in a row of a table might not contain any data whatsoever. That is, the column is considered to not exist at all instead of merely not having a value. A DBNull object represents the nonexistent column.

Ryan ChongSoftware Team Lead

Commented:
are you doing this in Win Form or Web Form, etc ?
arnold
Distinguished Expert 2017

Commented:
Why not set a default value on the column to 0.00 if not defined as int, then update all hulks to 0.00 then set the column as not null.
G FGeneral Manager

Author

Commented:
Hi!
So, I added the code that Zvonko recommended, and sure enough, it is now saying 1.0 in the textbox!

Ryan, sorry I didn't get a chance to reply to your question, I'm using Win Forms.

arnold, I have defined the Column as decimal, specifically 10,1, when I created the table using HeidiSQL.  Unsigned, which was the default anyways.

slightwv, I'll have a close look at the documentation.  

Hopefully this holds as I start filling the table with test data.

Thanks again everyone!

G
ZvonkoSystems architect
Top Expert 2006

Commented:

Thank you very much for detailed feedback.