Avatar of G F
G F
Flag for Canada asked on

Format of MySQL result

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
DatabasesC#MySQL Server

Avatar of undefined
Last Comment
Zvonko

8/22/2022 - Mon
slightwv (䄆 Netminder)

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

Zvonko

Change this:

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

Open in new window

To this:

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

Open in new window



G F

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
G F

ASKER
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
ASKER CERTIFIED SOLUTION
Zvonko

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

>> 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 Chong

are you doing this in Win Form or Web Form, etc ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

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 F

ASKER
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
Zvonko

Thank you very much for detailed feedback.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy