Solved

I need an efficient method to query data with MANY parameters

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

26 Experts available now in Live!

Get 1:1 Help Now