Insert Decimal

Oct 10, 2006

I mamtrying to insert into a MS SQL 2000 database a decimal
and nothing works

i have tried :

INSERT INTO MyTable
(myDecimal) VALUES ('1,1')

INSERT INTO MyTable
(myDecimal) VALUES ('1.1')

INSERT INTO MyTable
(myDecimal) VALUES (CONVERT(decimal('1,1')))

INSERT INTO MyTable
(myDecimal) VALUES (CONVERT(decimal('1.1')))

INSERT INTO MyTable
(myDecimal) VALUES (CONVERT(decimal(1,1)))

INSERT INTO MyTable
(myDecimal) VALUES (CONVERT(decimal(1.1)))

thank you for helping

View 14 Replies

SQL Insert Rounds Decimal

Sep 25, 2006

Everytime i try to insert into my database it rounds all my decimals to ints. In design view they are decimals. Here is the code:cmd.Parameters.Add("@B_NetProceeds", SqlDbType.Decimal).Value = B_Proceeds;In Database i get 859Label1.Text = B_Proceeds.ToString() gives me 858.67 Why does it keep rounding? Also my float works fine. Its just the decimal.

View 7 Replies View Related

Cant Insert Decimal From Textbox

Dec 20, 2004

Hi guys.

Hopefully someone can help me, im pulling out my hair over this!

Im trying to insert a price into my MSSQL database.

The Column type is decimal.

If i use the following simple script

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Sub Submit_Click(Sender As Object, E As EventArgs)
Dim sqlCmd AS SQLCommand
Dim sqlConn as SQLConnection = New SqlConnection("************;")
Dim mySQL as String = "Insert Into Products (Delivery, Price) Values (" & Dvalue.Text & ", " & Pvalue.Text & ")"
sqlCmd = New SQLCommand(mySQL,sqlConn)
sqlConn.Open()
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
End Sub
</script>
<form action="" runat="server">
Delivery - <asp:TextBox ID="Dvalue" runat="server" /><br>
Price - <asp:TextBox ID="Pvalue" runat="server" /><br>
<Asp:Button id="Submit" runat="server" text="Insert" onclick="Submit_Click" />
</form>

And add in the values as
Delivery - 12.9999
Price - 56.777

The resulting numbers on the database are
Delivery - 13
Price - 57

Im using 1and1 to host my sql database and using their own SQL admin tool to configure it.

The price column has the following options

Name - Price
Type - Decimal
size(if char) - 19 ( i cant seem to change this)
default value - <blank> (I cant seem to change this)

No other options are ticked (as in nullable, indexed, unique key, primary key, identity)

Is there some setting or something ive missed/ dont know about? (ive only really used access and mysql in the past)

I would really appreciate any help

View 7 Replies View Related

How To INSERT Decimal Value Into A .DBF File Using VS2005

Feb 20, 2007

Hi,

I use this code to connect into my Database:

db2 = New ADODB.Connection

dbConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=C:PATH;

Exclusive=NO;Collate=Machine;NULL=YES;DELETED=NO;

BACKGROUNDFETCH=YES;"

db2.Open(dbConn)

inside my app, I use this command to INSERT into .DBF named testInsert.DBF,
the testInsert.dbf has the feilds of: Name( string format) & Amount(Decimal format)

Dim Conn as String

Conn="INSERT INTO testInsert.DBF VALUES ('shippers06','625.06')"

rs.Open(Conn,db2)

In this Coding, the VS2005 returms me an error of "DATA TYPE mismatch"

I try to Convert the value 625.06 to Decimal but it just don't work...

Please HELP a noobie like me in finishing this app...

Thnx in Advance

View 2 Replies View Related

CREATE TABLE For BULK INSERT: How To Set The Decimal Number Right In Col ?

Jan 26, 2008

Hi all,

I executed the following sql code in my SQL Server Management Studio Express (SSMSE):
-- myCSV1.sql --

USE MyDatabase

GO

CREATE TABLE myCSVtable

(

Col1 int,

Col2 nvarchar(25),

Col3 nvarchar(25),

Col4 decimal (9.3),

)

BULK INSERT myCSVbulk

FROM 'c:myfile.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

);

GO
=====================================
I got the following error message:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '9.3'.

How can I set the statement "Col4 decimal (9.3)" right? Please help and advise.

Thanks in advance,
Scott Chang

View 7 Replies View Related

SQL Server 2012 :: Insert Empty String In A Decimal Field?

Jul 13, 2015

I have a requirement where i have to insert empty string in column whose datatype is decimal.

I have to replace the column value if it is null then insert it as ' '.

Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.

IIF(Column1 IS NULL, ' ', Column1))

I used TRY_PARSE but it insert null value if there is conversion error.

View 3 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?

Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.

Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric

Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!

I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007

I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
��1,500,123.00 to ��1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007

I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.

It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ???

Thank you for your help!

Anna

View 3 Replies View Related

Packed Decimal To Decimal Conversion

Jun 4, 2007

Hi,

I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 œ&
20060715 0001010100010101

Please help!

Thanks

Mirudhu

View 4 Replies View Related

