Problem With Money Variable In Sql 2005

Jul 19, 2007

Hi,

I have a Price variable in a table and when the price = 999 999.99$ the database convert it to 1 000 000$.
If price = 999 999.51$ convert it to 999 999.50$

If the price is 999 998.99$ convert it to 999 999.00$

How can i fix that? I always want that the price to be the same as the user enter it.

I use :

Dim Prix As Single

myCommand.Parameters.Add(New SqlParameter("@Price", SqlDbType.Money))
myCommand.Parameters("@Price").Value = Prix

Thanks

View 7 Replies


ADVERTISEMENT

Problem With Variable Of Type Money?

Mar 2, 2006

Here's what I want to do: I've got a table with orders, each order hasa specific discountrate (an int, which represents a percentage). Eachorder consists of 1 or more items in another table, each item in thattable has a price. Now I want to return the full price and thediscounted price (or the discounted amount).Here's a relevant excerpt of the code:------------------------------------------------------------------CREATE TABLE #tmp (OrderID Integer,Price money,Discount money)DECLARE @Discount moneySELECT @Discount =(((SELECT SUM(OrderDetails.Price * OrderDetailsAmount)FROM OrderDetailsWHERE OrderID = @orderID AND CustomerID = @CustomerID)+(SELECT ISNULL(SUM(OrderDetailsSupplement.Price *OrderDetailsAmount),0)FROM OrderDetailsSupplementINNER JOIN OrderDetails ONOrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsIDWHERE OrderID = @orderID AND CustomerID = @CustomerID))*( @DiscountRate / 100 ))SELECT CustomerFull,SUM(Price) As Price,SUM(Discount) As Discount,SUM (Products) As Products,COUNT(@orderID) As OrdersFROM #tmpGROUP BY CustomerFullORDER BY CustomerFull------------------------------------------------------------------The problem: instead of getting a low number (like 0.57 for instance),I get a 0. Right now I've "solved" this by replacing "( @DiscountRate /100 )" with just "@DiscountRate" and then dividing by 100 in my aspcode, but I'd really like to know what I'm doing wrong.--BVH

View 7 Replies View Related

Storing A Money Data Item (in SQL Server) In A Double SSIS Variable

Nov 5, 2007

Hi

I have a table in SQL Server with following spec
Table1(Grossamount(money))

I have a SSIS variable called grosstot of type double and use following sql in Execute SQL task in SSIS

Select Sum(Grossamount) from Table1

I then assign the result of above sql stmt to the SSIS variable grosstot within the same Execute SQL task.

it gives me the error :
[Execute SQL Task] Error: An error occurred while assigning a value to variable "grosstot ": "The type of the value being assigned to variable "User::grosstot " differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".



I tried the following sql to no avail

Select CONVERT(numeric (12,2), Sum(Grossamount) from Table1


Your help very much appreciated

View 7 Replies View Related

How Can I Cast A Money Field In A View To Look Like Money

Oct 16, 2007

I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).
If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.
How can I cast so it is still money? by default the entries look like 100.0000.
They must remain a money datatype.

View 9 Replies View Related

Forwarding Variable Number Of Parameters From VB.2005 To Sql Server 2005 Stored Procedure

Jan 15, 2008

I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....

View 1 Replies View Related

Max Size Of XML Variable In SQL Server 2005

Apr 11, 2007

What is the maximum size supported by XML datatype in SQL 2005.???
Thanks in Advance.
 
 

View 1 Replies View Related

Can A Sql 2005 Function Return More Than A Variable

Jul 30, 2007

Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance

View 3 Replies View Related

Filename To Variable SSIS 2005

Mar 31, 2008



Hi All,

may be i'm asking a question that was discussed for billion time, but i can't find exact answer.

So i have access database that generates .xml or xls files by pressing button. So user select from drop down menu id ( e.g. 100,101,102,103 etc) and then file 101.xml or 102.xml or 101.xls or 102.xls is generating.

i need SSIS package to be configured to get filename is variable and then use insert data from xml or xls file to the related table

I don't understand how to pass file name through variable to SSIS package and then work with so called "variable".xls or
"variable".xml file. Could you please give detailed answers if it's possible?

Your early reply will be very much appreciated


Thank you

View 10 Replies View Related

Must Declare The Scalar Variable In Sql 2005

Dec 18, 2007

Code Block
CREATE FUNCTION myFUNCTION (@YearMounth varchar(5) =[84/08] )
RETURNS table AS
return (
SELECT dbo.T1.PersonelNo, dbo.T1.OfficeCode, dbo.T1.jobCode
FROM dbo.T1
WHERE (LEFT(dbo.T1.StartDate, 5) <= @yearMounth) AND
(LEFT(dbo.T1.EndDate, 5) >=@yearMounth)
)





this FUNCTION run in SQL Server 2000 But Not Run In SQL Server 2005 And This Error




Code Block
Msg 137, Level 15, State 2, Procedure myFUNCTION, Line 6
Must declare the scalar variable "@yearMounth".

