I'm building a form that will display data for a selected material item using a ComboBox.
The list boxes will display summary figures for the selected material item. I have already written the queries in Access to get an idea of how to perform the calculations but now am debating whether I should attemp to write the same formulas with code by using the DataSet\DataTable objects as I have created these behind the form already in case I need them.
Not sure how if there is a big performance or integrity advantage of doing with code run against VB.Net DataSet\DataTable objects or if I should just run my SQL queries to retrieve the data and perform calculations. In either case I need to add a Where clause to the sql statements or VB.Net select commands to filter on the material item selected in the combobox.
What is the preferred approach. Here's the db connctions and sql statements I have at this point.
Dim dcMRP As Odbc.OdbcConnection = New Odbc.OdbcConnection("DSN=database;uid=test;pwd=TEST")
sSQLMatlReq = "Select job, Material, Status, Est_Qty, Act_Qty, Trade_Date, Due_Date from material_req Where status = 'O';"
daMRP.SelectCommand = New Odbc.OdbcCommand(sSQLMatlReq, dcMRP)
sSQLJob = "select Job, Part_Number, Status, Pick_Quantity, Completed_Quantity, Trade_Date, Sched_Start from job where status <> 'closed';"
daMRP.SelectCommand = New Odbc.OdbcCommand(sSQLJob, dcMRP)
Formula script example:
select sum(job.pick_quantity) - sum(Material_Req.Act_qty) As JobPickSumRemn from job
inner join material_req on job.job=material_req.job and job.part_number=material_req.material
where job.part_number = 'fg'
and job.status not in('closed','template')
and (material_req.due_date <= (getdate() -1) or Due_Date is null)