Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: DB storing integers not decimals

    I've been trying for days to do this and have seen about 100 posts and tutorials on the subject, but I'm still getting errors

    I'm using this ASP:
    com.parameters.append (com.CreateParameter("@cost", adDecimal, adParamInput, 9, upload.form("cost")))
    com.Parameters("@cost").Precision = 18
    com.Parameters("@cost").NumericScale = 2

    to talk to this stored proc:
    @cost Decimal(18,2),

    and the table column values of:
    decimal, length=9, precision=18, scale=2

    The DB is storing the values as integers. so 1 becomes 100 and 1.2 becomes 120 etc.

    edit: BTW, the extra two digits is coming from a form validating function - formatNumber(quantity,2,0,0,0) But it's the removal of the decimal point in the DB which is my problem.
    Last edited by darkmunk; 11-02-11 at 09:37.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run a trace on the SQL Server to see what is being passed as the actual parameter.

  3. #3
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Thanks MCrowley,
    I'm not able to access this DB from any kind of console, only an online admin with a query window.
    Is there a script-based way to run a trace?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You would need sysadmin rights on the server, or possibly view server state. If this is a hosted database, you may be out of luck. You will need to adjust the path to the output file:
    Code:
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    declare @stop datetime
    set @maxfilesize = 50
    set @stop = dateadd (mi, 10, getdate())
    declare @outputfile nvarchar(255)
    set @outputfile = N'D:\MSSQL9\MSSQL.1\MSSQL\LOG\trace.trc'
    
    exec @rc = sp_trace_create @TraceID output, 2, @outputfile, @maxfilesize, @stop 
    if (@rc != 0) goto error
    
    -- Set the events
    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 10, 1, @on
    exec sp_trace_setevent @TraceID, 10, 3, @on
    exec sp_trace_setevent @TraceID, 10, 6, @on
    exec sp_trace_setevent @TraceID, 10, 8, @on
    exec sp_trace_setevent @TraceID, 10, 10, @on
    exec sp_trace_setevent @TraceID, 10, 11, @on
    exec sp_trace_setevent @TraceID, 10, 12, @on
    exec sp_trace_setevent @TraceID, 10, 13, @on
    exec sp_trace_setevent @TraceID, 10, 14, @on
    exec sp_trace_setevent @TraceID, 10, 15, @on
    exec sp_trace_setevent @TraceID, 10, 16, @on
    exec sp_trace_setevent @TraceID, 10, 17, @on
    exec sp_trace_setevent @TraceID, 10, 18, @on
    exec sp_trace_setevent @TraceID, 12, 1, @on
    exec sp_trace_setevent @TraceID, 12, 3, @on
    exec sp_trace_setevent @TraceID, 12, 6, @on
    exec sp_trace_setevent @TraceID, 12, 8, @on
    exec sp_trace_setevent @TraceID, 12, 10, @on
    exec sp_trace_setevent @TraceID, 12, 11, @on
    exec sp_trace_setevent @TraceID, 12, 12, @on
    exec sp_trace_setevent @TraceID, 12, 13, @on
    exec sp_trace_setevent @TraceID, 12, 14, @on
    exec sp_trace_setevent @TraceID, 12, 15, @on
    exec sp_trace_setevent @TraceID, 12, 16, @on
    exec sp_trace_setevent @TraceID, 12, 17, @on
    exec sp_trace_setevent @TraceID, 12, 18, @on
    
    -- Set the Filters
    declare @intfilter int
    declare @bigintfilter bigint
    
    -- Set the trace status to start
    exec sp_trace_setstatus @TraceID, 1
    To read the trace back into a table in SQL Server:
    Code:
    select 
    row_number() over (order by startTime) as RowNumber,
    TextData,
    DatabaseID  ,
    HostName  ,
    ApplicationName ,
    LoginName        ,
    SPID        ,
    Duration     ,        
    StartTime     ,                                         
    EndTime        ,                                        
    Reads           ,     
    Writes           ,    
    CPU   ,
    EventClass  ,
    ProviderName ,
    EventSequence into tracetable
    from ::fn_trace_gettable('D:\MSSQL9\MSSQL.1\MSSQL\Log\trace.trc', default)

  5. #5
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Wow! thanks for that.
    I don't think I can run that in it's present environment. It may come in useful when I move the whole shebang elsewhere tho'.

    Is is possible to capture the output of the parameters at ASP level?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    response.write com.Parameters("@cost").Value

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Excellent! thanks, that cured my attempt to do the same
    The result of submitting 1111.222 was 1111222 - so it's getting turned into an integer before the DB.
    Any ideas?

  8. #8
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Done some tests and it is definitely the parameter code that is creating the integers.
    Have I missed something out?
    Have I added the 'precision' and 'scale' bits properly, or do I need to append this to the @cost parameter?
    com.parameters.append (com.CreateParameter("@cost", adDecimal, adParamInput, 9, upload.form("cost")))
    com.Parameters("@cost").Precision = 18
    com.Parameters("@cost").NumericScale = 2

    response.write(com.Parameters("@cost").Value
    Or maybe the value should be assigned last, after defining the precision etc?

  9. #9
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Fixed!

    This has confused me even more, but while testing the output I tried adding 2 to the parameter value (just to identify my change) and it suddenly showed the decimal correctly!

    So I tried adding 0 and that also fixed it ??

    So the ASP Parameter code looks like this (note the '+0' at the end):
    Code:
    com.parameters.append (com.CreateParameter("@cost", adDecimal, adParamInput, 9, upload.form("cost")+0))
    			com.Parameters("@cost").Precision = 18
    			com.Parameters("@cost").NumericScale = 2

Posting Permissions

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