ASP, SQL Server And Currency Datatype...
May 13, 2005
Hi,
I've just created an ecommerce website using ASP and SQL Server 2000 - on my development machine (WinXP Pro, IIS, SQL Server 2000) the SQL Server datatype for the price field is Money - however, the datatype on my hosts SQL Server is Currency (there is no Money datatype available??)
Now, the problem is this: on my local machine I enter a price of say 99.99 - this shows up on the front-end & in the admin area as 99.99, no problem. Now, on the live server when I enter 99.99 it is somehow converted to 9999 - if I enter 99,99 (with a comma) it shows correctly as 99.99 - however, when I go to edit the price it reverts to 9999...does anyone know what is going on here? Why is it converting 99.99 to 9999??
Please let me know if you need more clarification of the above.
Many thanks,
Peter
__________________
Paliz Design
http://www.palizdesign.com/
View 4 Replies
ADVERTISEMENT
Jun 11, 2008
What is the best datatype in SQL Server 2000 to use for a US dollar amount?
View 1 Replies
View Related
Aug 5, 2015
I have this data below :
isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | DEM | 013 | 2009 | 5 | 8
DEX | XFFA | EUR | 013 | 2014 | 11 | 25
get this data by running the following query :
select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year)
,max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFA') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt
what's needed is to get the earliest record all times, no mater the currency :
isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | EUR | 013 | 2014 | 11 | 25
View 3 Replies
View Related
Apr 15, 2008
currency format in sql 2005 reporting services.
How to format amount do not display numbers after decimal point? e.g. if the value in the database 139000.82 what function to use (other than C or FormatCurrency)to display amount in the report such as $139,000?
Thank you to all!
MY
View 6 Replies
View Related
Jan 28, 2015
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
View 4 Replies
View Related
Apr 24, 2008
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
View 6 Replies
View Related
Sep 17, 2003
Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
View 1 Replies
View Related
Mar 14, 2008
Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
View 10 Replies
View Related
Mar 13, 2004
Hello I am a newbe to sql and i am shure my problem is simple but hope somone can help.
My Total comes out like $20.00 and i want it to come out like 20.00€
Simple but frustraiting
Any help apprieciated
Colin
View 4 Replies
View Related
Jan 24, 2007
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible?
SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
I tried:
SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
But this does not format it as currency. Any input would be helpful.
View 6 Replies
View Related
Sep 8, 2004
I have a currency that needs to go into the Database. The column in the database is of type money.
When i try ctype or convert into integer, desimal or even sqlmoney type i get an error. Cannot convert data type nvarchar to data type money.
Can anyone help me with this Please.
Here is the code...
sqlcom = New SqlCommand("insert MeasureQuantities(MeasureQuantityName,MeasureQuantityDescrip,MeasureQuantityPrice) values(@MeasureQuantityName,@MeasureQuantityPrice,@MeasureQuantityDescrip)", ocnn)
ocnn.Open()
Dim decPrice As System.Data.SqlTypes.SqlMoney
decPrice.ToDecimal()
decPrice = CType(MeasureQuantityPriceAdd.Text, System.Data.SqlTypes.SqlMoney)
sqlcom.Parameters.Add("@MeasureQuantityName", MeasureQuantityNameAdd.Text)
sqlcom.Parameters.Add("@MeasureQuantityPrice", decPrice) 'MeasureQuantityPriceAdd.Text, System.Data.SqlTypes.SqlMoney))
sqlcom.Parameters.Add("@MeasureQuantityDescrip", MeasureQuantityDescripAdd.Text)
sqlcom.ExecuteNonQuery()
View 2 Replies
View Related
Apr 10, 2008
Hi i need to apply a currency conversion in my view now i don't have any table which holds the currency value at the moment its hard codes. I can build a tale with the currecnty but am not sure how i would apply this in my view.. without nothing to references it.
View 1 Replies
View Related
May 28, 2008
How do I get data that looks like 200,000 returned as a currency format?
Here is what I have in my select statement it doesn't work and can't seem to find an example that works or maybe I just have it set up wrong?
'$' + convert(Sum(OrderHeader.SubTotal)as money) as 'Total Sales'
Ultimately I have a field that is returned and it exists on a reporting services report. I can't find a way just to format the field as currency through the properties either would that be the better way to do it if there is a way?
View 2 Replies
View Related
Mar 25, 2007
Hi all. Is there a way in SQL to convert the integer to currency format? just for example....
4000 convert to 4,000
1312500 convert to 1,312,500
30000 convert to 30,000
Most of you will say "Do it in your front end"... but the problem is I don't know how to do it in my Report(Business Intelligence Project). If anyone of you knows, tell me please... Thanks. :)
-Ron-
View 12 Replies
View Related
Sep 28, 2007
Hello forum,
I am quering a datebase table from an asp page, comparing values that the user input in a form, value is a string 1000000 and the field in the database that i am compparing from is a currency type, i am getting an error saying that i need to convert to currency, type mistmatch. i am using the following to convert but it does not work, any suggestions.
here is my query:
"SELECT STotalAllocation FROM tabletoquery where STotalAllocation >='" & CCur(Session("plusMillion")) & "' "
'Session("plusMillion") is equal to 1000000
this is the error:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Disallowed implicit conversion from data type varchar to data type money, table 'SiteDetail7CMaster', column 'STotalAllocation'. Use the CONVERT function to run this query.
search_report.asp, line 136
View 4 Replies
View Related
Feb 21, 2008
Hi!
I have money value of 56.0000.I want it to only give me the last two decimal places,56.00.I used this one to come up to that result:cast(round(sum(total),2)as int)but my result is this 56.Am i on the right track?
Funnyfrog
View 13 Replies
View Related
Oct 20, 2006
Starting in my control flow, I execute a data flow that populates a recordset via SQL 2005 Stored Proc. One of the columns in source table is a currency type.
Back to the control flow, I have a for each container that includes an execute sql task that updates or inserts records into another table. I get precision or data type issues since I can not assign the package variable to a currency data type. The only way I can get this to work is if I convert the currency column in my data flow to a string and then cast the variable in my update/insert sql task. Any suggestions?
Thanks....Gary
View 4 Replies
View Related
Dec 14, 2006
The $ must appears only on certain total levels and has to be in the same position in the column regardless of the value for financial reporting. In other words, I don't want the $ to float next the most significant digit in the value. See the example below from Excel. Using an IIF statement I can concatenate the $ to the string when it is a certain group level. However, I have tried many various formatting strings but can't find one that results in a fixed number of characters so that the $ will always appears in the same position in the column.
Anyone have a way to do this?
General Government
222
3.45%
$ 405,590,654
1.98%
Public Buildings
194
3.01%
375,453,314
1.83%
Other Facilities
22
0.34%
21,324,140
0.1%
Property Acquisition
6
0.09%
8,813,200
0.04%
Grand Total
6,439
100.0%
$ 20,507,977,298
100.0%
I did get this to work by adding a column to the grouping. That somehow causes the heading size to increase (??) and takes much more time than coding a format code in an expression (I have many reports to do).
View 4 Replies
View Related
Jun 19, 2006
It seems that the "decimal" type allows more flexibility but also uses more storage than "money". On the other hand, does "money" have any special t-sql functions that understand its unquie nature? (e.g. adding two money values together with no cents round-off error).
For example, would the number 12345.12 be declared as a decimal(7,2) or currency?
Barkingdog
View 1 Replies
View Related
May 30, 2008
I have a situation where I have three tables in the source i.e.
1. Currency
2. Currency ExchangeRate
3. ExchangeRateType
I understand the conversion of 1:M, M:1 and M:M currency conversion in the Analysis Services by modeling Currency as a Dim and Exchange Rate as a Fact Table with Time Key for semi-additive aggregation. Same as the way Exchange Rate works in the Adventure Works.
Now the problem we have, there is no one Exchange Rate, we have different Exchange Rate Types. So different Exchange Rate will be applied to different Currency conversion for Calculations.
How to materlized these 3 sources table into a dimensional model in a right way, so it answers all the scanerio.
Thank You
View 1 Replies
View Related
Dec 22, 2004
I have a fact table with amounts, all in a single currncy. I would like to be able to process the cube where I can select the currency from a dimension.
The format of the currency exchange dimension must include date, currency code, and exchange rate, where the native exchange rate (the currency that the amounts are in) is defined as 1.000.
View 7 Replies
View Related
Aug 9, 2002
How can I get the money datatype to return only two decimals instead of four?
View 2 Replies
View Related
Feb 23, 2007
I have a column - datatype 'money' and my price variable is (for instance)
8450
how do I put this value into the money column without getting this error?
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'db196009544.dbo196009544.vehicles', column 'price'. Use the CONVERT function to run this query.
ASP VBScript
I am using this syntax:
price = Upload.Form("price") '---this gives 8450---
SQL="UPDATE vehicles SET price='" & price & "' WHERE id=" & request.queryString("linkID") & ""
Thanks
mark
View 4 Replies
View Related
May 3, 2004
I'm looking to display the results of the following query as currency. Any help with this would be greatly appreciated...
SELECT SUM (sysTotalCharge) - SUM (sysTotalCredit) AS TotalNet FROM Bookings WHERE (sysRecordDate BETWEEN '01/01/::Year::' AND '01/31/::Year::')
View 2 Replies
View Related
Jun 1, 2004
I've written an Sql statement to extract the Currency Amount and the currency rate and I want to calculate the Amount in Local Currency.
If the currency amount is in the local currency the Currency rate value returned is 0. I have used a case statement to convert the 0 to 1. (is there
easier way)
But I now can't calculate my Local amount value because of the way I have converted the currency rate.
Select IH.Amount, 'Currency Factor' = CASE
when
IH.[Currency Factor] = 0 then 1
else IH.[Currency Factor]
end,
what way should I have written this?
View 4 Replies
View Related
Feb 12, 2008
Hi,
The field below I need to convert to a dollar amount. Can someone help me please?
clm_tchg (decimal(10,2), null)
Thanks
View 5 Replies
View Related
Feb 1, 2007
I'm looking more into UDAs and wondering if someone can help me out. I get how I use them and route to them. What about their freshness -- the data? If I'm aggregating data via some methods...when is that underlying data aggregates. When the UDA is called at runtime (coulnd't that hurt performance). I guess I"m likening these things to indexed views with benefits. Am I lost here -- someone help..thanks.
View 1 Replies
View Related
Nov 28, 2007
It's probably right underneath my nose, but I cannot seem to be able to set the currency format.
My Stored procedure returns an integer that represents a cash value in pennies (like cents).
I then divide this value by 100 and convert it to a Double, so its value is represented in Pounds.
This all works fine, my problem is that when I wrap the above logic in a FormatCurrency, I get a dollar ($) sign before it. How can I change this so that it gives a pound sign (£)?
View 8 Replies
View Related
May 16, 2008
I am trying to understand the currency conversion but facing some problems in the same.The more I am tring to understand that the more confused am i getting.
My requirement is specific. I am trying to write sql queries for the measures which come under the scope of currency conversion.
I will query the Cube to get the measure value :
SELECT [Measures].[Sales Amount] on 0 from [Adventure Works DW]
And then query the SQL database as:
Use AdentureWorksDW
SELECT SUM(SalesAmount) FROM FactInternetSales
My understanding is this ( Please correct me if i am wrong) :
If the Currency conversion is:
1. Many to One : Data will be different (in SQL and MDX) as currency conversion is applied which will convert that to a particular (Corporate) currency.
2. One To many : Data will not be different (in SQL and MDX)
2. Many to many : Data will not be different (in SQL and MDX)
I did not get much information on the same from anywhere. I want to undersatnd the script of the Currency conversion. (The script generated by Currency conversion wizard) and then depending on the script (I will extract the Type of conversion) write the SQL for that.
Regards..
Girija Shankar
View 2 Replies
View Related
Dec 15, 2005
HI,I have a table with IDENTITY column with the datatype as INTEGER. Nowthis table record count is almost reaching its limt. that is totalrecord count is almost near to 2^31-1. It will reach the limit with inanother one or two months.In order to avoid the arithmentic overflow error 8115, we would likechange the datatype from INT to BIGINT. we hope this will solve ourproblem.How do I approch this datatype conversion?. Since the data count ishuge, that leads to a long down time of database.we need better approach or solution for this problem?. kindly give mea better solution that will reduce the total downtime of the productiondatabase.?.Regards
View 1 Replies
View Related
Feb 25, 2008
Hi guys..
i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...
and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...
Thanks and looking forward.-MALIK
View 5 Replies
View Related
Nov 12, 2003
I'm brain-dead so forgive the simple question, but how do you sort a table based on the value in a money datatype field when sorting defaults to:
$1
$10
$15
$2
$20
$25
...
$5
$55
etc.
Obviously this isn't a "sorted by total amount", where I'd want the results returned like:
$1
$2
$3
...
$9
$10
$11
...
etc.
Links to examples or documentation would be great (I've tried searching both these forums and Google already).
View 8 Replies
View Related
Apr 22, 1999
Going against a Sybase database using ODBC, the numeric data in the amount field (money)
drops all zero's to the right of a whole number when bringing it into MSQL 7. Example:
2,000.00 comes across as 2
2,001.00 comes across as 2,001.00
Has anyone a solution to this? What am I doing wrong? If I run a query against the SYBASE
database, everything looks fine. Once I bring it into SQL, I'm about 9,000,000 dollars off.
Thanks for any help you can offer.
View 1 Replies
View Related
