Replace comma to dot when SQL is loaded.

Hi Experts.

I use aspgrid from
Object reference below:

I will post the code below my explanation.

I have problems with commas and dots.
When I store a value using a dot in a field
in the aspgrid and then save the record is
turned into a comma.


12.1 become 12,1

The datatype for the table field is float.

The real proble is if I click on the edit button
and want to change a value in a record I must
cahnge all commas to dots again.

If I should ignore my validation code and store the value
in the form as 12,1 it turns to 121
Therefore I must use a dot before my decimals.

So the solution must be to display replace the commas to dots when pressing on the edit button.

I have tried several solutions but none fo them works.

Really need some expert help here.


<TITLE>AspGrid 3.0 Demo - Expense Report</TITLE>

function Validate()
      if( isNaN(document.AspGridFormSave1.FIELD5.value ) )
            alert('Not a number.');
            return false;
      if( isNaN(document.AspGridFormSave1.FIELD6.value ) )
            alert('Not a number.');
            return false;
      if( isNaN(document.AspGridFormSave1.FIELD7.value ) )
            alert('Not a number.');
            return false;
      if( isNaN(document.AspGridFormSave1.FIELD8.value ) )
            alert('Not a number.');
            return false;
      if( document.AspGridFormSave1.FIELD5.value == "")
            alert('Field is empty.');
            return false;
      if( document.AspGridFormSave1.FIELD6.value == "")
            alert('Field is empty.');
            return false;
      if( document.AspGridFormSave1.FIELD7.value == "")
            alert('Field is empty.');
            return false;
      if( document.AspGridFormSave1.FIELD8.value == "")
            alert('Field is empty.');
            return false;
      return true;

