• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

I need an efficient method to query data with MANY parameters

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
Karen Wilson
Asked:
Karen Wilson
  • 2
  • 2
3 Solutions
 
Easwaran ParamasivamCommented:
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
 
mr_nadgerCommented:
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
 
Karen WilsonAuthor Commented:
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
 
mr_nadgerCommented:
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
 
Karen WilsonAuthor Commented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now