Link to home
Start Free TrialLog in
Avatar of G F
G FFlag 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
Avatar of slightwv (䄆 Netminder)
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

Change this:

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

Open in new window

To this:

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

Open in new window



Avatar of 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
Avatar of 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
Avatar of Zvonko
Zvonko
Flag of North Macedonia 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

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

are you doing this in Win Form or Web Form, etc ?
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.
Avatar of 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

Thank you very much for detailed feedback.