|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
I want to KEEP trailing zerostechnicians (VB.Net front-end, SQL Server 2000 back end). The technicians need to record numbers with varying scale and precision. For example, they may record one reading as 63.45 and the next as 123.1 and a third as 1.32456. That's fine - those can be saved as floating point numbers (very little math is done with these numbers so I'm not too worried about strange floating point results). The problem is that I can't come up with a good way to save trailing zeros. If the technician records a reading of 12.00 or 15.560 for example, it needs to be preserved with the trailing zeros. I can make the numbers display correctly in the front end when they are typed in, but when they're saved, SQL Server drops the trailing zero(s). That means that when they're retrieved from the DB the next time they're needed, there is no way to know how many zeros to display (e.g. the tech may have typed in 12.00, but after it's been saved and retrieved it comes back as 12 and there's no way to know if the tech originally type it in as 12 or 12.0 or 12.000). The only ideas I've come up with are: 1) Save the numbers as text (varchar) and have stringent validation to make sure that no text actually sneaks in. 2) Save the numbers as floating point AND save the precision with which they were entered (e.g. 12.00 would get saved as 12 and the precision would be saved as 2). Putting numbers in text fields (option 1) makes me nervous and option 2 seems like a ton of extra work. Does anyone have a better idea or a recommendation for option 1 or option 2? Chester, take a look at the decimal and numeric data types in SQL Server
Books Online. You can specify the precision (total number of digits the field can contain), and scale (the number of digits to the right of the decimal place). Tom Dacon Dacon Software Consulting Show quoteHide quote "Chester" <ctart***@netscape.net> wrote in message news:1166228854.395329.51560@73g2000cwn.googlegroups.com... > I'm working on an app that records data collected by service > technicians (VB.Net front-end, SQL Server 2000 back end). The > technicians need to record numbers with varying scale and precision. > For example, they may record one reading as 63.45 and the next as 123.1 > and a third as 1.32456. > > That's fine - those can be saved as floating point numbers (very > little math is done with these numbers so I'm not too worried about > strange floating point results). The problem is that I can't come up > with a good way to save trailing zeros. If the technician records a > reading of 12.00 or 15.560 for example, it needs to be preserved with > the trailing zeros. I can make the numbers display correctly in the > front end when they are typed in, but when they're saved, SQL Server > drops the trailing zero(s). That means that when they're retrieved > from the DB the next time they're needed, there is no way to know how > many zeros to display (e.g. the tech may have typed in 12.00, but after > it's been saved and retrieved it comes back as 12 and there's no > way to know if the tech originally type it in as 12 or 12.0 or 12.000). > > The only ideas I've come up with are: > 1) Save the numbers as text (varchar) and have stringent validation to > make sure that no text actually sneaks in. > 2) Save the numbers as floating point AND save the precision with which > they were entered (e.g. 12.00 would get saved as 12 and the precision > would be saved as 2). > > Putting numbers in text fields (option 1) makes me nervous and option 2 > seems like a ton of extra work. Does anyone have a better idea or a > recommendation for option 1 or option 2? > And how would that help him?
Show quoteHide quote "Tom Dacon" <T**@dacons.com> wrote in message news:%23Ew$rsKIHHA.3312@TK2MSFTNGP03.phx.gbl... > Chester, take a look at the decimal and numeric data types in SQL Server > Books Online. You can specify the precision (total number of digits the > field can contain), and scale (the number of digits to the right of the > decimal place). > > Tom Dacon > Dacon Software Consulting > > "Chester" <ctart***@netscape.net> wrote in message > news:1166228854.395329.51560@73g2000cwn.googlegroups.com... >> I'm working on an app that records data collected by service >> technicians (VB.Net front-end, SQL Server 2000 back end). The >> technicians need to record numbers with varying scale and precision. >> For example, they may record one reading as 63.45 and the next as 123.1 >> and a third as 1.32456. >> >> That's fine - those can be saved as floating point numbers (very >> little math is done with these numbers so I'm not too worried about >> strange floating point results). The problem is that I can't come up >> with a good way to save trailing zeros. If the technician records a >> reading of 12.00 or 15.560 for example, it needs to be preserved with >> the trailing zeros. I can make the numbers display correctly in the >> front end when they are typed in, but when they're saved, SQL Server >> drops the trailing zero(s). That means that when they're retrieved >> from the DB the next time they're needed, there is no way to know how >> many zeros to display (e.g. the tech may have typed in 12.00, but after >> it's been saved and retrieved it comes back as 12 and there's no >> way to know if the tech originally type it in as 12 or 12.0 or 12.000). >> >> The only ideas I've come up with are: >> 1) Save the numbers as text (varchar) and have stringent validation to >> make sure that no text actually sneaks in. >> 2) Save the numbers as floating point AND save the precision with which >> they were entered (e.g. 12.00 would get saved as 12 and the precision >> would be saved as 2). >> >> Putting numbers in text fields (option 1) makes me nervous and option 2 >> seems like a ton of extra work. Does anyone have a better idea or a >> recommendation for option 1 or option 2? >> > > Well, other than the fact that it specifically addresses his
requirement...what more would you want? Tom Dacon Dacon Software Consulting Show quoteHide quote "Stephany Young" <noone@localhost> wrote in message news:%23YiW20KIHHA.2632@TK2MSFTNGP06.phx.gbl... > And how would that help him? > > > "Tom Dacon" <T**@dacons.com> wrote in message > news:%23Ew$rsKIHHA.3312@TK2MSFTNGP03.phx.gbl... >> Chester, take a look at the decimal and numeric data types in SQL Server >> Books Online. You can specify the precision (total number of digits the >> field can contain), and scale (the number of digits to the right of the >> decimal place). >> >> Tom Dacon >> Dacon Software Consulting >> >> "Chester" <ctart***@netscape.net> wrote in message >> news:1166228854.395329.51560@73g2000cwn.googlegroups.com... >>> I'm working on an app that records data collected by service >>> technicians (VB.Net front-end, SQL Server 2000 back end). The >>> technicians need to record numbers with varying scale and precision. >>> For example, they may record one reading as 63.45 and the next as 123.1 >>> and a third as 1.32456. >>> >>> That's fine - those can be saved as floating point numbers (very >>> little math is done with these numbers so I'm not too worried about >>> strange floating point results). The problem is that I can't come up >>> with a good way to save trailing zeros. If the technician records a >>> reading of 12.00 or 15.560 for example, it needs to be preserved with >>> the trailing zeros. I can make the numbers display correctly in the >>> front end when they are typed in, but when they're saved, SQL Server >>> drops the trailing zero(s). That means that when they're retrieved >>> from the DB the next time they're needed, there is no way to know how >>> many zeros to display (e.g. the tech may have typed in 12.00, but after >>> it's been saved and retrieved it comes back as 12 and there's no >>> way to know if the tech originally type it in as 12 or 12.0 or 12.000). >>> >>> The only ideas I've come up with are: >>> 1) Save the numbers as text (varchar) and have stringent validation to >>> make sure that no text actually sneaks in. >>> 2) Save the numbers as floating point AND save the precision with which >>> they were entered (e.g. 12.00 would get saved as 12 and the precision >>> would be saved as 2). >>> >>> Putting numbers in text fields (option 1) makes me nervous and option 2 >>> seems like a ton of extra work. Does anyone have a better idea or a >>> recommendation for option 1 or option 2? >>> >> >> > > If he uses, the decimal or numeric Sql Server datatype, how do you suggest
he retain the trailing zeroes of the values exactly as they were entered? Show quoteHide quote "Tom Dacon" <tdacon@community.nospam> wrote in message news:%23ga9XNMIHHA.4848@TK2MSFTNGP04.phx.gbl... > Well, other than the fact that it specifically addresses his > requirement...what more would you want? > > Tom Dacon > Dacon Software Consulting > > "Stephany Young" <noone@localhost> wrote in message > news:%23YiW20KIHHA.2632@TK2MSFTNGP06.phx.gbl... >> And how would that help him? >> >> >> "Tom Dacon" <T**@dacons.com> wrote in message >> news:%23Ew$rsKIHHA.3312@TK2MSFTNGP03.phx.gbl... >>> Chester, take a look at the decimal and numeric data types in SQL Server >>> Books Online. You can specify the precision (total number of digits the >>> field can contain), and scale (the number of digits to the right of the >>> decimal place). >>> >>> Tom Dacon >>> Dacon Software Consulting >>> >>> "Chester" <ctart***@netscape.net> wrote in message >>> news:1166228854.395329.51560@73g2000cwn.googlegroups.com... >>>> I'm working on an app that records data collected by service >>>> technicians (VB.Net front-end, SQL Server 2000 back end). The >>>> technicians need to record numbers with varying scale and precision. >>>> For example, they may record one reading as 63.45 and the next as 123.1 >>>> and a third as 1.32456. >>>> >>>> That's fine - those can be saved as floating point numbers (very >>>> little math is done with these numbers so I'm not too worried about >>>> strange floating point results). The problem is that I can't come up >>>> with a good way to save trailing zeros. If the technician records a >>>> reading of 12.00 or 15.560 for example, it needs to be preserved with >>>> the trailing zeros. I can make the numbers display correctly in the >>>> front end when they are typed in, but when they're saved, SQL Server >>>> drops the trailing zero(s). That means that when they're retrieved >>>> from the DB the next time they're needed, there is no way to know how >>>> many zeros to display (e.g. the tech may have typed in 12.00, but after >>>> it's been saved and retrieved it comes back as 12 and there's no >>>> way to know if the tech originally type it in as 12 or 12.0 or 12.000). >>>> >>>> The only ideas I've come up with are: >>>> 1) Save the numbers as text (varchar) and have stringent validation to >>>> make sure that no text actually sneaks in. >>>> 2) Save the numbers as floating point AND save the precision with which >>>> they were entered (e.g. 12.00 would get saved as 12 and the precision >>>> would be saved as 2). >>>> >>>> Putting numbers in text fields (option 1) makes me nervous and option 2 >>>> seems like a ton of extra work. Does anyone have a better idea or a >>>> recommendation for option 1 or option 2? >>>> >>> >>> >> >> > > I am at a loss as to why you would be nervous about "Putting numbers in text
fields". I'll bet you dollars to donuts that the technicians are typing the values into a TextBox control which is nothing more than a 'text field as you put it. Therefore, whatever validation you have on those textboxes will suffice. In the database tables, make sure that you declare the columns with enough width to cater for the maximum length of a number that a technician can enter, i.e. maximum number of integral digits + maximum number of decimal digits + 1 (for the decimal point) + 1 (for a sign, if necessary). If you need to do math on the values stored in the database then in your SQL statments, cast the varchar or nvarchar value as a float first, e.g.: select sum(cast(<columnname> as float))'. If you need to do any math on aany of the values in your application then all you need to do is cast the string as a Decimal, Single or Double (I recommend Decimal), e.g.: Dim _x As Decimal = Decimal.Parse(value) For validation, I would recommend using the Decimal.TryParse method. You obviously don't care what the value is so long as it can be sucessfully converted to a Decimal. An aspect that you do need to consider is whether or not there are any 'outside agencies' that insert these values into the database. If so, then they wiull have to brought into line as well. Likewise any 'outside agencies' that use these values from the database will also need to be told how to interpret the values. Show quoteHide quote "Chester" <ctart***@netscape.net> wrote in message news:1166228854.395329.51560@73g2000cwn.googlegroups.com... > I'm working on an app that records data collected by service > technicians (VB.Net front-end, SQL Server 2000 back end). The > technicians need to record numbers with varying scale and precision. > For example, they may record one reading as 63.45 and the next as 123.1 > and a third as 1.32456. > > That's fine - those can be saved as floating point numbers (very > little math is done with these numbers so I'm not too worried about > strange floating point results). The problem is that I can't come up > with a good way to save trailing zeros. If the technician records a > reading of 12.00 or 15.560 for example, it needs to be preserved with > the trailing zeros. I can make the numbers display correctly in the > front end when they are typed in, but when they're saved, SQL Server > drops the trailing zero(s). That means that when they're retrieved > from the DB the next time they're needed, there is no way to know how > many zeros to display (e.g. the tech may have typed in 12.00, but after > it's been saved and retrieved it comes back as 12 and there's no > way to know if the tech originally type it in as 12 or 12.0 or 12.000). > > The only ideas I've come up with are: > 1) Save the numbers as text (varchar) and have stringent validation to > make sure that no text actually sneaks in. > 2) Save the numbers as floating point AND save the precision with which > they were entered (e.g. 12.00 would get saved as 12 and the precision > would be saved as 2). > > Putting numbers in text fields (option 1) makes me nervous and option 2 > seems like a ton of extra work. Does anyone have a better idea or a > recommendation for option 1 or option 2? > "Chester" <ctart***@netscape.net> wrote: My instinct would be to store them as (6345,2) and (1231,1) and>I'm working on an app that records data collected by service >technicians (VB.Net front-end, SQL Server 2000 back end). The >technicians need to record numbers with varying scale and precision. >For example, they may record one reading as 63.45 and the next as 123.1 >and a third as 1.32456. (132456,5). That way you preserve all information, and your database keeps numbers rather than strings, and it's an easy calculation to turn one of these pairs back into a float. -- Lucian Why do you have a problem storing them as text? Especially
if you're not going to do calculations on them, or try to format them all into the same format. You can always convert them to decimal or float when/if you need to. In the meantime, you have exactly what the original technician input. If you needed to do calcs, you could always store them both ways, assuming you don't have 10 million rows and 200 columns or something huge like that. I would definitely do some validation to make sure the result is numeric, if that's definitely a condition. Robin S. ------------ Show quoteHide quote "Chester" <ctart***@netscape.net> wrote in message news:1166228854.395329.51560@73g2000cwn.googlegroups.com... > I'm working on an app that records data collected by service > technicians (VB.Net front-end, SQL Server 2000 back end). The > technicians need to record numbers with varying scale and precision. > For example, they may record one reading as 63.45 and the next as > 123.1 > and a third as 1.32456. > > That's fine - those can be saved as floating point numbers (very > little math is done with these numbers so I'm not too worried about > strange floating point results). The problem is that I can't come up > with a good way to save trailing zeros. If the technician records a > reading of 12.00 or 15.560 for example, it needs to be preserved with > the trailing zeros. I can make the numbers display correctly in the > front end when they are typed in, but when they're saved, SQL Server > drops the trailing zero(s). That means that when they're retrieved > from the DB the next time they're needed, there is no way to know how > many zeros to display (e.g. the tech may have typed in 12.00, but > after > it's been saved and retrieved it comes back as 12 and there's no > way to know if the tech originally type it in as 12 or 12.0 or > 12.000). > > The only ideas I've come up with are: > 1) Save the numbers as text (varchar) and have stringent validation to > make sure that no text actually sneaks in. > 2) Save the numbers as floating point AND save the precision with > which > they were entered (e.g. 12.00 would get saved as 12 and the precision > would be saved as 2). > > Putting numbers in text fields (option 1) makes me nervous and option > 2 > seems like a ton of extra work. Does anyone have a better idea or a > recommendation for option 1 or option 2? > Thanks for all the good advice! I've done some more testing since the
original post and have elected to go the text route. The store-as-text solution is going to be the easiest to reach from where I am now and the most widely applicable. Stephany brought up a good point about 'outside agencies' and (fortunately) there are non to worry about in this case. So I only have myself to blame if the app ends up trying to do something like (12.87 * kilograms). Looks like I'll have to be extra diligent about verification. My reluctance was based on all the problems I've had with systems that stored numbers as text but didn't tightly control the input and so ended up with letters where there should only be number, etc. Thanks again and happy VBing. Chester "Chester" <ctart***@netscape.net> wrote in news:1166228854.395329.51560@ 73g2000cwn.googlegroups.com:> The only ideas I've come up with are: You should handle the decimals during output - You can use Number.ToString> 1) Save the numbers as text (varchar) and have stringent validation to > make sure that no text actually sneaks in. > 2) Save the numbers as floating point AND save the precision with which > they were entered (e.g. 12.00 would get saved as 12 and the precision > would be saved as 2). ("0.000000") to output it with 6 decimal places). 
Other interesting topics
Convert a date string
Create button control at runtime SQL Server Authentication issues! Windows Service, Process.Start Need some For Loop Next Item Anyone know where I can download csharp develepper? Screen Capture Add values in 1-dimensional array Problem with assignment How to use Shell Extensions class from Eduardo Morcillo |
|||||||||||||||||||||||
