Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    4

    Unanswered: Money datatype- simple question!

    Hi there!
    In SQL server 2000, how do you change the number of decimal digits of money datatype field? By default it is 4 digits. I want to have only 2 digits after decimal point. Please help me!

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Instead of using money, you can use decimal with a scale of 2 or cast the money type to decimal with a scale of 2.

  3. #3
    Join Date
    Jun 2002
    Location
    India
    Posts
    4
    Hi there!
    Thanks for your reply. Now that I have changed the money datatype to decimal.

    But the following line of code gives an 'Type Mismatch' error.
    Code:
    Dim Amt
    Amt=rs("Rate")+rs("Interest")  'error in this line 
    Response.write Amt
    'Rate' and 'Interest' are fields of decimal datatype.
    rs - is a ADO recordset.
    Can you tell me why? I have tried with 'Numeric' datatype also, and got the same error.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Since your using VB, why not leave it as money and then use the FORMAT function?

    MyValue = Format(DBValue,"###,###.##")

    OR

    In SQL Server you could use the CAST function on the MONEY field

    try this

    declare @amt money

    set @amt = 0.2
    select CAST(@amt AS VARCHAR)
    MCDBA

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    It looks like you are doing asp with vbscript - it that correct ? I have tested it and it works fine. Do you have vb - if so test it with vb. How are you connecting to the database - post your code leading up to the recordset. Which version of sql server are you using - which version of ado/asp are you using ? Can you just set one of the field values equal to amount ?

  6. #6
    Join Date
    Jun 2002
    Location
    India
    Posts
    4
    Hi rnealejr,
    Thanks for the reply. Yes, you are right, I am using ASP with VBscript.

    Version: SQL server 2000 & ASP3.0[IIS5.0].
    Code:
    Connection String: "Provider=SQLOLEDB;User ID=sa;password=xxx;Initial Catalog=myDb;Data Source=(local);"
    
    set rs=server.createobject("ADODB.Recordset")
    sql="Select * from myTable"
    rs.open sql,con
    if not rs.EOF then
         Amt=rs("Rate")+rs("Interest")  'error in this line 
    end if
    Can you just set one of the field values equal to amount ?
    What do you mean by this? I didn't get you.

    More Info: I am looking for a solution that is related with SQL server, rather than ASP. [because if the solution is ASP related, then I may have to change a lot of forms - around 150]

    Please help me

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    I will look into your code. Can you do the following:

    Amt = rs("Rate")

    and/or

    Amt = rs("Interest")

  8. #8
    Join Date
    Jun 2002
    Location
    India
    Posts
    4
    Hi,
    Thanks again for your reply!
    When I used Amt = rs("Rate"), it assigns the value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •