MSSQL Float Data Problem

Hello,

I am connecting to MSSQL through ODBC using regular SQL commands (SQLAllocHandle, SQLConnect, SQLFetch). Sometimes when I retrieve float data from the server it gets corrupted. For instance, instead of 59.457443 I will actually get 59.45744299999998. The value that is stored in the database is 59.457443 and it is stored as a float. The code I use to retrieve the data worked OK with an Access database, the problem only appeared after switching to MSSQL. Does anybody know if it's a configuration issue? Any help would be greatly appreciated.

EoF



ADVERTISEMENT

MSSQL && ODBC Float Problem

Hi,

I am having problems using MSSQL Express with ODBC. I am trying to retrieve a float field from the MSSQL server, but the value I get is corrupted (or at least approximated wrong). For instance, the value I want to retrieve is 59.457443, but what I actually get is 59.457442999999998. This only happens if I connect through ODBC. I did some tracing with ODBC and I've noticed that it's not a problem in my code, since the wrong value is what is actually extracted from the database. Following is an extract from the log file.

a88-ec EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 03C62918 UWORD 5 SWORD 1 <SQL_C_CHAR> PTR 0x0415EB30 [ 18] "59.457442999999998" SQLLEN 50 SQLLEN * 0x0415E720 (18)


I am wondering if I overlooked something configuration-wise of if this is a bug in the driver? Any help would be greatly appreciated.

EoF

View Replies View Related

MSSQL Float Precision Problem By Using PHP Driver

I'm connecting MSSQL Server 2005 Express via MS Driver for PHP (CTP version October 2007) and sometimes I don't retrieve exact float values. For example, in database is 0.7 and I get 0.69999999999999996, but for 1.0 in database I get 1.0. The result is the same, if I use prepared statement (sqlsrv_conn_prepare() and sqlsrv_stmt_execute()) or directly sql_conn_execute().

The table definition is very simple:
CREATE TABLE [dbo].[test](
a float NOT NULL
);
insert into test values(0.7);
insert into test values(1.0);
insert into test values(1.1);
insert into test values(1.2);
insert into test values(1.3);
insert into test values(1.4);
insert into test values(1.5);
insert into test values(1.6);
insert into test values(1.7);
insert into test values(1.8);
insert into test values(1.9);
insert into test values(2.0);

And select command is:
select a from test;

PHP code:
$conn = sqlsrv_connect("localhostsqlexpress", array("UID" => "sa", "PWD" => "password"));
sqlsrv_conn_execute($conn, "USE dbname");
$stmt = sqlsrv_conn_execute($conn, "select a from test");

while($row = sqlsrv_stmt_fetch_array($stmt, SQLSRV_FETCH_TYPE_ARRAY))
echo $row[1]."; ";

Result:
0.69999999999999996; 1.0; 1.1000000000000001; 1.2; 1.3; 1.3999999999999999; 1.5; 1.6000000000000001; 1.7; 1.8; 1.8999999999999999; 2.0

Expected result:
0.7; 1.0; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2.0

Running configuration:
Windows 2003 Server SP2, SQL Server 2005 Express SP2, IIS6, PHP 5.2.5 ISAPI

Thank you very much for help.
Vlasta

View Replies View Related

Convert A Binary Float To FLOAT Datatype

I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.

All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little


Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)

SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))

SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]

And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT

SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END

WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END

RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!


Peter Larsson
Helsingborg, Sweden

View Replies View Related

Float Data Types

I've got a float data type in a table. I imported data from a csv file. One value was 0.5195, but when I use the value in a calculation or select it using the Query Analyser, I'm getting a value of 0.51949999999999996. Is there any way around this, it's a real pain?

Thanks in advance.

View Replies View Related

Float Data Type

We are using a GL package called Solomon. It uses SQL Server 7.0 for it's database. I want to create a data warehouse using this data as the source. The package uses the float data type for dollar amounts. The dollar amounts in the data have either no numbers, 1 number, or 2 numbers after the decimal point. Is the float data type the best one to use in my data warehouse for dollars and cents, or should I try using the monetary or decimal (precision 2) data type? Which type uses the most storage?

View Replies View Related

Float Data Type

I am using SQL Server 7.0. I create a table with one field..type of float.
Using SQL Server Query Analyzer:
INSERT INTO MyTable(MyField) VALUES (4.9)
INSERT INTO MyTable(MyField) VALUES (Round(4.9,2))


SELECT * FROM MyTable

Result = 4.9000000000000004

This is a basic example of a problem I am having in another table with the
same float field that I am using to store money in. I don't want to use
the money field as the BDE from Borland has some issues with money fields.
Any suggestions? Thanks in advance.

View Replies View Related

Error In Reading Float Data

I am using SQL Server 2000, VS 2003
I have Education table in which there is a field CGPA having float data type (null allowed) I retreive the data from SQL server using stroed proc and store it in SqlDataReader dr while reading if CGPA contains 0 then it raises an error that "Specified cast is not valid" other wise it does not raise any error.
while (dr.Read()){ Education e = new Education(); e.EducationId = dr.GetInt32(0);  e.Country = dr.GetInt32(1); e.InstitutionName = dr.GetString(2); e.Grade = dr.GetString(3); e.CGPA = dr.GetFloat(4); // ERROR HERE  e.Percentage = dr.GetFloat(5); e.PassingYear = dr.GetString(6);}
where as in Education CGPA is also the float property can any one tell me how to read 0 value of float from SQL server
 

View Replies View Related

Problem With Float Data Type

Hi all,
I have declared a field with datatype as float.
When I enter value with two precision it chooses to round off to lower value and insert into the database.

I am losing precision in this case.

I want to insert 4.56. It inserts the way shown and hence all my further calculations go haywire.

Is SQL server designed to store float like this or Am I doing soemting wrong ???

Please advise...

4.56 (Inserted)

4.5599999999999996 (Stored)

View Replies View Related

SQL Server Float Data Type

1. In c++ (or any other language) I'm copying bit by bit to a doublevariable dob_var.2. If I copy dob_var to dob_var2 which is also a double I do not loseinformation.3. This implies that the same would be when copying to a data base, butwhen I write dob_var to an sql server float field (same size as adouble variable = 64 bit) it sometimes loses information. e.g. whenvalue=1E-320.It is true that SQL float is valid until 1E-308 but so is a C++ double.Any comments?

View Replies View Related

MS Access And SQL Float Data Type

Hi,

I have a developer running an update on a table row that has a float
data tpye column and another TEXT type column among the others.

There is an in explicable situation where some updates to the TEXT column
is executed from MS ACCESS bu not others. Trapping the SQL code coming
from MS ACCESS we realized that the command fails when the float column
has a long precision values, for example like, 62.699999999999996. However
if I run the same update from SQL Query Analyzer it works fine.

The problem is that Access is NOT using the index in this update but incorrectly includes ALL columns in the tables in it's WHERE clause, including this float column , when the command fails. I assume it does NOT do so when the command executes correctly on values NOT like 62.699999999999996, because the SQL Profiler did not list the command run.

How can access be told to use the index ot NOT to use all columns in WHERE
clause. Or is this a BUG in access.

Thanks in advance.

Please also reply to my email address,
r.wimalaeatne@iaea.org

Ranjit

View Replies View Related

Why I Cannot Save Float Data Type

Hai...

Hopefully ur guys can help me. I declare data type as float in price table. So the problem here is, whenever i enter a float number and move to the second row to enter a new number. The first number in the first row will automatically change to integer, without no reason. For example "2.3" will become "25".

Is there any configuration that need to do.

View Replies View Related

Load Float Data From Excel



For example load 0.15 from Excel to varchar field in database and get 14.99999, why?
How just get 0.15 in varchar field?

View Replies View Related

Float Data Types And Scientific Notation



Hi All,

Am I missing something or is there something odd with float data types. i know that float is not the most precise definition but i came across something really odd today.

first let me define the scenario.

this is sql server 2005, standard edition build 3042.

I have a table defined as


CREATE TABLE [dbo].[ASSET](

[Property_Num] [numeric](10, 0) NOT NULL,

[Accrual_Factor_Val] [float] NULL

)



the accrual_factor_val was updated to a value of0.00005 then the web service failed because the proc returned 5E-05!

i opened the table, and discovered this isthe stored value. is this correct?



thoughts and ideas?

thanks for your input!

View Replies View Related

Error Converting Data Type Varchar To Float

I have created a stored procedure that contain this field (below) inorder to meet certain criteria. But my problem is when I try to runthe stored procedure I encounter an error "Error converting data typevarchar to float".CASE Final WHEN 0 THEN '--' ELSE Final END AS FinalGradeThe Final field is a float data type.Could anyone teach me how to fix this problem?

View Replies View Related

Error Converting Data Type Varchar To Float

Hello everyone... i have some problem with instead of trigger... after insert values into v_PhotovoltaicClimateData, i got this error, Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

below is my DDL for Photovoltaic table

quote:CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Below is my DDL for ClimateData table

quote:CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



below is my v_PhotovoltaicClimateData

quote:CREATE VIEW [dbo].[v_PhotovoltaicClimateData]
AS
SELECT dbo.Photovoltaic.PV_site, dbo.Photovoltaic.PV_state, dbo.Photovoltaic.PV_type_of_system, dbo.Photovoltaic.PV_nominal_power,
dbo.Photovoltaic.PV_module, dbo.Photovoltaic.PV_mounting, dbo.ClimateData.Latitude, dbo.ClimateData.Longitude, dbo.ClimateData.Altitude,
dbo.ClimateData.Climate_type
FROM dbo.ClimateData INNER JOIN
dbo.Photovoltaic ON dbo.ClimateData.PV_application_id = dbo.Photovoltaic.PV_application_id

below is my instead of trigger command...

quote:CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Photovoltaic already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Climate Data already in the table ***/

END -- trigger def

this is my commad insert values using instead of trigger that i've created...

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44 , 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

after execute this commad, i got this error..
quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

what should i do??? anyone help me.

View Replies View Related

Error Converting Data Type Varchar To Float.

hello anyone... i got this message "Error converting data type varchar to float" when i was trying to insert values into table using instead of trigger...

below is my table ClimateData

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[ClimateData] Script Date: 03/26/2008 03:04:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[ClimateData] WITH CHECK ADD CONSTRAINT [FK_ClimateData_Photovoltaic] FOREIGN KEY([PV_application_id])
REFERENCES [dbo].[Photovoltaic] ([PV_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE


Below is photovoltaic table

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[Photovoltaic] Script Date: 03/26/2008 03:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_OrganizationDetail] FOREIGN KEY([Org_application_id])
REFERENCES [dbo].[OrganizationDetail] ([Org_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_Users] FOREIGN KEY([User_application_id])
REFERENCES [dbo].[Users] ([User_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE

Below also my command for instead of trigger


quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Organization Detail already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Organization Types already in the table ***/

END -- trigger def




and finally, i hav tried using this command to insert into table v_PhotovoltaicClimateData.. this is the command to insert

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44, 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

and i got this error...

quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.



so anyone... plzz help me...

View Replies View Related

Using Float/Money/Numeric Data Types On SQL Server 2000

Hi,
I am trying to execute a stored procedure in my ASP VB script code to insert a recordset into a table of SQL Server 2000.
On the tables, I use some money data and a float. In the stored procedure, I used the numeric type for both values:

CREATE PROCEDURE dbo.PRODUCT_Insert_Accessories
@partnumber CHAR(50),
@product CHAR(255),
@prodesc CHAR(500),
@proprice numeric, <----- this is normally a money type in the table
@price numeric, <----- this is normally a money type in the table
@shipoption INT,
@peso numeric, <----- this is normally a float type in the table
@supplier int,
@cost numeric, <----- this is normally a money type in the table
@bspecial int,
@miles INT,
@speprice numeric, <----- this is normally a money type in the table
@fship int,
@id int OUTPUT


To call this stored procedure, I used the following ASP code:
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_Backoffice_STRING
Command1.CommandText = "dbo.PRODUCT_Insert_Accessories"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@partnumber", 129, 1,50,Command1__partnumber)
Command1.Parameters.Append Command1.CreateParameter("@product", 129, 1,255,Command1__product)
Command1.Parameters.Append Command1.CreateParameter("@prodesc", 129, 1,500,Command1__prodesc)
Command1.Parameters.Append Command1.CreateParameter("@proprice", 131, 1,8,Command1__proprice)
Command1.Parameters.Append Command1.CreateParameter("@price", 131, 1,8,Command1__price)
Command1.Parameters.Append Command1.CreateParameter("@shipoption", 3, 1,4,Command1__shipoption)
Command1.Parameters.Append Command1.CreateParameter("@peso", 131, 1,8,Command1__peso)
Command1.Parameters.Append Command1.CreateParameter("@supplier", 3, 1,4,Command1__supplier)
Command1.Parameters.Append Command1.CreateParameter("@cost", 131, 1,8,Command1__cost)
Command1.Parameters.Append Command1.CreateParameter("@bspecial", 3, 1,4,Command1__bspecial)
Command1.Parameters.Append Command1.CreateParameter("@miles", 3, 1,4,Command1__miles)
Command1.Parameters.Append Command1.CreateParameter("@speprice", 131, 1,8,Command1__speprice)
Command1.Parameters.Append Command1.CreateParameter("@fship", 3, 1,4,Command1__fship)
Command1.Parameters.Append Command1.CreateParameter("@id", 3, 2,4)
Command1.Execute()

But, I have an error (type mismatched, not the good data type) on the lines where are declared the money data and the float data.

Please, help me to found out the solution. Is it a data type problem?

Thanks,
Chris

View Replies View Related

Replace Nulls With Blank Spaces In Float Data Type

Hello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks

View Replies View Related

Converting A Data Type Double To A Type Float Using A Data Adapter &&amp; SSCE 3.5

Hi,

I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:


this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);


This did not happen with VS2005/CE3.01.

I have tried changing all references to decimal (or money in CE3.5) without luck.

I'm beginning to think that string may be the way to go!!!!!!!

Can someone shed some light on my problem.

Thanks,

Later:

It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.

View Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Float

I'm hoping you can help me.

I have the following expression:

[Inflation Adjusted Total] = POWER(dbo.v_ProjectTotalCosts.[Total Job Incl IDC]*
CONVERT(DECIMAL(10,5),[contingency])/100+1*CONVERT(DECIMAL(10,5),[Inflation Rate])/100+1,
Case when (CONVERT(DECIMAL(10,5),[In Service Year]) -
CONVERT(DECIMAL(10,5),[Estimate Base Year]))= 0
then 0 ELSE (CONVERT(DECIMAL(10,5),[In Service Year]) - CONVERT(DECIMAL(10,5),[Estimate Base Year]))END),

Total Job Incl Idc(decimal(12,6) = 546789.102568
Contingency(Real) = 10.0
Inflation Rate(Real) = 3.0
In service yr(int) = 2001
Estimate Base Year(int) = 2001

I have tried to modify this expression every which way but I still get the error message:

Arithmetic overflow error converting expression to data type float.
Warning: Null value is eliminated by an aggregate or other SET operation.

Do you have any idea what I'm doing wrong?

View Replies View Related

Disabled The Automatic Round Of SQL SERVER 2000 On The Float Data Type

Hello,

I have a problem with the round of SQL Server 2K on the float data type.

In my application I do a SQL request for find a row in a table of SQL Server 2K.

This is the request :

SELECT DISTINCT N_ROW_ID FROM COMM WHERE N_ARTICLE_ID=79510 AND N_DATASOURCE_ID=1 AND N_SOURCE_ID=-102 AND N_PROVIDER_ID=-100
AND N_LEAD_TIME IS NULLAND N_UNIT_PRICE = 329.78 AND N_UNIT_PRICE_CURRENCY_ID=1



N_UNIT_PRICE is a float data type.

In the line need, the field N_UNIT_PRICE has 329.78 but with the round of SQL Server 2K I have 329.779999999999997.

So I never find the row



Do you have a solution for comparate a float flied with SQL Server round and a float send ina request as my 329.78?

I can't transform the column in decimal or numeric data type.

Is it possible to Disable the function?



Regards

View Replies View Related

(Urgent)How Can I Specify The Excel File's Column To Import Data As Varchar Instead Of Float



Hi,
I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treatingit as a float)and so my insert fails...

what can i do to rectify the problem...

regards
Karen

View Replies View Related

Help With &"Error Converting Data Type Varchar To Float&"

Error converting data type varchar to float.


I am getting this error. Any one there to guide me

View Replies View Related

Importing CSV Data To MSSQL Using PHP

Hi, is this possible at all? From my understanding there are tools you canbuy to convert and merge data from an excel / csv document into an MSSQLtable, the thing is the convertor needs to be run on the server side and hasdirect connection to MSSQL. What I need to do though, is to import an excel/ csv file into an MSSQL table hopefully remotely via PHP. So is there a wayto do this at all?I know PHP has file functionality to load the file's contents into a string,so in theory we can write some PHP code to take a URL and analyse the csvfile and then manipulate the data and import into the MSSQL db, do you knowany sample code that does this? And am I right in thinking that this isdoable?Thanks,Jules

View Replies View Related

Help With MSSQL Data Types

I currently run our company website using MS Access and ASP. I am trying to upgrade the database to MSSQL Server 2000. Unfortunately I'm not finding it that straight forward. I am trying to learn MSSQL but the learning curve is a bit steep.

I have a column in my MSSQL db called tbl_FULL which contains a full description of the product along with html formatting for line breaks, paragraphs and special characters (ie ).

I have set the data type to varchar 1800. All seemed to be well until I tried to enter new data, but unfortunately it only displays the first 50 or so characters.

Can anyone tell me what I am doing wrong?

View Replies View Related

Image Data From MSSQL 6.5

I would like to use bcp and transfer image data from MS-SQL Server 6.5 to MS SQL Server 2005.

1. Can I use bcp successfully? if so should I mention any option to copy the BLOB?
2. Is there any reliable method to move tables with BLOBs

Regards,

/ash

View Replies View Related

Data Type That Accept . For MSSQL

Hi,


I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?

I'm using MSSQL Server 2000.


Please advice.

View Replies View Related

I Need To Know Where Mssql Server Stores Its Data.

Hello,I need to learn where mssql server stores its data. I knew it was inProgram filesCommon filesBut I cant find.. Its urgent pls..Thanx

View Replies View Related







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