View 4 Replies View Related

Money In SQL And ASP.NET

May 6, 2007

Hi,I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (£2.00) or 20.00(£20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.
I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.
Thanks.
 
 

View 2 Replies View Related

DataType Money

Dec 28, 2006

Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00
Thanx

View 3 Replies View Related

Money Format ..need Some Help..please

Jan 13, 2007

Hi everybody.Here's my Product tableID int,Title nvarchar(50),Price moneyHow can I get value from price field like thisIF PRICE is 12,000.00 it will display 12,000IF PRICE is 12,234.34 it will display 12,234.34Thanks very much...I am a beginner. Sorry for foolish question

View 7 Replies View Related

Money, SQL Server And C#

May 19, 2007

Argggg....Please help.I need to store money in SQL Server.I am using C# and a stored procedure to insert.How do you accomplish this? Specifically what datatype should the money variable be in C# during the insert? It's initial value is a string as it is entered from a text field.I have been trying for over an hour now to simply insert money into SQL Server using C# and have it stored as a money type!Thanks 

View 3 Replies View Related

Money Format

Oct 10, 2007

 hello everyone...,i have problem in money format...i have moneytable is containning: userid          money   A            20000,0000   B            40000,0000userid type varchar(50)money type money i have store procedure like this:ALTER PROCEDURE [dbo].[paid](    @userid AS varchar(50),    @cost AS money, @message as int="1"  output)ASbegin transactiondeclare @money as money select @money = moneyfrom moneytablewhere userid=@useridif (@money > @cost)beginset @money = @money - @costUPDATE moneytable        SET money = @money        WHERE userid=@useridset @message ='1'endelsebeginset @message = '2' endCOMMIT TRANSACTION when i execute this procedure. i insert value to userid        Acost         100,0000 it can not decrease, because cost 100,0000 is same with 1000000, why is like that?i want cost 100,0000 is same with 100. how can i do that? thx... 

View 6 Replies View Related

Comparing Money

Aug 29, 2004

Hi,

I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?

Thanks in advance,
Uday

View 6 Replies View Related

SQL Money Convertion,

Apr 24, 2005

Hi All,
 
Here in Belgium, we work with a comma as decimal seperator, also in all the web apps...
I've tried to update a money table on the sql with following statement
update parts set article = '" & art & "' and price= cast(" & p & "  as decimal(5,2)) where supid=202
in this example p is a variable and contains figures like 5,7
This statement always give following error Incorrect syntax near the keyword 'as'
does some have an idea how to fix this one ?
 
thx

View 4 Replies View Related

Money Format

Apr 5, 2001

Is there a way to format the output of an calculation so that a comma is used as decimal symbaol and a periode after each 3 digits left of the comma ?

We're calculating salary increase like 5000 * 1.025 and the output should look like 5.250,00

Thanks for any help
Charles

View 2 Replies View Related

Money Datatype

Jan 27, 2003

Greetings,

How do you keep or round a money datatype to use only 2 decimal places? I have an instance where I am multiplying a money with a decimal datatype. The decimal has up to 8 decimal places. This is causing the money datattype to extend to 4 decimal places. This makes for problems when I am comparing 2 values.

ex.)
IF 14.88 >= 14.8821


99% of the time this does not happen, but is causing problems. Does anyone have any suggestions?


thanks in advance,

Roger

View 2 Replies View Related

Money To Varchar

May 8, 2004

From query analyzer how can I change the field datatype from money to varchar?
Alter table tablenaame alter column columnname varchar(30)--Is not working.

View 2 Replies View Related

Money To Varchar

May 11, 2004

How can I alter a field datatype from money to varchar?

View 2 Replies View Related

Money Format

Jun 3, 2008

Hi, I have data in a Payment field:

45.14 - was orig in text format.
Now after converting to money, I see it as this:

45.1400

I need to get rid of the zeros! Anyone please help?

Thanks!

View 3 Replies View Related

Money Format ..need Some Help..please

Jan 12, 2007

Hi everybody.
Here's my Product fields

ID int,
Title nvarchar(50),
Price money

How can I get value from price field like this
IF PRICE is 12,000.00 it will display 12,000
IF PRICE is 12,234.34 it will display 12,234.34

Thanks very much...I am a beginner. Sorry for foolish question

View 4 Replies View Related

Money/currency

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

Datatype For US Money

Feb 12, 2008

Do you usually use the money or smallmoney datatype for US dollars in a table, or do you use a decimal datatype, and if so which. Any opinions on the advantages and disadvantages of each?

If you use money or smallmoney, do you usually add a constraint to make sure the value is even to the penny (no $24.3487 type amounts)?











CODO ERGO SUM

View 1 Replies View Related

Scrambling MONEY

Mar 3, 2008



I'm looking for a way to "secure" a column of MONEY values. The idea is to hide the value without resorting to actual SQL encryption services that would require converting the MONEY to a VARBINARY. In short, I want to keep the column type as MONEY, but I want to scramble the value, so its still a valid MONEY type.

