Formatting Problem

I'm trying to send a sports standing result to the sql database. It would be send as either a string or a number as something like .450 or similar.

No matter what I send or how I setup the column it always turns it into 0.450 with a leading 0, even if the data type is text and it's sent a string.

Which data type should I be using to get only the decimal and the 3 trailing digits?

Thanks in advance.

Lawrence

 

I think Joel is right. Don't worry so much about how the data is actually stored in the database. You should format the number when you actually want to display it to the user.

 

Here is the SQL command string that being executed. The PCT field is writing an on-screen value from a label, lblPct. In this case assume the label is ".188".


strsql = "UPDATE [Standings] SET Won" & T & "=" & Trim(lblWon.Text) & ", Lost" & T & "=" & Trim(lblLost.Text) & ", Pct" & T & "=" & lblPct.Text & ", TB" & T & "=" & TBResult & " WHERE (Name='" & Trim(CurPlayer.Text) & "')"


"T" is a numerical value that allows me to write to the correct field from fields named pct1, pct2 etc. It works fine but it keeps adding the leading zero. I'm feeding it a string value but it's still adding a leading zero.


Numerical datatype writes 0.188 to the database. Same with text. Decimal writes 0.000


I can't find any data type that will write the text exactly as it is ".188" or whatever the percentage is. Everythjing I've tried adds a leading 0.


This is very puzzling...


If I feed that field a string value directly from that label(while the datatype is decimal) it writes 0.000 but if I assign that value to a variable dimesioned as a decimal type ...


Dim PctX as decimal


PctX = lblPct.text


And then use the PctX value in the SQL command string... it writes "0.188"


Now I go into SQL Management studio and execute this command....


UPDATE Standings


SET Pct1 = .188


WHERE (Name = 'Joe Blow')


Andit writes 0.188... Argh!


Lawrence

Post Edited (Lawrence) : 9/1/2006 2:53:45 AM GMT

 

Ok, so (I'm guessing) your problem is when you browse the database you are seeing a 0 infront of the decimal? This is a formatting issue with the software you are using to display the columns in the database.

The number in the database is actually stored in a binary format (not the way it is displayed), it's the software's choice to display the number the way it displayed. This cannot be changed.

If you are writing a program, you get to select how this number is displayed... for example you can do this...




decimal d = .123m;
Console.WriteLine("d = {0}", d);
Console.WriteLine("d = {0:.###}", d);

This will output like this...

d = 0.123
d = .123



Also, you will benefit greatly by usingmy Database Utility Library. It will simplify your queries a lot and make it much more readable...

http://joel.net/software/databaseutility.aspx (free with source code).


















Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Yes butwhen the data is recalled it's recalling 0.188 instead of .188, when I SELECT that data it comes back as 0.188


Yes, I see that 0.188 value when I view the data in the table. It's being written that way I presume.


How do I format the UPDATE string so that leading 0 won't get written, or added on recall?


I'm coding with VB.netin VWD. It seems the only way to kill then leading zero (in the SQL database)is to kill the decimal point. If I send 188 to the database 188 gets written and recalled. If I send .188 it writes and recalls 0.188


Try it on a sql databaseif you canand give me a SQL command string that will UPDATE a column with .188and thenallow a SELECTcommand which recallsthe ".188" value without the leading 0. I can't do it.


Maybe it's VB / VWD causing the issue? I can manually go into the table and enter .188 without a problem. Just can't get it there from code.


Lawrence



Post Edited (Lawrence) : 9/1/2006 3:04:52 AM GMT

 

The data is stored in a binary format (not as a string). So 0.188 .188 and 0.1880 are all stored exactly the same way. It's up to the client (not the database) to choose how to display this.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

You should use the DECIMAL data type. Though it really depends on how you format the number, not the data type in the DB.


What is the current data type? What code are you using to display the field?


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

I think my problem is my inexperience with SQL. Guide me please.


1. I have a DataGrid / DataView control that is bound to database fields, Won, Lost & Pct.


2. When the pct data is recalled it's recalled on the screen in the control (as I said earlier) as (for example) 0.188, I don't see any parameters in the control itself for formatting or trimming that data down to .188


3. How do I, in code, with that bound control, change that to display .188 in that data control? Not sure how to do that with the SQL language.


How would you approach that problem directly?


Idid buy "SQL for Dummies"


I appreciate all the help you guys give people here. I'm an experience VB proggie but I'm just cutting my SQL teeth.


Lawrence

 

<aspataGrid runat="server" id="myGrid">
<Columns>
<asp:BoundColumn datafield="field1" headertext="Field 1" dataformatstring="{0:.###}" />
</Columns>
</aspataGrid>



Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET