Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


msflexgrid shows strange numbers when column contains decimal number

Posted on 2005-05-03
Medium Priority
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 ( cn.open "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
  • 4
  • 2
  • 2

Assisted Solution

gary_j earned 150 total points
ID: 13917768
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

ID: 13925199
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

Expert Comment

ID: 13925938
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 33

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 600 total points
ID: 13926224
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

ID: 13932472
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 33

Accepted Solution

Robberbaron (robr) earned 600 total points
ID: 13933604
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)

.... http://www.basenow.com/help/Data_types_in_Microsoft_SQL_Server.asp ....
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

ID: 13934473
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

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

580 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