The goal is to have a pair of UDF's SCRAMBLE() and UNSCRAMBLE() that work on MONEY.

I've done some searching, but have not found anything along this topic.

Does anyone have any ideas on how to do this?





View 6 Replies View Related

All My Money In Dollars Please??

May 30, 2007

I have a Windows 2003 server, with SQL 2005. How do i change the culture/regional options for the database as on my local test machine (which seems to be exactly the same in terms of windows regional settings and SQL server settings) the currenency fields show with a £ signs as expected, however on my production box they are $. I know this should be simple but its the night before a deadline and my brain is screwed. Please help!!

View 5 Replies View Related

Money Conversion

Apr 2, 2006

Hi!

When I write:

'SELECT Amount FROM tTransaktion'

I get returnvalues such as '12000.0000'. Instead, I want it to return '12 000'.

The Amount column is of datatype money. Is this possible!?

Thanks!

View 7 Replies View Related

How To Use A Variable From The Code On Microsoft Reporting Services 2005

Jan 19, 2007

Hi all.

I have a Application developed on VB2005 with the reports (processed local) from Microsoft Reporting Services 2005.

I would like to know how can i use the value of a variable to be displayed on a textbox on my report.

Thanks for the help.

Best regards.

dp

View 7 Replies View Related

SQL Reporting Services 2005: Must Declare Scalar Variable

Mar 4, 2008

I have a report that calls a stored procedure that builds a temp table and then runs a select that joins on that temp table. The only parameter I have is @Name, an optional parameter that can be filled or NULL.

I keep getting the error "An error has occurred during local report processing. An error has occurred during report processing. Query execution failed for data set. Must declare the scalar variable "@Name".

I have checked to make sure this parameter is connected to the dataset by looking at the parameter tab. I've searched on posts but haven't seen an answer. Does anyone have any idea what this could be about and how to fix it?

View 20 Replies View Related

How To Use Same Variable Between The Control Flow Tasks In SSIS 2005?

Apr 16, 2007

I want to get the start time of data load and end time after data load and store it in a table which has mapping_id , mapping_name,start_time,end_time.

i use ActiveXScript task to get the start time before data load and store the mapping_id in a global variable,then data flow transformation occurs.

i want to use a global variable to store the mapping id ,so that i can update the end time after data load with that variable.how to do this?

is there any other way, i can get the start and end time of data load (other than the logging information)?

View 3 Replies View Related

Money Format Error

Oct 10, 2006

I use asp.net 2.0 and sql server 2005 for a web site (and Microsoft enterprise library).When I run aplication at local there is no problem but at the server I take this error:Disallowed implicit conversion from data type varchar to data type money, table 'dbname.dbo.shopProducts', column 'productPrice'. Use the CONVERT function to run this query.productPrice coloumn format is money. It was working correctly my old server but now  it crashed.How can I solve this problem? And why it isn't work same configuration? My code:decimal productPrice;Database db = DatabaseFactory.CreateDatabase("connection");string  sqlCommand = "update shopProducts set ......................,productPrice='" + productPrice.ToString().Replace(",", ".") + "',..................... where productID=" + productID + " ";db.ExecuteNonQuery(CommandType.Text, sqlCommand)

View 1 Replies View Related

Money In Stored Procedure

Jun 25, 2007

 Hi there,I have a table products with product_price money(8) field.  I have a stored procedure,CREATE   procedure p_dat_update_product(            @product_id int,             @product_price decimal) asset nocount ondeclare  @msg varchar(255) -- error message holder-- remove any leading/trailing spaces from parameters  select @product_id = ltrim(rtrim(@product_id))  select @product_price = ltrim(rtrim(@product_price))-- turn [nullable] empty string parameters into NULLs  if (@product_id = N'') select @product_id = null  if (@product_price = N'') select @product_price = null-- start the validation  if @product_id is null  begin    select @msg = 'The value for variable p_dat_update_product.@product_id cannot be null!'    goto ErrHandler  end-- execute the query  if exists (select 'x' from product where product_id = @product_id)   begin   update dbo.product    set            product_price = @product_price    where product_id = @product_id  end   if (@@ERROR <> 0) goto ErrhandlerreturnErrHandler:  raiserror 30001 @msg  returnGO And a class function        public void Edit_Product(string product_id,string product_price)        {            decimal dec_product_price = decimal.Parse(product_price);            SqlDataServ oMisDb = new SqlDataServ("LocalSuppliers");            oMisDb.AddParameter("@product_id", product_id);            oMisDb.AddParameter("@product_price", dec_product_price.ToString());            oMisDb.ExecuteCmd("p_dat_update_product");        }But i keep getting this error,Error converting data type nvarchar to numericoMisDb.ExecuteCmd("p_dat_update_product"); I do not know what to do and solve this.  Is there something that i have done wrong.thanks in advance. PS: still learning aspx and ms sql 

View 12 Replies View Related

Diff Between Money And Smallmoney

Mar 15, 2008

What is the difference between money and smallmoney, and is there a way to format these?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved