Solved

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

Posted on 2006-07-11
15
426 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:JMO9966
  • 8
  • 6
15 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 

Author Comment

by:JMO9966
Comment Utility
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
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 

Author Comment

by:JMO9966
Comment Utility
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
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
cmd.ExecuteScalar

I'm not creating a dataset or table.
0
 

Author Comment

by:JMO9966
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 

Author Comment

by:JMO9966
Comment Utility
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
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 

Author Comment

by:JMO9966
Comment Utility
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
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 

Author Comment

by:JMO9966
Comment Utility
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
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 250 total points
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now