Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I need an efficient method to query data with MANY parameters

Posted on 2011-09-13
5
Medium Priority
?
270 Views
Last Modified: 2012-05-12
I query data with many parameters and there has to be an easier way then coding for each circumstance!  I usually code with ElseIf statements.  Below is a sample.  There has to be an easier way.  Any suggestions?

'----------between dates
        If P = "" And n = "" And Me.sDate.Text <> CDate(Today) And Me.CheckBox1.Checked = False Then
            Dim rec = From id In d.tblWaivers _
                      Where id.ApprovDate >= CDate(sDate.Text) _
                      And id.ApprovDate <= CDate(eDate.Text) _
                      Order By id.ApprovDate Ascending _
                      Select id

            If rec.Count = 0 Then
                MsgBox("No records retrieved")
            Else
                Me.TblWaiverDataGridView.DataSource = rec.AsQueryable
            End If

            '----------between dates and product
        ElseIf P <> "" And n = "" And Me.sDate.Text <> CDate(Today) And Me.CheckBox1.Checked = False Then

            Dim rec = From id In d.tblWaivers _
                      Where id.ApprovDate >= CDate(sDate.Text) _
                      And id.ApprovDate <= CDate(eDate.Text) _
                      And id.LineItem.Contains(P) _
                      Order By id.ApprovDate Ascending _
                      Select id

            If rec.Count = 0 Then
                MsgBox("No records retrieved")
            Else
                Me.TblWaiverDataGridView.DataSource = rec.AsQueryable
            End If

            '-----------product only
        ElseIf P <> "" And Me.sDate.Text = CDate(Today) And Me.CheckBox1.Checked = False Then

            Dim rec = From id In d.tblWaivers _
                      Where id.LineItem.Contains(P) _
                      Order By id.ApprovDate Ascending _
                      Select id

            If rec.Count = 0 Then
                MsgBox("No records retrieved")
            Else
                Me.TblWaiverDataGridView.DataSource = rec.AsQueryable
            End If

            '----------getting ready to expire only
        ElseIf Me.CheckBox1.Checked = True Then

            Dim dueDate As Date = DateAdd(DateInterval.Day, -30, Today)

            Dim rec = From id In d.tblWaivers _
                      Where id.ExpirDate <= dueDate _
                      Order By id.ApprovDate Ascending _
                      Select id

            If rec.Count = 0 Then
                MsgBox("No records retrieved")
            Else
                Me.TblWaiverDataGridView.DataSource = rec.AsQueryable
            End If
        Else
            MsgBox("You need to enter something for this to work!")
        End If

Thanks!

0
Comment
Question by:Karen Wilson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 501 total points
ID: 36529125
Write a stored procedure in SQL. In SP try to use ISNULL, COALESE, IF, CASE.. THEN statements to check the conditions. Call the SP in your application.
0
 
LVL 7

Assisted Solution

by:mr_nadger
mr_nadger earned 999 total points
ID: 36535478
stored proc is the best way, and you can set default values in the code or procedure, so you only have to have one method to call it.
You could set fields like produce to be like "%" & txtProductName.text & "%", and null dates to '01 jan 1901' for start dates etc.
0
 

Author Comment

by:Karen Wilson
ID: 36535499
I've never worked a stored procedure before (I know...  crazy huh but I don't care for them) so I guess I need to take a stab at it.  It just appears that I would be doing the same thing that I would be doing inside the code in the application.  I'll give it a go and see if it is easier.  
0
 
LVL 7

Assisted Solution

by:mr_nadger
mr_nadger earned 999 total points
ID: 36535528
it's easier to test and maintain all but the simplest SQL statements as stored procs; once you've got your statement working, it's just a matter of copy and pasting.

You can also update them without having to republish your app/website :)

Oh yes, stored procs "should" run more quickly than statements which are compiled on the fly
0
 

Author Closing Comment

by:Karen Wilson
ID: 36587113
I am going to close this question.  Thanks for the suggestion.  I am on the other side of the fence when it comes to stored procedures.  It looks like I will have to do the same thing over there as I do coding a query.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

688 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