Data retrieval from a VB.Net form used for formulas - SQL Queries vs VB.Net Code

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")


DataSet|DataTables:

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)
            daMRP.Fill(dsMRP, "Material_Req")

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)
            daMRP.Fill(dsMRP, "Job")


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)


Thank You,
Jon
JMO9966Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jeff CertainConnect With a Mentor Commented:
In case you haven't got it already....

Dim sql as string = string.format("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) AND MaterialId='{0}'", cboMaterial.SelectedValue)
0
 
Jeff CertainCommented:
The preferred method:
1. Move to an enterprise database.
2. Use stored procedures :)

If this is not an option, ALWAYS do the query and calculation on the database. The engines behind those are optimized for performing basic aggregation functions (sum, count, average, etc) and for filtering.
0
 
Jeff CertainCommented:
The filter could look something like this:

Dim sql as string = string.format("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) AND MaterialId={0}", cboMaterial.SelectedValue)
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
JMO9966Author Commented:
That worked great, thank you!

Now I need to figure out how to store the results of this query into a variable if that's possible.

Thanks,
Jon
0
 
Jeff CertainCommented:
Piece of cake...

Imports System.Data.Odbc

Public Function GetTotal() As Double
Dim conn as New OdbcConnection("insert your connectionstring here")
Dim sql as string = string.format("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) AND MaterialId={0}", cboMaterial.SelectedValue)
Dim cmd as New OdbcCommand(sql, conn)
conn.Open
GetTotal = cmd.ExectureScalar
conn.Close
End Function
0
 
JMO9966Author Commented:
I know how to create a dataset\datatable for this result set but I was trying to dig the value out directly to a variable if possible.

Thanks,
Jon
0
 
Jeff CertainCommented:
cmd.ExecuteScalar

I'm not creating a dataset or table.
0
 
JMO9966Author Commented:
Nice, thanks!

Question for you.  The form will have 40 or so text boxes laid out in 5 rows, 8 columns format.  Each text box will contain a summary number from a SQL statement.  The 8 columns are each for a week going out 8 weeks.  SQL statement is the same but has to have different data filter on it.

Seems to me I'd want to create on db connection and then just run one sql script at a time and pass to variable from function to display in text box and then onto the next text box, over and over and over....

How would you approach this keeping my existing design in mind?

Thanks again,
Jon
0
 
Jeff CertainCommented:
Alright... there are a couple ways to deal with this. A lot of it probably depends on exactly what the difference between your rows happen to be.

If it is suitable, I'd probably think about generating a cross-tab query. This is sort of your classic "grid" query, with materials along the left, dates along the top, and the sums in the cells. You should be able to read that into a datatable, then just bind that to a readonly grid. (I'm not real good at cross-tab, but we have a whole topic area of really good SQL people.)

If that is not suitable, then you could probably wrap each row into a custom control with all your boxes. You could add a connection property that you set to the same connection for all user controls; open it before you update the control set, then close it when you're done. You'll probably want to check connection.State before trying data retrieval, just in case it gets closed.

You might even want to plan on extending the control. Instead of hard-coding 8 boxes, you could set it up to have N boxes, with the boxes summing on a date range based on a setting (i.e. wekk, month, quarter, year, etc). Then, create a control whose only job is to have a collection of these controls, so you can have as many rows as you need. And this collection control would be able to handle the updating to open the connection, update all controls, and close the connection.

Make sure you set the filter info (i.e. material type) as a property of the control, so that your filter is not hard-coded.

As you've probably gathered, this second approach will not be a trivial undertaking. However, it will result in a couple of seriously cool controls for your collection... and a great learning experience. Plus, I'd be willing to bet that your life will be easier in the future for the investment of time now.... 'cause you know the next thing your boss is going to ask is to show another row, or 6 or 8 columns. Wouldn't it be nice to do that in 10 minutes instead of 10 hours?
0
 
JMO9966Author Commented:
I should clarify the rows will all be for the same material id selected in the ComboBox, but will contain summary information for different data views, on hand, on order, requirements, etc....  

The column will then be week1, week2, week3 based of current date going forward.

I get an error when I call the GetTotal function.  I added this funciton call to a button event and I get an unhandled expection of type 'System.Data.Odbc.OdbcException'   Additional Information: System Error

It's dying on the GetTotal = cmd.ExecuteScalar  ??

Thanks,
Jon

0
 
Jeff CertainCommented:
This will help handle exceptions a littel better... you might want to put a breakpoint in the code at messagebox.show oex.message... and then examine hte inner exception to see if you get more info.

Public Function GetTotal() As Double
Try
Dim conn as New OdbcConnection("insert your connectionstring here")
Dim sql as string = string.format("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) AND MaterialId={0}", cboMaterial.SelectedValue)
Dim cmd as New OdbcCommand(sql, conn)
conn.Open
GetTotal = cmd.ExecuteScalar
Catch oex as odbcException
messagebox.show oex.message
Catch ex as Exception
messagebox.show ex.message
Finally
If conn.State = ConnectionState.Open then conn.close
end try
conn.Close
End Function
0
 
JMO9966Author Commented:
Thanks!

I checked these objects in immediate window as it was running and here's the results.  It dies on the GetTotal = cmd.ExecuteScalar line.

? cboMaterialID.SelectedValue
"10625"  {String}

? sql
"Select On_Hand_Qty from Material_Location where MaterialId=10625"

? GetOnHand
0.0

? cmd.ExecuteScalar
Run-time exception thrown: ODBC error [07002] Too Few Parameters.  Expected 1


Jon

0
 
Jeff CertainCommented:
Public Function GetTotal() As Double
Dim total As Integer
Try
Dim conn as New OdbcConnection("insert your connectionstring here")
Dim sql as string = string.format("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) AND MaterialId={0}", cboMaterial.SelectedValue)
Dim cmd as New OdbcCommand(sql, conn)
conn.Open
total= cmd.ExecuteScalar
Catch oex as odbcException
messagebox.show oex.message
return 0
Catch ex as Exception
messagebox.show ex.message
return 0
Finally
If conn.State = ConnectionState.Open then conn.close
end try
return total
End Function
0
 
JMO9966Author Commented:
Thanks, my final syntax is close, but needs fine-tuning.

current sql string for a simple example I'm trying instead is gettin the selected value from the combobox correctly, but I need the materialID to be in single quotes for query to run successfully I believe.

right now:

select sum(on_hand_qty) from material_location where materialID=123test

Need syntax to be:

select sum(on_hand_qty) from material_location where materialID='123test'


Thanks again for the great assistance,
Jon
0
All Courses

From novice to tech pro — start learning today.