February 6, 2017 at 2:16 am
Hi, hopefully this question is in the correct place.
I have a SQL Server 2014 database that I am working on for several clients, this is connecting to a Visual Studio (VB) front end application for data entry and validation.
Several of the items in the database are measurements and I felt it was better to use a single unit of measurement throughout the database so have standardised on mm within the database, however some of the users are in the USA and want the ability to view and enter in inches.
If I read a table in full and as is by using the following then everything reads and subsequently updates no problem "Select * From MaterialTable"
IHowever if I read using this line then the read works correct and the data is displayed in the correct units (have hard coded the 25.4 below, usually is either 1 or 25.4 depending on display units), however when I try to update the table remains unchanged though I do not get any error"Select material_id, material_description, diameter = case when 1 = 1 then diameter / 25.4 end, wall_thickness = case when 1 = 1 then wall_thickness / 25.4 end From MaterialTable"
I am very much a novice with SQL, is there anything clever I should do to update correctly or am I flogging a dead horse?
If this is not going to work my fallback plan is to read the values into different a different column, hide the original then update the original if the new column is changed and then update, which is a bit more work in the front end but I think should be possible?
Am I missing something simple? Is there a better way?
Thanks in advance for any help.
February 6, 2017 at 2:26 am
To update a table you need an UPDATE statement, not SELECT.
I don't understand the purpose of a CASE expression with 1=1 as the condition? That condition will always evaluate as True and therefore you don't need CASE at all.Select material_id, material_description, diameter = diameter / 25.4, wall_thickness = wall_thickness / 25.4 From MaterialTable
John
February 6, 2017 at 2:34 am
Hi I know I need an update to update thats not the problem.
The issue I have is that the update doesn;t work when I read the data using the CASE as shown.
I know 1 =1 is always true, that is to get the values to divide by the correct scaling for the display units in use.
I am happy to do something different if you can tell me how to apply a scalefactor differently
February 6, 2017 at 2:43 am
I still don't understand. What are you trying to do - update the table to turn mm into inches? I would advise you to store data in consistent units - either imperial or metric throughout. You mention you have a VB front end - why not have that do the conversion and display the correct units according to the nationality of the user? Please will you share your UPDATE statement?
John
February 6, 2017 at 2:55 am
John Mitchell-245523 - Monday, February 6, 2017 2:43 AMI still don't understand. What are you trying to do - update the table to turn mm into inches? I would advise you to store data in consistent units - either imperial or metric throughout. You mention you have a VB front end - why not have that do the conversion and display the correct units according to the nationality of the user? Please will you share your UPDATE statement?John
This is exactly what I am trying to achieve. Hence the front end needs to convert the data into the relevant display units from the consistent database units which are mm, I am getting the values in either inches or mm's by use of the select case statement.
The update is as simple as the below, the update works fine when not using the CASE statement.
Changes = Table.GetChanges
If Not IsNothing(Changes) Then Adapter.Update(Changes)
Table.Clear()
Adapter.Fill(Table)
February 6, 2017 at 3:03 am
I can't help you with your VB code, I'm afraid - I don't know enough about it. If you use an Extended Events session or Profiler trace to capture the SQL that is actually executed, that would probably provide a clue about what's going wrong. But my advice is not to do this at all. Store your measurements as mm, and if certain users want to see inches, do that for them in the presentation layer.
John
February 6, 2017 at 3:04 am
John Mitchell-245523 - Monday, February 6, 2017 3:03 AMI can't help you with your VB code, I'm afraid - I don't know enough about it. If you use an Extended Events session or Profiler trace to capture the SQL that is actually executed, that would probably provide a clue about what's going wrong. But my advice is not to do this at all. Store your measurements as mm, and if certain users want to see inches, do that for them in the presentation layer.John
The units are stored as mm, that is root of the problem
February 6, 2017 at 3:19 am
But why is that a problem? You can have your front end display the measurements in any unit you like, so why does it matter what units they're stored in? If you want to do this as a one-off, to convert your whole database to imperial, do something like this for each table:UPDATE MyTable
SET
MillimetreColumn1 = MillimetreColumn1 / 25.4
, MillimetreColumn2 = MillimetreColumn2 / 25.4
, ...
Before you do this, make sure that the data types for your columns are appropriate for your new units. If your mm measurements are stored as int, for example, that's not likely to give sufficient precision for measurements in inches. That's another reason why I recommend you not to do this.
John
February 6, 2017 at 3:28 am
I don't want to change units within the database.
I just want the user to be able to read and write data from the database using different units.
I can read in different units no problem using above SQL, however after reading the table in this way I can no longer update, presumably because the dataadaptor is aware that the data is display is modified and not directly linked to the source table. So if I can't update in different units just by using SQL which would have been simplest and most elegant solution then I will need to do it differently. Probably by having additional columns in the front end to contain the scaled data that will change the linked column when it is modified. Unless someone can tell me a better way?
February 6, 2017 at 3:38 am
We've been talking at cross purposes, then. When you said "This is exactly what I am trying to achieve", I took that as a reply to my question "What are you trying to do - update the table to turn mm into inches?" As I mentioned, I'm afraid I don't know enough about VB code to be able to help you with doing it the way you're doing it. What I would do is drag the data out of the database in mm, with no conversions whatsoever, and have your front end convert to inches (and back to mm where there is an update).
John
February 6, 2017 at 4:26 am
I agree with the others, you don't want to store your data in different measurements, you should store them in 1 and convert at the front end. How do you know if a user is in the USA, or not (or what they're preference is), language settings? Are ALL measurements currently stored in millimetres or is it a mixture. If it's already a mixture, how do you know if a value is one or the other?
This is total guesswork on your scenario, I have no idea what your VB looks like, and I'm not an expert, however, I'd do something like this:'Put the below in your process that gets the information from your SQL Query
'You can find a list of Language ID's here https://technet.microsoft.com/en-us/library/cc179219.aspx
IF (Application.LanguageSettings.LanguageID(msoLanguageIDUI) = 1033) THEN
'1033 = American English
txtMeasure.Text = "Inches"
txtDiameter.Text = rstMaterial!diameter / 25.4
txtThickness.Text = rstMaterial!wall_thickness / 25.4
ELSE
txtMeasure.Value = "Millimetres"
txtDiameter.Text = rstMaterial!diameter
txtThickness.Text = rstMaterial!wall_thickness
END IF
'On your save function, do similar, but instead * your diameter and thickness value by 25.4 IF the user is American
You could also, instead of my presumed example of a locked text box with the measurement name, have it as a dropdown box. You could then give the option for the user to swap between measurements, and then add an OnChange option on the drop down, changing the value of your txtDiameter and txtThickness (either 8 25.4 or /25.4, depending on their choice).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2017 at 5:18 am
Thom A - Monday, February 6, 2017 4:26 AMI agree with the others, you don't want to store your data in different measurements, you should store them in 1 and convert at the front end. How do you know if a user is in the USA, or not (or what they're preference is), language settings? Are ALL measurements currently stored in millimetres or is it a mixture. If it's already a mixture, how do you know if a value is one or the other?This is total guesswork on your scenario, I have no idea what your VB looks like, and I'm not an expert, however, I'd do something like this:
'Put the below in your process that gets the information from your SQL Query'You can find a list of Language ID's here https://technet.microsoft.com/en-us/library/cc179219.aspx
IF (Application.LanguageSettings.LanguageID(msoLanguageIDUI) = 1033) THEN
'1033 = American English
txtMeasure.Text = "Inches"
txtDiameter.Text = rstMaterial!diameter / 25.4
txtThickness.Text = rstMaterial!wall_thickness / 25.4
ELSE
txtMeasure.Value = "Millimetres"
txtDiameter.Text = rstMaterial!diameter
txtThickness.Text = rstMaterial!wall_thickness
END IF
'On your save function, do similar, but instead * your diameter and thickness value by 25.4 IF the user is American
You could also, instead of my presumed example of a locked text box with the measurement name, have it as a dropdown box. You could then give the option for the user to swap between measurements, and then add an OnChange option on the drop down, changing the value of your txtDiameter and txtThickness (either 8 25.4 or /25.4, depending on their choice).
I DON'T want to store my data in different measurements! The whole point is to avoid that
February 6, 2017 at 5:34 am
mikek 50666 - Monday, February 6, 2017 5:18 AMI DON'T want to store my data in different measurements! The whole point is to avoid that
So would the above idea not work for you? The example requires all measurements are stored in millimetres, and then converts based on their language setting. Does that help you?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2017 at 5:44 am
Thom A - Monday, February 6, 2017 5:34 AMmikek 50666 - Monday, February 6, 2017 5:18 AMI DON'T want to store my data in different measurements! The whole point is to avoid thatSo would the above idea not work for you? The example requires all measurements are stored in millimetres, and then converts based on their language setting. Does that help you?
I am using a datagridview control for the data as I thought it would simplify my task, so the update is just a case of end edit on that then update the dataadapter it is linked to.
If the data was updating with inches instead of mm or vice versa I could fix it but the update doesn't want to happen at all if I read the data in the way I mentioned originally.
If I don't do it that way then it is not easy to manually scale the datagridview
February 6, 2017 at 7:02 am
Can I presume that the datagrid is therefore interactive and acts as a 2 far interface (both for returning data and inserting)? If so, I'm not sure you can get the functionality you want. Datagrids simply display the data, they aren't really designed to display it differently for different users, and I'm very sure they can't treat an update statement differently depending on the user.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply