msflexgrid shows strange numbers when column contains decimal number

Posted on 2005-05-03
Last Modified: 2013-12-25
hi all...I need help with msflexgrid....I am a newbie on visual basic...I was trying to use msflexgrid to show data from sql server 2000.  I don't use ADODC but I use ADODB.

basically I use dsn for the connection ( "myconn"), set the cursortype to aduseclient,
then use cmd to set activeconnection, and create rs for it.  I successfully display the data, but weird number showed on the table. I have 110.25 , 220.85, 325.97 on the column. The result is like this

stockname | weight
gold          | 110.25
silver         | 00000003
copper       | 00000002

the number is formatted as float in the database. can anyone help me? thanks........

Question by:spradjinata
    LVL 5

    Assisted Solution

    When I'm putting data in a flex, I always format it.  I have a padleft function

    function padLeft(strIn as string, intLen as integer) as string
        padLeft = strIn

        Select Case len(padLeft)
            case < intlen
                padLeft = space(intLen - Len(padLeft)) & padLeft
            case > intLen
                padLeft = left(padLeft,intlen)
        End Select
    end function

    Then I format Like this
          strVar = padleft(Format(strVar,"##0.00"),6)

    This gives me a consistent, columnar look

    Author Comment

    thanks for the answer....but it is not what I'm looking for.....the thing is it displays random number modification. After I change the data format to real in SQL server, the data looks like this.  for instance silver is 204.744 and copper is 205.882

    stockname        | weight
    gold  10gr        | 110.25
    silver 10gr        | 204.74399
    copper 10gr      | 205.8819

    and it happens randomly for other

    stockname        | weight
    gold  20gr        | 110.25550
    silver 20gr        | 204.74
    copper 20gr      | 205.88

    I wonder if it ever happens to you.....thanks
    LVL 5

    Expert Comment

    so what are you looking for?
    it sounds like you're looking for rounding, but not getting it.
    I don't believe that the numbers are being reformatted randomly; they are not doubt returned as they are stored.
    give me an example of:
            a number stored in the database (look at it in SQL and tell me exactly how it looks)
            how that number is being displayed in your flexgrid
            how you want that number displayed in your flexgrid
    then i should be able to help you
    LVL 32

    Assisted Solution

    by:Robberbaron (robr)
    I suspect that your view in SQLServer is actually rounded to 3 dec.

    the record set contains the actual number which VB shows unmodified.

    if you formated the out to 3 dec as suggested it would 'look' the same.  


    the numbers in your question look very strange though. I assume the ones in your comment are the real ones.

    post your record display code for some futher analyis if desired.

    Author Comment

    thanks robberbaron and gary for answering me......this problem has been

    bugging me for days.

     I want the number to be displayed as is without further formatting.  I have

    tried with datagrid, and it displays fine. I choose to use flexgrid because

    with datagrid recordset cannot be closed. ( I use this to search inventory

    based on the value of a combo box) If I tried to search for another item,  

    error 3705 occurs....

    so I have this combobox that has value such as "gold earring 10 grams",

    assign the value to a string  (strItemType = combo1.text) and used that for

    the sql command  " select * from tblJewelry where itemType = ' "


    for example the exact record in the SQL would be :
    compname                | Quantity | purity
    gold earring 10 grams   | 250.85   |40
    silver earring10 gram   | 220.876  |70
    copper earring 10 gram  | 100.85   |82
    gold earring 20 grams   | 120.774  |40
    silver earring 20 gram  | 330.855  |70
    copper earring 20 gram  | 402.85   |82

    I enter all the data myself  so I will  know if different number is

    displayed. The data shown in the flexgrid would be: (after the combobox


    compname                | Quantity   | purity
    gold earring 10 grams   | 250.85     |40
    silver earring10 gram   | 220.87599  |70
    copper earring 10 gram  | 100.85     |82
    gold earring 20 grams   | 120.77399  |40
    silver earring 20 gram  | 330.85549  |70
    copper earring 20 gram  | 402.85     |82

    so it displays extra digits on the back of items. It happens to item

    quantity with more than three decimal points.

    here is the complete code.

    Private Sub cmdFindComp_Click()

    Dim strSearch As String
    Dim strKadar As String

    strSearch = Combo1.Text
    strKadar = txtKadar.Text
             ' Open your ADO connection where "murni" is an ODBC DSN that
             ' points to murni database in SQL Server
             cn.CursorLocation = adUseClient
             cn.Open "murni"
             ' Create your command to query inventory name
             With cmd
                .ActiveConnection = cn
                If strKadar <> "" Then
                    .CommandText = "SELECT compname as 'Nama Komponen'," & _
                               " purity as kadar, quantity as jumlah " & _
                               " FROM tblComponentInvtry where compname='" &

    strSearch & "'" & _
                               " AND purity = " & strKadar

                    .CommandText = "SELECT compname as 'Nama Komponen'," & _
                               " purity as kadar, quantity as jumlah " & _
                               "FROM tblComponentInvtry where compname='" &

    strSearch & "'"
                End If
             End With
             'Open your recordset
             With rs
                ' Set rs properties
                .CursorType = adOpenStatic
                .LockType = adLockBatchOptimistic
                ' Call open using active command
                .Open cmd
              End With
             ' data value checking
           ' Do While Not rs.EOF
            ' Print rs.Fields(0)
            ' Print rs.Fields(1)
            ' Print rs.Fields(2)
            ' rs.MoveNext
            ' Populate the flexGrid providing rs as the data source
            Set fgridDispInventory.DataSource = rs

     thank you so much for the help.
    LVL 32

    Accepted Solution

    I now understand why u dont want to format each value.

    what data type are you using in SQLServer?   It appears to be rounding errors when there are more than 6 digits / or more than 3 decimals.  Not currency type ?  money & small money are accurate to 4 decimals only....

    Perhaps you can increase the data type in SQLServer to retain more digits.  ie real , float or decimal (9,4)

    .... ....
    Decimal Data

    Decimal data consists of data that is stored to the least significant digit. Decimal data is stored using decimal or numeric data types in SQL Server. The number of bytes required to store a decimal or numeric value depends on the total number of digits for the data and the number of decimal digits to the right of the decimal point. For example, more bytes are required to store the value 19283.29383 than to store the value 1.1.

    In SQL Server, the numeric data type is equivalent to the decimal data type.
    Monetary Data

    Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead

    Author Comment

    hi robberbaron ...I used real as the datatype.  I did use float on the first time and since float size is 53, this is what happens

    compname                   | Quantity    | purity
    gold earring 10 grams   | 250.85      |40
    silver earring10 gram    | 00000004  |70
    copper earring 10 gram | 00000002  |82

    I believe that it displays (maybe) around 40 decimal points. I really don't know how this could happen because it displays okay with datagrid, but not with flexgrid.

    by the way I should have named the column Weight instead of Quantity, because it is the total weight of  that stock. sorry for the confusion.


    Author Comment

    well....thanks for the effort...I will give out the points. although I haven't  solve the problem...thanks

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now