SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update not working after read database using CASE


Update not working after read database using CASE

Author
Message
mikek 50666
mikek 50666
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 46
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.

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85772 Visits: 18135
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
mikek 50666
mikek 50666
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 46
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
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85772 Visits: 18135
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
mikek 50666
mikek 50666
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 46
John Mitchell-245523 - Monday, February 6, 2017 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

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)

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85772 Visits: 18135
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
mikek 50666
mikek 50666
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 46
John Mitchell-245523 - Monday, February 6, 2017 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

The units are stored as mm, that is root of the problem

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85772 Visits: 18135
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
mikek 50666
mikek 50666
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 46
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?
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85772 Visits: 18135
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search