I need to write a floating point value to a column in a SQL server database. I have the following line of
code, that formats the float value before passing it off to the stored procedure.
Please look at the line of code below. Does the 8 after SqlDbType.Foat mean that this will be a floating
point with 8 signigicant digits past the decimal point?
mySqlCommand.Parameters.Add("@Price", SqlDbType.Float, 8).Value = float.Parse(InitialPrice);
Hello all,
I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.
I yesterday got called by a client who said that a payment for £15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.
The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.
The variable that the result was being put into was of real datatype.
I then wrote a udf to test this. Here is the function:
CREATE FUNCTION dbo.POUNDS_TO_PENCE
(
@POUNDVALUE real
)
RETURNS INTEGER
AS
BEGIN
RETURN @POUNDVALUE * 100
END
As you can see nothing very special.
If you run this runction and pass in 17.63 it will return 1762!!!
The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.
I've also found that the same problem occurs when passing in £30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).
Does anyone know why this is or is it a bug in the processor?
The SQL books online say the following about the float and real data types:
--------------------------------------------------------
float and real (T-SQL)
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.
Syntax
float[(n)]
Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
n is Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes
The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
real
Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
--------------------------------------------------------
Apart from the fact that it says 'Approximate number data types' I can't see any difference between the data type apart from the ranges.
Anyone any ideas?
Thanks
Tom Holder
How can I truncate a floating point number to required number of decimal points
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645
Thanks.
Hi.
Intermittently, the following error has been displayed when a SQL job on our box (SQL Server 2000 Service Pack 4 running on Windows 2000) runs.
'A floating point exception occurred in the user process. Current transaction is canceled.' The error number given is 3628 though I've also seen a 4xxx number (not at machine at the moment so cant be precise for the latter).
The intermittent problem over the last two days has become more regular and now does not appear to be related solely to the SQL job. For example, when connecting to the box using my local copy of SQL 2005 Management Studio and I attempt to browse the database objects I get this problem.
To the best of my knowledge, this server has not had any software installed on it and/or nothing has changed on it recently.
It has now got to the point that this particular job no longer runs. The job in question, in case this is significant, is a vanilla stored proc that returns data using OPEN ROWSET to communicate with an Access database (this connectivity has been verified to be okay, in that other similar jobs all run just fine)
If anybody can shed some light and/or point me in the right direction I would be extremely grateful.
Cheers
CG
Hi,I would like to know, if I need to do some floting point operations(mainly multiplication and division) on each roll of a table, should Iread the data out from the DB and do the calculation with a programminglanguage, say C#, or should I just use sql to do it on the sql server.An obvious advantage of doing it in the sql server is that you dontneed to transfer the data between the sql server and he applicationserver.But I am not sure if there are any other factors that will overridethis advantage: like the performance of doing lots of floting pointoperations in sql server.How is the performance of doing floting point operations in sql servercomparing to C# or other languages?Also are there any other factors that should be considered for thiscase andare there any other advantages to do this is sql server or in c#?ThanksBenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI'm trying to perform a calculation on a field in SQL Server thatshould return a value with a decimal point. My problem is that thevalue returned is truncated without the decimal point. Is there asetting that needs to be turned on in SQL server to allow this?for exampleSelect 20/3should return 6.6666667but instead I get 6
View 1 Replies View RelatedHi all
I'm a newbie in SQL server and please excuseme for this silly question, Could anyone tell me when i should use which of the following types:
Decimal
Float
Real
I've mixed up !!! all of them can have floating point BUT what's the difference? some advise please!
Thanks in advance.
Kind Regards.
Hi SQL people,
I have rating system on pages in my website, each page being rated one to five by users. At the back end, an ASP.NET page displays the average rating and number of ratings for each page. As the rating is stored as an integer, the SQL statement returns an integer average. I would like to get a floating point average. Currently the statement used to return the average looks like this:
select AVG(Rating) as AverageRating from Ratings where [RatingPage] = @RatingPage
Is there a simple way to modify this to return a floating point average without iterating through the records, or converting the Rating field to a float?
Thanks for helping!
I have the following query:
SELECT AVG(respondent_question.answer)
Now, the 'answer' column is an integer. However, I want the query to return a floating point number with one decimal place (i.e., 5.4, 2.3)
Since the column is an integer, the query returns only an integer by rounding to the nearest integer. How can I do this? Thanks!
Hi,
I got below error in the SQL Server Production Server and i checked in the microsoft site it needs to install SQL Server service pack 4 to resolve the
problem.
"A floating point exception occurred in the user process. Current transaction is canceled"
I need help that i want to reproduce this below problem in the SQL Server environment and tried several ways but no luck.
Please advise me how to reproduce the problem.
Would be appreciate your help.
Regards
Sathish
I'm having a problem that I think is due to corrupt data. Depending on
the column I use in my order by clause two problems are occuring.
1. No results are returned and I get this error:
A floating point exception occured in the user process.
2. Results are returned but there are a different number of rows depending on which columns I use in my Order By clause.
Examples
SELECT * FROM SymbolStats
ORDER BY calc_date, symbol
Returns - 12207 rows but only includes one of the 25 dates in the table.
----------
SELECT * from SymbolStats
ORDER BY current_hv
Returns - 0 rows.
----------
SELECT * from SymbolStats
ORDER BY average_hv
Returns - floating point error
With more conditions in the WHERE clause the number of results returned varies greatly.
The
fact that different numbers of rows can be returned from the same query
only differing in how they are ordered seems like a bug.
Does this sound like corrupt data? If so, what are the best methods for fixing it?
Thanks,
patrick
I'm getting this error:
System.Data.SqlClient.SqlException: A floating point exception occurred in the
user process. Current transaction is canceled. at
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping
tableMapping) at ...etc etc
when updating one particular table, but not when updating another using nearly the same code.
I've searched and searched on the inet, but with no joy.
Microsoft have several different manifestations of it, under various
different circumstances, but the solutions all seem to involve
installing SP4, which by the looks of it is a massive procedure that
you have to go to university to be competent to do.
In any case, according to Enterprise Manager, I already have SP4
included. On the other hand, the one file of mine I checked the date of
against MS's hotfix file list was 2002 not 2003, so who knows?
My Service Manager is v 8.00.760, and it's the Development Edition (ie it's all running on the one machine).
According to the info on sqlDataAdaptor.update, the
sqlError that gets returned as part of the sqlException contains
fields Class, which gives the seriousness of the error, and State,
which identifies the exact error. I get Class=16 (user-fixable)
and State=1, which we have to go to SQL Books Online to discover the
meaning of. SQL BO probably sounded like a good idea to W.Gates,
but it took me half an hour to get anywhere near finding out what that
'1' meant. When I did, it was (guess): 'A floating point
exception occurred in the user process. Current transaction is
canceled.' !!!
Can anyone please tell me what exactly might be causing the error?
Graham Rounce
I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1
DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
SELECT POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
GO
DECLARE @x FLOAT(24) = 1.0004;
DECLARE @y FLOAT(24) = 1.0/12.0;
[URL] ....
Answer: 3.33272237835747E-05
I also tried using Windows Calculator.Answer:3.3327223783495255846580902358195e-5
And an online "high-precision" calculator from [URL] ...
Answer: 3.332722378349525584658E-5
The best SQL results compare only to the fourth digit with other results. Everything else agrees to the 10th or 12th digit.
Is the precision of arithmetic calculations on SQL Server that bad, or am I doing something wrong here? Is there another more precise alternative?
I did the following. The results speak for themselves
DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
DECLARE @z FLOAT(53) = POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
SELECT POWER((1+@z),12); -- 1.00039999839968
-- using results from other sources (c#, windows calc, casio.com)
SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004
SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004
SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004
It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.
sql server 2000 sp4 build 2184runs stats with maintenance plansp_msforeachtablestored procedure updating full stats for each table and the resultsare identicalA floating point exception occurred in the user process. Currenttransaction is canceled.all pointers are to move to latest service packAny pointers on how to avoid this errorThanks for your timeMassa
View 6 Replies View RelatedI am trying to run "select * from <table> where <columnname> = 0.0" inquery analyzer<columnname> is a floatI get the following error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.I get the same error when I use the float <columnname> through anapplicationI have tried upgrading to SP4, without any luck - still get the errormessage.Any help would indeed be appreciated.Thanks -AH
View 1 Replies View RelatedWe get the following error message."a floating point exception occured in the user process. currenttransaction is cancelled".this message comes when trying to excute a stored procedure. Thisexception is unpredictable.OS : Windows 2000 (SP3)Version: SQL server 2000 (SP3).
View 1 Replies View RelatedHi,I'm running SQL Server Version 8.00.194 on Windows 2000.I am am running this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'and TheoVolImplied > 0.0TheoVolImplied is of type float, precision 15, length 8.When I run this query I get this error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere TheoVolImplied > 0.0It works fine with no problems.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'It works fine with no problems.Anyone have any ideas about what might be wrong?
View 2 Replies View RelatedI have a Web application in SharePoint 2013 which is HTTPS. I have powerpivot installed and it is working fine when I try to access the PowerPivot Gallery with the port number.For e.g. URL...Everything is working fine.
But when I try to accesss the same URL...without port number I am getting the error. something went wrong Could not load
type 'Microsoft.AnalysisServices.SPAddin.ReportGallery.ReportGalleryView'.
Hi,
I installed the SharepointRS.msi add-in and configured the Reporting Services to the sharepoint integration mode. After the installation ,I am supposed to find a new section in the Application management of Central Administration Tool of sharepoint called Reporting services. But i am not able to find that.
View 1 Replies View Related
Hi..
I have a column in the data base with the type Float,
I want to limit the number of digits after decimal point to 2 when I display the value in ASP.NET but I don't know how!?
the number that appear after calculation llike "93.333333"
I use decimal(2,2) as data type but an error accour and this is the message
"- Unable to modify table. Arithmetic overflow error converting float to data type numeric.The statement has been terminated."
Can you help me..
thanks
I have a closed polygon that coincidently is in the shape of Iowa :) I have a point that is within the state and a point WELL outside it, but I get weird results that I don't expect when I try to get it to tell me that the point is within the polygon. Here is some basic code, with long coordinates data.
DECLARE @g geography,
@pIn geography,
@pOut geography
SET @g = geography::STPolyFromText('POLYGON((-91.119987 40.705402, -91.129158 40.682148, -91.162498 40.656311, -91.214912 40.643818, -91.262062 40.639545, -91.375610 40.603439, -91.411118 40.572971, -91.412872 40.547993, -91.382103 40.528496, -91.374794 40.503654, -91.385399 40.447250, -91.372757 40.402988, -91.385757 40.392361, -91.418816 40.386875, -91.448593 40.371902, -91.476883 40.390968, -91.490158 40.390762, -91.500221
[code]...
(1 row(s) affected)As I read that there is a distance of about 7864 meters, this is close to what I would expect, so that's ok. The point outside I would expect a distance as well so that is confusing.. Then we have the intersects, it says that the point inside does NOT intersect but the one outside DOES, this is backed up by the intersection values.
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 RelatedHello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
for example:
SELECT CAST(CAST(getdate() AS datetime) AS float)
how can i convert the return select value to nvarchar????
I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?
Thanks,
Peter
This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59.
[SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code>
Here is my button click sub in its entirety:
1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs)
2 'Variable declarations...
3 Dim articleid As Integer
4 articleid = Request.QueryString("aid")
5 Dim strSelectQuery, strInsertQuery As String
6 Dim strCon As String
7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection()
8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand()
9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader
10 Dim MyHttpAppObject As System.Web.HttpContext = _
11 System.Web.HttpContext.Current
12 Dim strRemoteAddress As String
13 Dim intSelectedRating, intCount As Integer
14 Dim Ratingvalues As Decimal
15 Dim Ratingnums As Decimal
16 Dim Stars As Decimal
17 Dim Comments As String
18 Dim active As Boolean = False
19 Me.lblRating.Text = ""
20 'Get the user's ip address and cast its type to string...
21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress)
22 'Build the query string. This time check to see if IP address has already rated this ID.
23 strSelectQuery = "SELECT COUNT(*) As RatingCount "
24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'"
26 'Open the connection, and execute the query...
27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString
28 conMyConnection.ConnectionString = strCon
29 conMyConnection.Open()
30 cmdMyCommand.Connection = conMyConnection
31 cmdMyCommand.CommandType = System.Data.CommandType.Text
32 cmdMyCommand.CommandText = strSelectQuery
33 intCount = cmdMyCommand.ExecuteScalar()
34 intSelectedRating = Int(Me.rbRating.Text)
35 conMyConnection.Close()
36 'Close the connection to release these resources...
37
38 If intCount = 0 Then 'The user hasn't rated the article
39 'before, so perform the insert...
40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) "
41 strInsertQuery += "VALUES ("
42 strInsertQuery += intSelectedRating & ", '"
43 strInsertQuery += strRemoteAddress & "', "
44 strInsertQuery += articleid & ", '"
45 strInsertQuery += comment.Text & "', '"
46 strInsertQuery += active & "'); "
47 cmdMyCommand.CommandText = strInsertQuery
48 conMyConnection.Open()
49 cmdMyCommand.ExecuteNonQuery()
50 conMyConnection.Close()
51 Me.lblRating.Text = "Thanks for your vote!"
52 Comments = comment.Text.ToString
53
54 If Len(Comments) > 0 Then
55 emailadmin(comment.Text, articleid)
56 End If
57 'now update the article db for the two values but first get the correct ratings for the article
58 strSelectQuery = _
59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
61 conMyConnection.Open()
62 cmdMyCommand.CommandText = strSelectQuery
63 dtrMyDataReader = cmdMyCommand.ExecuteReader()
64 dtrMyDataReader.Read()
65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
67 Stars = Ratingvalues / Ratingnums
68 conMyConnection.Close()
69 'Response.Write("Values: " & Ratingvalues)
70 'Response.Write("Votes: " & Ratingnums)
71
72 UpdateRating(articleid, Stars, Ratingnums)
73 Else 'The user has rated the article before, so display a message...
74 Me.lblRating.Text = "You've already rated this article"
75 End If
76 strSelectQuery = _
77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
79 conMyConnection.Open()
80 cmdMyCommand.CommandText = strSelectQuery
81 dtrMyDataReader = cmdMyCommand.ExecuteReader()
82 dtrMyDataReader.Read()
83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
85 Stars = Ratingvalues / Ratingnums
86 If (Ratingnums = 1) And (Stars <= 1) Then
87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
88 ElseIf (Ratingnums = 1) And (Stars > 1) Then
89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then
91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
92 ElseIf (Ratingnums > 1) And (Stars > 1) Then
93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
94 End If
95
96 'Response.Write(String.Format("{0:f2}", Stars))
97 'Response.Write("Values: " & Ratingvalues)
98 'Response.Write("Votes: " & Ratingnums)
99 If (Stars > 0) And (Stars <= 0.5) Then
100 Me.Rating.ImageUrl ="./images/rating/05star.gif"
101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then
102 Me.Rating.ImageUrl = "./images/rating/05star.gif"
103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then
104 Me.Rating.ImageUrl = "./images/rating/1star.gif"
105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then
106 Me.Rating.ImageUrl = "./images/rating/15star.gif"
107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then
108 Me.Rating.ImageUrl = "./images/rating/2star.gif"
109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then
110 Me.Rating.ImageUrl = "./images/rating/25star.gif"
111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then
112 Me.Rating.ImageUrl = "./images/rating/3star.gif"
113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then
114 Me.Rating.ImageUrl = "./images/rating/35star.gif"
115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then
116 Me.Rating.ImageUrl = "./images/rating/4star.gif"
117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then
118 Me.Rating.ImageUrl = "./images/rating/45star.gif"
119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then
120 Me.Rating.ImageUrl = "./images/rating/5star.gif"
121 End If
122 dtrMyDataReader.Close()
123 conMyConnection.Close()
124 End Sub
If you want to reduplicate the error, click over here and try to submit a rating:
http://www.link-exchangers.com/view_full_article.aspx?aid=51
Thanks for helping me figure this out.
Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
Hi,
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability
we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected
we need to store values as is
Error:
base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range.
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at MS.Internal.MS
COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default);
DataRow dr;
DataTable dt = new DataTable();
DataColumn dc;
try
{
dc = dt.Columns.Add("Availability", typeof(decimal));
€¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal
€¦€¦€¦.
}
bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]";
bulkCopy.WriteToServer(dt);
thx
Implement time interval type in the form of a user defined type in SS2k8r2? Specifically an interval type described in the book Temporal Data and the Relational Model by C. J. Date at all. As an example, an interval is below:
1/4/2006:1/10/2006
which would mean the time period from 1/4 to 1/10.
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package3.dtsx" finished: Failure.
I created a function called Temperature in VB to be used as a UDF in SQL2005. I get the error listed below. Any thoughts?
CREATE FUNCTION Temperature(@FluidName SQL_variant, @InpCode SQL_variant, @Units SQL_variant, @Prop1 SQL_variant, @Prop2 SQL_variant)
RETURNS Float
AS EXTERNAL NAME Fluids_VB6.[Fluids_VB6.FluidProperties.Fluids].Temperature
Then ran function:
select dbo.temperature('R22','t','e','225.6','0')
Got this:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'Temperature':
System.InvalidCastException: Conversion from type 'SqlBoolean' to type 'Boolean' is not valid.
System.InvalidCastException:
at Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(Object Value)
at Fluids_VB6.FluidProperties.Fluids.Setup(Object& FluidName)
at Fluids_VB6.FluidProperties.Fluids.CalcSetup(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2)
at Fluids_VB6.FluidProperties.Fluids.CalcProp(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2)
at Fluids_VB6.FluidProperties.Fluids.Temperature(Object FluidName, Object InpCode, Object Units, Object Prop1, Object Prop2)
Thanks
Buck