asked on

Showing form information from a view

Hello experts. I have a view that contains a bunch of calculations. For example a small part of the code is listed below.

SELECT dbo.tbl_Employee.Department, dbo.tbl_Employee.EmployeeNumber, dbo.tbl_Employee.FirstName, dbo.tbl_Employee.LastName,
ROUND((CASE WHEN [Supp Dent Enrolled] = 'True' THEN (CASE WHEN ([Supp Dent] = 8.08) THEN (16.14 / 160) WHEN ([Supp Dent] = 24.96) THEN (16.16 / 160)
WHEN ([Supp Dent] = 24.72) THEN (16.16 / 160) WHEN ([Supp Dent] = 44.61) THEN (16.16 / 160) ELSE (0) END) ELSE 0 END), 4) AS SuppDental,
ROUND((CASE WHEN [Supp Vision Enrolled] = 'True' THEN (2.54 / 160) ELSE 0 END), 4) AS SuppVision, (CASE WHEN ([HDHP Enrolled] = 1) THEN (([HDHP Amt] / 12)
/ 160) ELSE (0) END)
For now lets focus on Supp Dent = 8.08 and Supp Den = 24.96.

What I am looking to do is create some sort of admin screen in vb.net that allows me to view these figures and change them when needed thus updating the view (right now I am going into the view directly to update) How do I go about searching the statement to get the dollar amounts appear on the front end of my admin screen? I hope this makes sense. If not I attached a crude mock up.
example.docx

Scott McDaniel (EE MVE )

You can directly update the table using standard SQLConnection and SQLCommand methods:

Using con As New SQLConnection(YourConnectionSTring)
con.Open
Using cmd As New SQLCommand
cmd.Connection = con
cmd.CommandText = "UPDATE YourView SET SomeField=" & YourTextboxName.Value & " WHERE SomeOtherField=" & SomeOtherTextbox.Value
cmd.ExecuteNonQuery
End Using
End Using

Of course, you'd have to know the Primary or Secondary key of the table you're updating (which would be the SomeOtherField item).

DJ P

ASKER

I'm afraid I'm not understanding how that gets me to display the dollar amounts in a form from the sql code I posted.

Scott McDaniel (EE MVE )

I thought you wanted to update the view after viewing the information on your form, and I assumed you already were viewing that data.

You can bind your form to the view to different controls. For example, here's how to bind it to a DataGridView:

https://docs.microsoft.com/en-us/dotnet/framework/winforms/controls/displaying-data-in-the-windows-forms-datagridview-control

Personally, I just grab the data from the database and handle the data display myself, either by setting the .Text value of textboxes, the Datasource of a combo or datagridview, etc.

DJ P

ASKER

I'll do some reading per your link. I still cant make the connection on how to display the value in the code on a form

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DJ P

ASKER

Ok so when looking at this portion of my code:

ROUND((CASE WHEN [Supp Dent Enrolled] = 'True' THEN (CASE WHEN ([Supp Dent] = 8.08) THEN (16.14 / 160) WHEN ([Supp Dent] = 24.96) THEN (16.16 / 160)

Notice there can be two different amounts for Supp Dent. How do I represent this on my form. Sorry but I'm still a bit lost.