hi,
i'm using the following code to generate the value of a column in a database, where the colums value is dependent upon the multiplication of two other cells in the same row. When I execute the statement, it will work fine for whole numbers, but it does not work correctly for decimal values. All columns are of type varchar(50). An exmple.................It just multiplied 200 by 2.50 and returned 600
SqlCommand objCmd2 = new SqlCommand("UPDATE Portfolio SET Current_Worth=Current_Price*Number_Of_Shares WHERE Name_Of_Asset LIKE '%'+@Name_Of_Asset+'%'", objConn);
objCmd2.Parameters.AddWithValue("@Name_Of_Asset", DropDownList1.SelectedValue);
objConn.Open();
objCmd2.ExecuteNonQuery();
objConn.Close();
I found the following bug in Management Studio: I add an article with sp_addarticle and a value of 0x00 for @schema_option or via the wizard with all article properties set to false. Then I script the publication to a file. The result contains a sp_addarticle statement with @schema_option = 0x0000000000004071, which must be wrong. To prove that I recreated the article with the generated script and checked the article properties. As I feared 4 of the properties ('Copy clustered index', 'Copy nonclustered index', 'Copy unique key constaints' and 'Convert data types') are set to true!
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?
My C# application has a DataGridView which is bound to a DataSet. I used Visual Studio to add/configure the data source. Everything works fine on my computer. When I try to run the application on another computer I can't connect to the database, because it uses a wrong connection string.
How can I make a specific connection/connection string permanent for a specific application?
I have created a report, and am setting default values for the parameters. Once the default parameters is set, the report automatically generates. Is there a way to make it not automatically generate the report? We are setting the parameters with values that will get the end user the most recent data, but alot of times, they will want to pull older data as well. We don't want them to have to wait for the report to automatically generate before they can change the parameter values
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME) ,d.fmc_prodlnk, d. fmc_cost INTO #TestTable FROM ( SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
The values that are being fetched from the database are not being read as is , when there are decimal values with 10 or more digits after the point, the values are truncated (approximated) to 8 digits
The approximation is not consistent in all the cases Some times depending on the numbers for example 0.434000001 is truncated to 0.434.
This is happening when i read the values from the sql database using the SqlDataReader.GetValue method into an ArrayList in C# .NET
I have a table Saleshistory with 89481412 records. I have a field Sales of nvarchar type, all the records in this field are numeric, and the records are with 2 decimal places. I expect all the records to be ending at .00 (for example 5.00,345.00,25.00), but I want to verify if something is for example 5.6 etc.
So in short I want to check if any Salesrecords have more than zero values in the decimal. How can I check this.
I have an SQL search that is converting two values to type money. I want it to show two digits after the decimal point but am getting inconsistent results. The first value is as follows:
tblInventoryItem.itemcost as originalcost (the column is datatype money)
This displays correctly i.e. 2000.00 or 150.70 etc
The second value is this:
tblInventoryItem.itemcost + tblUpgrades.ItemCost as totalcostincupgr (both columns are datatype money)
But this displays as 2000 or 150.7
How can I get the second value to show two decimal places even when the digits are zeros?
Hi. I I'm importing a text file with lot's of decimal values with this format xx.xx. The problem is that my locale is Portugal and the points are being striped off and are not being considered as decimal separators (for example I have values like 0.04 and in the sql server database i see 4). I have tried to change the locale but i receive a message saying that the locale is not installed in my system.
I want to find out if a ceratin column has values with decimal figures populated? for example say column A has values
10.00 20.00 30.00 50.01
in this case the query should let me know that there is one figure (50.01) with a actual decimal value. So the results of the query should be 1 (since one record has decimal figures <>.00)
In SQL Server for a field of datatype float(8), if i insert a value 2.62 , it saves it as 2.6200000000000001 like so for other values also. But in frontend i can see the right values.
I've got this sql statement that keeps returning the wrong data. (it's related to a previous post, but is different)
Code: SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY FROM CUSTOMER C INNER JOIN USERS U ON C.ID = U.ID INNER JOIN ORDERS O ON C.ID = O.ID INNER JOIN ORDER_LINE OL ON O.ID = OL.ORDER_ID
select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma] from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
Anyone seen wrong values returned from a CLR update trigger when using the columncount property?
I have a 6-column table that I was experimenting on, and the isupdatedcolumn property was not returning true on the one column I was updating (I verfied the trigger was firing). So, I returned the columncount property in a pipe.send, and was surprised to see the value of 11. When I looped through each column's isupdatedcolumn property, the only column that returned true was column 9.
Then I remembered this table used to have more columns, so I conducted the following experiment:
Added a new column to the table (bringing the total number of columns to 7). Executed an update the caused the trigger to fire; columncount sent to the pipe returned 12. Deleted the column. Executed another update that caused the trigger to fire; columncount returned 12. Added another column to the table. Executed an update the caused the trigger to fire; columncount sent to the pipe returned 13. Deleted the column. Executed another update that caused the trigger to fire; columncount returned 13.
See the pattern? I'm fairly perplexed. Anyone seen this or something similar? This table is in a database that was migrated from SQL 2000 to 2005 and is 9.0 compatibility mode.
Hi, I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly. But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778 So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
The string column value looks like as below. Each value has a size of 15 withing a string
'2.2020 30 4.0000'
The column value should match with user input as below. The result should show equal when it is compared. Currently, it results not equal since it is a string comparision. The last digit '0' needs to be ignored for decimal values.
'2.202 30 4.0'
I need to handle the decimal values in such a way, if staring value with '.' and last digit is 0 then replace with space ''. So, it should look like
'2 2 2 30 4 ' = '2 2 2 30 4 '
When this string is compared, it results in EQUAL.
I tried the below logic, which even replaces the integer value like 30 to 3 and 3000 to 3 and results in equal which is incorrect.
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
I am importing an excel spreadsheet into a MS SQL database table. When the spreadsheet is finished importing, I am noticing that some values that were brought in resemble something like this 1.41666666666667. Other values may be shorter or only have 1 digit. The problem is another web application that pulls this data for use in online forms only allows up to 2 digits. How can I round all of the numbers like the above to 2 decimals and replace the existing values?
I know there is the rounding function that could be used like so:
SELECT ROUND ([Hrs Total 2],2) FROM AnnualClassifiedPAFs
How do I then take that rounded value and insert it back into the records?
Create report with SharePoint list. Columns are displaying on the report I used out of the group SUM function and it is group by report. one group total is showing correct total and other groups are showing wrong total and showing too much big value even there is whole column is empty.where value is not in column I am using "-" with IIF function. these columns are calculating by using date difference function between two date columns. these values coming with minus value . so ABS function is also using.
Columns calculation expression:
=IIF(ISNOTHING(Fields!DateCAPackage.Value) OR (ISNOTHING(Fields!Date_CA_Application.Value)) , "-" , CINT(Abs(DateDiff("d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value))))
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)
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?
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
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
