Datatype Question

I've got numbers stored in my table as varchar which i need to cast to a
datatype so that i can perform an arithmetic operation on.
34.2348905444367
45.08070345435
34.6546456354354353
43.6540697929
the problem is, I need them to be cast so that i can perform calculations on
them. I need the specific value back, and not something that is rounded up
or down.
If I use float I get extra numbers appearing at the end, (ie. there is a
rounding issue)
If I try to cast as decimal(12, 16)
The scale must be less than or equal to the precision.
How can i simply get the true value of the record ?
Many thanks.decimal(28, 16)
28 = total length
16 = positions to the right of the decimal point.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Douglas Adams" wrote:

> I've got numbers stored in my table as varchar which i need to cast to a
> datatype so that i can perform an arithmetic operation on.
> 34.2348905444367
> 45.08070345435
> 34.6546456354354353
> 43.6540697929
> the problem is, I need them to be cast so that i can perform calculations
on
> them. I need the specific value back, and not something that is rounded u
p
> or down.
> If I use float I get extra numbers appearing at the end, (ie. there is a
> rounding issue)
>
> If I try to cast as decimal(12, 16)
> The scale must be less than or equal to the precision.
>
> How can i simply get the true value of the record ?
> Many thanks.
>
>|||The following works for me. But don't confuse how the number is
*displayed* with the way it is stored
CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO T1 (x)
SELECT 34.2348905444367 UNION ALL
SELECT 45.08070345435 UNION ALL
SELECT 34.6546456354354353 UNION ALL
SELECT 43.6540697929 ;
SELECT CAST(x AS DECIMAL(20,16)) FROM T1
Result:
34.2348905444367000
34.6546456354354353
43.6540697929000000
45.0807034543500000
(4 row(s) affected)
David Portas
SQL Server MVP
--|||Thanks guys
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121852998.202064.66570@.g43g2000cwa.googlegroups.com...
> The following works for me. But don't confuse how the number is
> *displayed* with the way it is stored
> CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
> INSERT INTO T1 (x)
> SELECT 34.2348905444367 UNION ALL
> SELECT 45.08070345435 UNION ALL
> SELECT 34.6546456354354353 UNION ALL
> SELECT 43.6540697929 ;
> SELECT CAST(x AS DECIMAL(20,16)) FROM T1
> Result:
> --
> 34.2348905444367000
> 34.6546456354354353
> 43.6540697929000000
> 45.0807034543500000
> (4 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
>

Datareader does not return result

the following is the code,...When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...

Connection is open and the query is simple select statement

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

Thanks

Niranch

niranch,

can you show me exact syntax of your query string within the context of how you are using it in the c# code? You may have some quotes being escaped incorrectly or something such that when run from the app code, it does not return any results and therefore you are getting your error. so, make sure that your query string is written correctly, particularly how you have used single or double quotes...--jp

|||

hi jp,

thank you for your suggestion...I just figured out the problem...

It is just that, when I run the same set of statements in my code behind file, I get the result, but when I run it in a seperated .CS file(DAL), I get the error mentioned in my post...I am trying to return datareader object to the Business tier...

I dont know why...

Thanks

Niranch

|||ahh so you are having trouble with the data moving through tiers. can you show some code of how your are interacting between the tiers?|||

hi this is my code...the same set to commands ,connections works fine in codebehind file

codebehid file

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database)

class where i have database access

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader);

|||

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader); <-- at this point is your reader null? if you put a throw statement here like if(myReader == null) throw new Exception("Null Reader") will you get an exception? is the reader already null here, or are you getting results here and just not when you set

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database) you get the null reader here? that make sense? at what point are you not getting any results? -- jp

|||

I get the problem, Exactly after executing the command

DAL Page

myreader = mycommand.executereader(), i get a null reader. "Invalid read attempt when there is no data" is the error...only in this page, i have my connection open...

i dont know if it is ok to send a datareader object as return object to another page............

|||

thankyou, i got the problem solved,,,

I transfered the contents from reader to datatable and passed as return objec to presentation tier...It worked fine.............

DataBase: Timeout Expired

Hi,

When I am trying to query a table which has got lakhs of records,

it said, Error:Timeout Expired . Timeout Expired prior to completion of operation or server is not responding.

Can anybody help me out

You can change/set the timeout property in the connection string. A more important question is, do you really need to show that many hundreds of thousands of records in the application? There are ways like paging that you can only bring in few hundred records at a time.|||

I dont want to display all these results nut, i need to query these many records to display some 100 records.

I have used Timeout in Connection String also.

Connection is fine.

But Query Execution is taking that much time.

Something similar to http://techielion.blogspot.com

Can you please explain that to me?

|||

If you only need 100 records you could do a SELECT TOP 100 ....

If its still taking too long, check your query. Do you have proper indexes on the columns that are in the WHERE condition? Are the indexes up to date? Is there too much fragmentation?

|||

Hi,

You also need to check the ConnectionTimeout and CommandTimeout property to see if it is too small to return all the results.