Solved

I need an efficient method to query data with MANY parameters

Posted on 2011-09-13
5
260 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
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 167 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 333 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 333 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

12 Experts available now in Live!

Get 1:1 Help Now