<body bgcolor="#FFFFFF">
<p><b>Op-register v0.0.1</b><br>
      ' Create an instance of AspGrid
      Set Grid = Server.CreateObject("Persits.Grid")
      ' Specify location of button images
      Grid.Imagepath = "aspgrid/images/"
      ' Specify SQL statement. We store session IDs to allow simultaneos users to run the demo transparently
      Grid.SQL = "select id, sessionid, Productid, OpLocationid, InternalTime, InternalYield, CustomerTime, CustomerYield, EditedDate from OpRegister WHERE Productid= '"&Cstr(session("sessProductid"))&"' ORDER BY id"

      ' Connect using a DSNless connection string
      ConnectStr = "Provider=SQLOLEDB;Server=madeleine;Database=Operationsregister;UID=sa;PWD="
      Grid.Connect ConnectStr, "", ""
      ' Optional: specify a client-side JavaScript validation routine
      Grid.FormOnSubmit = "return Validate();"

      ' Use POST rather than GET
      Grid.MethodGet = False
      ' specify <TABLE> attributes of the grid
      Grid.Table.CellSpacing = 0
      Grid.Table.CellPadding = 0
      Grid.Table.Width = 600

      ' Hide the ID and SESSIONID columns
      Grid.ColRange(1, 3).Hidden = True

      ' specify the default value for the hidden SESSIONID column
      Grid.Cols("sessionid").DefaultValue = Session.SessionID
      Grid.Cols("Productid").DefaultValue = Cstr(session("sessProductid"))
      Response.write "Product:<font color=""red""><b>" & Cstr(session("sessProductid")) & "</b></font><br>"
      ' Enable control buttons at both sides of grid

      ' Set header captions      
      Grid("OpLocationid").Caption = "Location"
      Grid("InternalTime").Caption = "Tid1"
      Grid("InternalYield").Caption = "Yield1"
      Grid("CustomerTime").Caption = "Tid2"
      Grid("CustomerYield").Caption = "Yield2"
      Grid("EditedDate").Caption = "Date"

      ' Set column width
      Grid.Cols(0).Header.Width = 35
      Grid.Cols(0).Cell.Width = 35
      Grid.Cols(0).Cell.NoWrap = True

      ' Columns 1, 2 and 3 (id, sessionid and Productid) are hidden
      Grid.Cols(4).Cell.Width = 10
      Grid.Cols(4).Header.Width = 10

      Grid.Cols(5).Cell.Width = 8
      Grid.Cols(5).Header.Width = 8

      Grid.Cols(6).Cell.Width = 10
      Grid.Cols(6).Header.Width = 10

      Grid.Cols(7).Cell.Width = 8
      Grid.Cols(7).Header.Width = 8
      Grid.Cols(8).Cell.Width = 10
      Grid.Cols(8).Header.Width = 10
      Grid.Cols(9).Cell.Width = 50
      Grid.Cols(9).Header.Width = 50      
      Grid.Cols(999).Cell.Width = 35
      Grid.Cols(999).Header.Width = 35
      Grid.Cols(999).Cell.NoWrap = True
      ' Set fonts, colors for the control columns
      Grid.Cols(0).Header.BgColor = "#90F0FE"
      Grid.Cols(0).Cell.Align = "CENTER"
      Grid.Cols(0).Cell.BgColor = "#FFFFFF"
      Grid.Cols(0).Cell.AltBgColor = "#E0E0E0"
      Grid.Cols(0).Footer.Align = "CENTER"
      Grid.Cols(999).Header.BgColor = "#90F0FE"
      Grid.Cols(999).Cell.Align = "CENTER"      
      Grid.Cols(999).Cell.BgColor = "#FFFFFF"
      Grid.Cols(999).Cell.AltBgColor = "#E0E0E0"      
      Grid.Cols(999).Footer.Align = "CENTER"

      ' Set fonts, colors and sizes for the data columns
      Grid.ColRange(2, 9).Header.Font.Face = "Arial"
      Grid.ColRange(2, 9).Header.Font.Size = 2
      Grid.ColRange(2, 9).Header.BgColor = "#A6CAF0"
      Grid.ColRange(2, 9).Header.NoWrap = True
      Grid.ColRange(2, 9).Cell.Font.Face = "Arial"
      Grid.ColRange(2, 9).Cell.Font.Size = 2
      Grid.ColRange(2, 9).Cell.BgColor = "#FFFFFF"
      Grid.ColRange(2, 9).Cell.AltBgColor = "#E0E0E0"

      ' Grid.Cols(<index>) is equivalent to Grid(<index>) as Cols is the default property
      ' Set the SIZE attribute for <INPUT TYPE=TEXT>      '
      Grid("OpLocationid").InputSize = 6
      Grid("InternalTime").InputSize = 6
      Grid("InternalYield").InputSize = 6
    Grid("CustomerTime").InputSize = 6
    Grid("CustomerYield").InputSize = 6
    Grid("EditedDate").InputSize = 10

      ' Display Category field as a drop-down list
      Grid("OpLocationid").AttachForeignTable "select OpLocationId, OpLocationName from OpLocation Where QuerySelection='"&Cstr(session("sessScheme"))&"' ", 1, 2
      ' Format the Date column      
    Grid("EditedDate").FormatDate "%d %b %Y", "%d %b %Y"
      Grid("EditedDate").DefaultValue = Date
      Grid("EditedDate").Cell.NoWrap = True
      ' Enable sorting on all visible fields
      Grid.ColRange(2, 9).CanSort = True

      ' Enable client-side confirmation on record deletion
      Grid.DeleteButtonOnClick = "Are you sure you want to delete this record? (This message is cusomizable)"

      Grid.FormOnSubmit = "return Validate();"

      ' Peform server-side validation of date
      If Request("AspGridSave1.x") <> "" Then ' Adding or saving in progress
            If Not IsDate(Request("FIELD9")) Then
                  Response.Write "<B><FONT SIZE=2 FACE=Arial COLOR=#FF0000>Server-side validation: this is not a date!</FONT></B><P>"
                  Grid.IgnoreCommands ' do not save!
            Else ' verify date range
                  TheDate = CDate(Request("FIELD9"))
                  If TheDate < CDate("1/1/1995") Then
                        Response.Write "<B><FONT SIZE=2 FACE=Arial COLOR=#FF0000>Server-side validation: A date must be after 1/1/1995!</FONT></B><P>"
                        Grid.IgnoreCommands ' do not save!
                  End If
            End If
      End If

      ' Display a total by requerying the same recordset
      Response.Write "<P>"
      Set Rec = grid.Recordset
      InternalTime = 0
      InternalYield = 0
      while Not Rec.EOF
            InternalTime = InternalTime + Rec("InternalTime")
            InternalYield = InternalYield + Rec("InternalYield")
            CustomerTime = CustomerTime + Rec("CustomerTime")
            CustomerYield = CustomerYield + Rec("CustomerYield")

      Response.Write "<FONT SIZE=3><B>Internal time: " & InternalTime & "</B></FONT><br>"
    Response.Write "<FONT SIZE=3><B>Internal yield: " & InternalYield & "</B></FONT><br>"
    Response.Write "<FONT SIZE=3><B>Customer time: " & CustomerTime & "</B></FONT><br>"
    Response.Write "<FONT SIZE=3><B>Customer yield: " & CustomerYield & "</B></FONT>"
      Set Grid = Nothing


Who is Participating?
iwebdevConnect With a Mentor Commented:
Column Object Method:

If you enter a number greater than 12.1 like 123456789 does it look like 1.234.567,89 ?

See FloatingPoint Method below.

Sub FormatNumeric(Decimal As Integer, Optional ThouComma = True, Optional NegativeSign = True, Optional FloatingPoint = True, Optional Prefix = "" )

Specifies numeric formatting for a column.
Decimal specifies the number of decimal positions for a number.
ThouComma is optional and True by default. If set to False, suppresses comma separators between 000's.
NegativeSign is optional and True by default. If set to False, suppresses the "-" sign for negative numbers and uses the parenthesized (...) form instead.
FloatingPoint is optional and True by default. If set to False, uses the European format (floating commas and "dot" separators), e.g. 1.234.567,89.
Prefix is optional and empty by default. Specifies a string prefix that will be automatically inserted in front of each number in the column.

sounds to me like a regional settings issue.....

but here's some code to change commas into dots

strVar = Replace(strVar,",",".")

so it'd probably be something like this

strVar = RSobj(FieldName)
strVar = Replace(strVar,",",".")
Response.Write strVar

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.