Solved

I need an efficient method to query data with MANY parameters

Posted on 2011-09-13
5
265 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 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