T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)

Jan 30, 2006

Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt

View 3 Replies View Related

Insert :) I Have Different Insert Code Lines (2 Insert Codelines) Which One Best ?

Jun 4, 2008

hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If

cheers

View 2 Replies View Related

Decimal To Hex

Jul 8, 2004

Is there a way to create a SP or UDF that converts a decimal number to hexidecimal notation?

View 3 Replies View Related

Need Help With Avg And Decimal

Jul 23, 2005

i am having trouble the with the line below:SELECT AVG(CAST(RATE1 AS Decimal(9, 2))) AS Expr1i am trying to do an average on the column "rate1" and want only 2decimal points but the syntax i wrote doesn't do it. is theresomething i am missing or doing wrong?when i run the above syntax i get 7.246428 and i need it to be 7.2 iknow this is possible in SQL but for some reason cant figure it out.Any help would be greatly appreciated.thanks

View 2 Replies View Related

Using Decimal As A Key

Jul 11, 2006

Is there any disadvantages of using Decimal type as a primary key? Ifso, are there other data types that can hold large numbers (more thanan int can hold: 13 digits or so) and perform better as a Key field?Thanks.

View 3 Replies View Related

Decimal

Mar 19, 2008

I imported some feilds from a text file. Most of which i set to varchar. However, I want one of the feilds to have 2 decimal places.

I tried changing it in the table design after the import and nothing changed.
Then i tried re-writing the query for insertiing the values, where i changed varchar(11) to numeric (11,2) but i got an error. "Error converting data type nvarchar to numeric."

Any suggestions on resolving this glitch ??

View 8 Replies View Related

SqlMoney Vs. Decimal

Jul 17, 2006

I just created a new table in my database. One of the fields in that table has a data type of money. Would my C# code be better off referring to this field as SqlMoney or as a decimal and why?
Would I be better off altering the field in the table so that it has a data type of decimal?
Thanks

View 12 Replies View Related

Decimal Problem

Dec 18, 2006

Hi guys,I've got a table with one of the columns 'ConversionRate' declared as decimal. The value for this column is defined as 1.45. In my vb file, I tried to retrieve the value using a SqlParameter as shown below:myConversionRate = Me.SqlCommand.Parameters.Add(Sql.StoredProcParameter.ConversionRate, SqlDbType.Decimal)myConversionRate.Direction = ParameterDirection.Output which will access the following query in my stored proc: ALTER PROCEDURE dbo.Charge
@PConversionRate DECIMAL = 0 OUTPUT
AS
SELECT @PConversionRate = ConversionRate,
FROM TblCharge
For some reasons, the value returned is always 1 instead of 1.45. Anyone experienced this problem before and knows how to resolve it? Thanks in advance.

View 7 Replies View Related

Cannot Convert To Decimal

Apr 17, 2007

I have a money field in SQL that when i try and get the sum of it i cannot convert it to decimal. This was working now its not, and nothing was changed.Any reason for the error? DECLARE @TEST decimal(10,2)SET @Test = (SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID='0000010604')Print @TestRETURNS: 160471.24----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Specified cast is not valid. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.InvalidCastException: Specified cast is not valid.Source
Error:

Line 3576: // CURRENT TOTALLine 3577: cmd.CommandText = "SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID=@LoanID";Line 3578: decimal split_currentamt = ((decimal)cmd.ExecuteScalar());

View 6 Replies View Related

2 Decimal Places On Sql AVG ()

May 31, 2007

Hi,
I am trying to get my string to 2 decimal places...
Here is the code:string sumSQL = "SELECT CONVERT (float,sum(callTime)/count(callTime)) as CAVG FROM leads where agent = '" + agent + "' and " + dates.Text;
SqlCommand sumCMD = new SqlCommand(sumSQL, conn2);
object null1 = sumCMD.ExecuteScalar();if (null1 != null)
{string avgS = sumCMD.ExecuteScalar().ToString();Response.Write(avgS + "<br>" + "</td></tr><tr>");
}

Any Help appreciated...
Doug

View 1 Replies View Related

Decimal Length

Feb 6, 2008

Hi all - am in a bit of a quandry over this one!My application is all up and running - it's an despact & accounts app - I've missed one thing though - the ability to deal with half pennies (or half cents for those of you over the pond ;))
All of my tables are set up as decimal (19,2) along with all the params in my stored procs (a lot!). Is there an easy way to change all of these in 1 swoop?
Thanks in advance,
Stephen.

View 1 Replies View Related

Inserting A Decimal

Apr 12, 2008

Hi i have a insert stored procedure, which i need to insert a label.text which is 12.99
the problem i have is that it is inserting as 13.00 any ideas what i need to do??Dim Price As Decimal = CostLabel.Text then i am inserting with this param....
command113.Parameters.Add("@Cost", SqlDbType.Decimal).Value = Price

View 4 Replies View Related

Inserting A Decimal - HELP

Dec 23, 2003

Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.

