Updating SQL Views/Tables

[ccountey]

Here is my situation:

My company purchased an application to track the technical needs of our employees. It's a help desk solution that displays work orders in a table that can be sorted, customized, etc. The tables in the application are just views of SQL tables, views, and queries.

The application did not have a way to track real time so I built an application that captures this information using the Timer in VB.net and writes the data into a new table I created within the previous application's database.

THE PROBLEM
I would like my application to have a "close work order" feature.

Application 1: The application we paid for
Application 2: The application I built

Both applications talk to the same SQL DB

Application 1 has a datagridview that displays work order data based upon various FillBy statements. I don't have the specifics of this information.

Application 2 has a similar datagridview that uses FillBy statements based on various controls on the form (FillByLocation, FillByTechnician, etc.).

I have a button control on a form in Application 2 that updates the Status column of the TASKS table from a textbox control on the same form.

When the Status = "Completed", I want the work order to become completed in all aspects in both applications. Within Application 2, the work order disappears from the open work orders FillBy parameter.

However, the work order remains in a "Pending" state in Application 1, so it still shows up to the technicians as a work order that needs attention.

Within the SQL DB, there are the following Tables:
TASKS: Shows the work orders and their statuses

...and the following Views:
vTasks_Grid_Search
vTasks_Grid
vTasks_Browse

Application 1 uses these views, Application 2 does not.

I'm a beginner at this, but I'm assuming that these views control what the technician sees based upon their login names and the statuses of work orders within the TASKS table.

How can I update these views (queries) using my application (Application 2)?


Thanks for reading and please let me know if you need additional information.

 

[cjard]

Erm..

A view is a block of sql code stored in the db that is re-run every time you select from it. You change the data, the view will show different data

VIEWS DO NOT EVER EVER EVER STORE DATA

If the view is still showing what you dont want it to show, open the code of the view in a SQL editor or query analyzer, and RUN IT
Then work out WHY it is still showing the data. Either change the code of the view, or correct the bug in your app that is making the data still show.


Just incase you missed the point i made initially:

Lets make a view:

CREATE VIEW whatever AS SELECT * FROM MyTable WHERE id = 123


Now lets select from it:

SELECT * FROM whatever



This is what the database does:


SELECT * FROM (

SELECT * FROM MyTable WHERE id = 123

)


Comprende? THere is NO DATA in the view. It is an SQL that is run, and all the data it gathers at that time, is treated like a table that can be selected from, just like nested SQLs mmkay?


If you deleted record ID 123 and then tried to tell me "but when I select * FROM whatever it still shows me the record" then I'll say sorry son, youre doing something wrong because that's impossible.. Once the data is gone, the view will return no results, so selecting from it also returns no results

 

[ccountey]

Updating Views

Thanks for your reply, cjard.

My application (App2) updates the data directly using the UPDATE command. I can't modify the other application, but why would it show data other than what's in the actual SQL DB? It's almost like it's keeping the old data in memory until something else is triggered.

 

[cjard]

show data other than what's in the actual SQL DB? It's almost like it's keeping the old data in memory until something else is triggered.

Either youre not committing the update, or it isnt actually doing that and you jsut think it is because your update logic is wrong. Take a look at the SQL of the view and work out why it is selecting the records that it is selecting