Professional Web Applications Themes

MS Access - problem with Double type field - ASP Database

Apologies for cross posting - I think this is a more appropriate newsgroup than the general one I originally posted in. ********************* Hi - I've got a really strange problem I can't explain - when I add new values into my database (Access) it multiplies them by 100 - for example if I enter on my form the values: 10.50 7.50 5.50 They go into the database as: 1050 750 550 The field in Access is a Double type, nullable, and set to default to 0. My code to add it is: (the response.writes are what product the numbered output ...

  1. #1

    Default MS Access - problem with Double type field

    Apologies for cross posting - I think this is a more appropriate
    newsgroup than the general one I originally posted in.

    *********************

    Hi - I've got a really strange problem I can't explain - when I add new
    values into my database (Access) it multiplies them by 100 - for example
    if I enter on my form the values:

    10.50
    7.50
    5.50

    They go into the database as:

    1050
    750
    550

    The field in Access is a Double type, nullable, and set to default to 0.

    My code to add it is:
    (the response.writes are what product the numbered output above - so I
    know what the form is posting, and subsequently what the database has in
    its fields)

    if Request("selected") <> "" then
    response.write(Request("RetailPrice"))
    response.write("<br>")
    response.write(Request("Price"))
    response.write("<br>")
    response.write(Request("CostPrice"))
    response.write("<br>")
    ProductsRS.AddNew
    ProductsRS("Product")=Request("Product")
    ProductsRS("Description")= Request("Description")
    ProductsRS("StockNumber")=Request("StockNumber")
    ProductsRS("CategoryID")= Request("CategoryID")
    ProductsRS("RetailPrice")=Request("RetailPrice")
    ProductsRS("Price")=Request("Price")
    ProductsRS("CostPrice")=Request("CostPrice")
    ProductsRS.Update
    ProductsRS.Requery
    ProductID=ProductsRS("ProductID")
    response.write(ProductsRS("RetailPrice"))
    response.write("<br>")
    response.write(ProductsRS("Price"))
    response.write("<br>")
    response.write(ProductsRS("CostPrice"))
    response.write("<br>")

    Can anyone please try and help me with this.

    Kind regards, Mark


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark Guest

  2. #2

    Default Re: MS Access - problem with Double type field

    Mark wrote: 

    This is a multipost, not a crosspost. There is a difference. Crossposting:
    sometimes OK. Multiposting: never ok.
     

    Don't use recordsets to modify data. My suggestion would be to use a saved
    parameter query. Open your database in Access, go to the Queries tab, and
    create a new query in Design View (there should be a button on the database
    window for this, depending on what version of Access you are using). Close
    the Choose Table dialog without selecting a table, and immediately switch to
    SQL View (toolbar button, menu option, or right-click context menu). Type
    this into the window (assuming the table's name is "Products"):

    INSERT INTO Products (Product, Description,StockNumber,
    CategoryID,RetailPrice,Price,CostPrice)
    VALUES ([pProduct], [pDescription],[pStockNumber],
    [pCategoryID],[pRetailPrice],[pPrice],[pCostPrice])


    If you test it by clicking the Execute button in the toolbar, you will be
    prompted for each parameter value. Enter some test values and then look at
    the data in the table to verify that the data was entered correctly. If so,
    save the query as "qInsProduct", and you are now ready to execute it from
    asp:


    dim cn, rs, sSQL, newID
    cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=p:\ath\to\database.mdb"

    'I would assign the values from the Request object to variables and
    'validate them at this point, however, just to save space and time,
    'I will illustrate using the request variables directly - YOU SHOULD
    'NOT DO IT THIS WAY - validate your user's input.
    'In addition, you should not make the runtime engine search
    'all the Request collections for your variables. Always specify
    'which collection contains the variable. IE, request.form("var")
    ' instead of reques("var"). Since I did not know whether you
    'were passing by POST or GET, I will not specify the collection
    'in this example. But you should!

    cn.qInsProduct Request("Product"), _
    Request("Description"), Request("StockNumber"), _
    Request("CategoryID"), Request("RetailPrice"), _
    Request("RetailPrice"), Request("Price"), Request("CostPrice")

    sSQL = "Select IDENTITY"
    Set rs = cn.Execute(sSQL,,1)
    newID=rs(0)

    sSQL="Select Product, Description,StockNumber, " & _
    "CategoryID,RetailPrice,Price,CostPrice " & _
    "FROM Products WHERE ProductID = " & newID

    Set rs=cn.Execute(sSQL,,1)
    'etc.

    If the incorrect values are still being displayed, verify that they were
    stored that way by opening the database in Access and looking at the data.
    If they were stored incorrectly, then there is probably a Regional Settings
    issue that should have been taken care of by passing the values by
    parameter. Let us know if this is the case.

    If the correct values are in the database, then there is a display issue,
    probably being caused by Regional Settings, again let us know.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  3. #3

    Default Re: MS Access - problem with Double type field

    Hi Bob - thank you - the code works well.

    At least it works fine locally - but still not on the host.

    They are 1and1 and I did have some trouble with regional settings on
    their sql server when I was doing something on that (they were
    displaying a comma instead of a decimal point).

    I think they're German, so the regional settings suggestion sounds like
    it could be the issue.

    I have tried setting <% session.lcid=2057 %> at the top of my script,
    but it is still going in to the database seemingly just taking out the
    decimal point. Are there any other settings I can use to ensure they
    are enforced, and not being overwritten by 1and1s IIS setup?

    Thanks again, Mark



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark Guest

  4. #4

    Default Re: MS Access - problem with Double type field

    Mark wrote: 

    I have little experience with internationalization issues. I was under the
    mistaken impression that passing the values by parameter would solve those
    issues. You might try using the CDbl function when passing the values to see
    if that helps:

    cn.qInsProduct ...,CDbl(Request("...")), ...

    If that does not do it, then we may need to resort to dynamic sql, replacing
    the periods with commas.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  5. #5

    Default Re: MS Access - problem with Double type field

    Try this:

    oldLCID = session.lcid

    session.lcid=2057

    nPrice = CDbl(Request("Price"))
    nCostPrice = CDbl(Request("CostPrice"))
    ..
    ..
    ..

    session.lcid = oldLCID

    ProductsRS("Price")= nPrice
    ProductsRS("CostPrice")= nCostPrice
    ..
    ..
    ..

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "Mark" <com> wrote in message
    news:phx.gbl... 


    Mark Guest

  6. #6

    Default Re: MS Access - problem with Double type field

    Hi - thanks for the help.

    This has now been resolved - although I'm not really sure which part was
    the key - but I now have a far more efficient 'query' accepting the data
    - The CDbl did seem to help - although setting the lcid within the page
    did not - I added a global.asa file, which set the lcid to my locale
    (2057), at my hosts instruction.

    Thanks again, Mark



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark Guest

  7. #7

    Default Re: MS Access - problem with Double type field

    Hi - thanks for the help.

    This has now been resolved - although I'm not really sure which part was
    the key - but I now have a far more efficient 'query' accepting the data
    - The CDbl did seem to help - although setting the lcid within the page
    did not - I added a global.asa file, which set the lcid to my locale
    (2057), at my hosts instruction.

    Thanks again, Mark



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark Guest

Similar Threads

  1. Replies: 0
    Last Post: October 11th, 03:24 AM
  2. Replies: 0
    Last Post: September 22nd, 10:26 PM
  3. Access data type mismatch for empty form field
    By createmedia in forum Coldfusion Database Access
    Replies: 5
    Last Post: January 27th, 05:24 PM
  4. Problem with character palette and Tracking field: can't type zero after type is modified
    By Tim_Murray@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 0
    Last Post: May 11th, 04:21 PM
  5. Replies: 6
    Last Post: July 22nd, 01:40 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139