For instance 12.5 will insert as 12.

Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@Num", SqlDbType.Decimal))
MyCommand.Parameters("@Num").Precision = 5
MyCommand.Parameters("@Num").Scale = 2
MyCommand.Parameters("@Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]

I also declared @Num to be a decimal in my Stored Procedure.

As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.

Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.

View 1 Replies View Related

My Decimal Value Is Being Rounded Of When It Gets To The Db

Jan 28, 2004

Hi

I'm inserting a decimal value into my db but it keeps round it of to a single figure. Its the result of time between two times. ie between 1:00 and 2:30 equals 2.5. But it ends up as 2 in the db even though the db field is also decimal. I know by looking at the watch in the bugger that its actually a decimal value just prior to insertion which only leads me to suspect that sql server is doing the rounding of, maybe this is the default behaviour but i need it to store the values as decimal.

View 9 Replies View Related

Decimal Value Is Getting Rounded

Oct 14, 2005

I am writing a simple shopping cart app and the price field is being rounded when inserted into the database.There is a textbox for the user to enter the price. A stored procedure is used to insert the line item info to the data base table. The field in the table is formatted as datatype decimal. Below is the stored procedure and the code that adds the value to the parameter. Any suggestions on where I am going wrong?<Code>CREATE PROCEDURE [sp_insert_CartDetail] ( @ReferenceNum [int], @Item [varchar](26), @Desc1 [varchar](27), @Desc2 [varchar](20), @Desc3 [varchar](30), @Desc4 [varchar](30), @Note [text], @Quantity [int], @DateOrdered [datetime], @SalesPrice [decimal], @DistCost [decimal], @SalesTaxable [smallint], @RequiredDate [datetime], @User1 [varchar](12), @User2 [varchar](12), @User3 [varchar](12), @User4 [varchar](12), @User5 [varchar](12), @User6 [varchar](12))
AS INSERT INTO [ToolCrib].[dbo].[CartDetail] ( [ReferenceNum], [Item], [Desc1], [Desc2], [Desc3], [Desc4], [Note], [Quantity], [DateOrdered], [SalesPrice], [DistCost], [SalesTaxable], [RequiredDate], [User1], [User2], [User3], [User4], [User5], [User6]) VALUES ( @ReferenceNum, @Item, @Desc1, @Desc2, @Desc3, @Desc4, @Note, @Quantity, @DateOrdered, @SalesPrice, @DistCost, @SalesTaxable, @RequiredDate, @User1, @User2, @User3, @User4, @User5, @User6)SqlCmdInsetCartDetail.Parameters("@SalesPrice").Value = CType(txtCost.Text, Decimal)
</code>

View 5 Replies View Related

Format Decimal

Mar 10, 2006

I want to format a number, a decimal, with 2 figures afther the "," but all I get is zeroes behind it. It works if I replace "cart.GetTotalWeight(cartId)" by 12.12 for example, 12,12 does not work... It driving me crazy

dim cart = New ShoppingCartDB()TotalweightLbl.Text = string.Format("{0:n}", cart.GetTotalWeight(cartId))
the stored procedure...
CREATE Procedure CMRC_ShoppingCartTotalweigh    (        @CartID    nvarchar (50),        @Totalweight decimal (5,2) OUTPUT            )    AS
    SELECT        @Totalweight = SUM(Products.Unitweight * ShoppingCart.Quantity)            FROM        ShoppingCart,        Products
    WHERE        ShoppingCart.CartID = @CartID      AND        Products.ProductID = ShoppingCart.ProductIDGO
the function....
Public Function GetTotalweight (ByVal cartID As String) As Decimal
            ' Create Instance of Connection and Command Object            Dim myConnection As SqlConnection = New SqlConnection ConfigurationSettings.AppSettings("ConnectionString"))            Dim myCommand As SqlCommand = New SqlCommand("CMRC_ShoppingCartTotalweigh", myConnection)
            ' Mark the Command as a SPROC            myCommand.CommandType = CommandType.StoredProcedure
            ' Add Parameters to SPROC            Dim parameterCartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.NVarChar, 50)            parameterCartID.Value = cartID            myCommand.Parameters.Add(parameterCartID)
            Dim parameterTotalweight As SqlParameter = New SqlParameter("@Totalweight", SqlDbType.decimal, )            parameterTotalweight.Direction = ParameterDirection.Output            myCommand.Parameters.Add(parameterTotalweight)
            ' Open the connection and execute the Command            myConnection.Open()            myCommand.ExecuteNonQuery()            myConnection.Close()
            ' Return the Total
             If parameterTotalweight.Value.ToString() <> "" Then                Return parameterTotalweight.Value            Else                Return 0            End If
        End Function

View 1 Replies View Related

Returning Decimal

Mar 3, 2001

I need to know a general syntax for inserting user information from an application into 6 Tables using one stored procedure. Is it something like:

Select fieldname, fieldname AS INSERT INTO tablename (@fieldname, @fieldname)

and keep going down the line, or do I need a BEGIN or GO somewhere?

Thanks!

View 2 Replies View Related

Text To Decimal

Nov 1, 2002

I need to import an nvarchar that contains 1/4 (for example) to a float field so it would read .25 Any suggestions? Thanks

View 3 Replies View Related